Home › Blogs › swick's blog

Monitoring MSSQL 2005 DB Autogrowth with OpenNMS and Python

Wed, 05/12/2010 - 14:31 — swick

At some point in time we experience an explosive growth of some of our MSSQL 2005 Express databases. It is a bug which is known:

http://blogs.msdn.com/sqlblog/archive/2006/09/18/my-database-file-is-mar...

We installed a newer version which should have fixed the bug (so said the release note by Microsoft).
But unfortunately it didn't. So we decided to monitor it. The script I show you in a second checks if the database 'autogrowth' value is set to a percentage value instead of a fixed one. And if yes, whether it is set to a value greater than 5. If you don't use the Express variant, you have to adjust the script accordingly

You need:

* IronPython 2.6
* Net-SNMP for Windows ( I run it on port 1161. So I can use the Windows SNMP also)

# snmpd.conf
 
extend dbautogrowth cmd /c "C:\Program Files\IronPython 2.6\ipy.exe" e:\monitoring_scripts\check_dbautogrowth.py

# E:\monitoring_scripts\check_dbautogrowth.py
 
# Configuration
# DBNAME ist the one you see in Management Studio NOT the actual filename
DBNAME="YourDataBase"
 
# Don't change anything below this line
 
import sys
import clr
 
INFOSTRING="DBAutogrowth_" + DBNAME
 
sys.path.append(r"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE")
clr.AddReferenceToFile('Microsoft.SqlServer.Express.Smo.dll')
clr.AddReferenceToFile('Microsoft.SqlServer.Express.ConnectionInfo.dll')
 
from Microsoft.SqlServer.Management.Smo import *
from Microsoft.SqlServer.Management.Common import *
import Microsoft.SqlServer.Management.Smo as SMO
import Microsoft.SqlServer.Management.Common as Common
 
clr.AddReference("System")
from System import *
 
 
def main():
 
    server      = Server("localhost\SQLEXPRESS")
    db          = server.Databases[DBNAME]
 
    growth_raw      =  db.FileGroups["PRIMARY"].Files[0].Growth
    growth_type_raw =  db.FileGroups["PRIMARY"].Files[0].GrowthType
 
    growth          = int(growth_raw)                 # Casting 'float' type to 'int' type
    growth_type     = str(growth_type_raw).lower()    # Casting 'FileGroup' type to 'str' type
 
 
    if growth_type == 'percent' and growth > 5:
 
        #Printing a String for the SNMP client to have an idea what this value stands for
        print INFOSTRING
        sys.exit(2)
    else:
 
        #Printing a String for the SNMP client to have an idea what this value stands for
        print INFOSTRING
        sys.exit(0)
 
 
if __name__ == "__main__":
    main()

<!-- /etc/opennms/poller-configuration.xml -->
 
...
<package name="example1">
...
        <service name="MSSQL-DB-Autogrowth" interval="300000" user-defined="false" status="on">
            <parameter key="retry" value="2"/>
            <parameter key="timeout" value="3000"/>
            <parameter key="port" value="1161"/>
            <parameter key="oid" value=".1.3.6.1.4.1.8072.1.3.2.3.1.4.12.100.98.97.117.116.111.103.114.111.119.116.104"/>
            <parameter key="operator" value="="/>
            <parameter key="operand" value="0"/>
            <parameter key="reason-template" value="The value of automatic database growth changed \ 
                                                    from a fixed value to a percent value greater than 5.\
                                                    Expected value should be 50 MB fixed or lower"/>
        </service>
...
</package>
...
<monitor service="MSSQL-DB-Autogrowth" class-name="org.opennms.netmgt.poller.monitors.SnmpMonitor"/>
...