Home Directory Plugins Databases SQLServer Check MSSQL Database Stats

Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com

Login

Remember Me

Check MSSQL Database Stats

Current Version
1.1
Last Release Date
2011-09-10
Compatible With
  • Nagios 3.x
  • Nagios XI
Owner
License
GPL
Hits
145307
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
One of two check plugins written in Python exclusively for MSSQL. Works with SQL Server 2000, 2005 and 2008. Designed for easy installation and easy upgradability.
This plugin is geared explicitly for gathering information on specific databases rather than the database as a whole.

I also added a doc on the exchange, please refer to it if you're connecting to instances on an MSSQL database. It says Nagios XI on it but will still apply to Nagios Core as well:

https://assets.nagios.com/downloads/nagiosxi/docs/Nagios_XI_Installing_MSSQL_Wizard_Prereqs.pdf

Installation of the plugin is easy and involves a call to the python plugin pymssql which is included with most packaging software. For Ubuntu:

sudo apt-get install python-pymssql

For Red Hat based:

yum install pymssql # Some repos have it python-pymssql

Then it should run no problem. I am *VERY* open to feature additions and bugs, so please bring them to my attention if you find them/have a need for them.

Examples of use are:

./check_mssql_database.py -H 192.168.0.1 -U user -p password --transpsec -w 20 -c 30

Now keep in mind if you're going to using percentages the w and c get a little trickier. This plugin is Nagios Plugin compliant as far as the bounding goes, fully supporting the use of the : ~ and @ symbols. Here is an example of what you'd do for the warns and crits on Log Cache Hit Ratio:

./check_mssql_database.py -H 192.168.0.1 -U user -p password --logcachehit -w 95: -c 90:

This will return warning of the percentage goes below 95% and critical if it goes below 90%.

For information the the use of ~ : and @, refer to section 2.5 of the Nagios Plugin Guidline here: http://nagiosplug.sourceforge.net/developer-guidelines.html

Metrics that it currently covers:

--datasize Check size of database
--logfileusage Check Log File Usage
--activetrans Check Active Transactions
--transpsec Check Transactions/Sec
--logcachehit Check Log Cache Hit Ratio
--time2connect Check Time to Connect [DEFAULT]
--loggrowths Check Log Growths
--logshrinks Check Log Shrinks
--logtruncs Check Log Truncations
--logwait Check Log Flush Wait Times
--logflushes Check Log Flushes/Sec

These are for specific databases on a MSSQL, if you want to see these metrics applied on your entire database, you can specify the database _Total, but again, this plugin is geared towards checking specific databases.

Be sure to take advantage of using Python by compiling this plugin to a pyc if you're running lots of checks with it!

Changelog:

1.1 - Fixed port bug that only allowed default port. Thanks for the tip CBTSDon!
Fixed ungraceful exit if mode not specified | Thanks mike from austria
1.3 - Added ability specify instances.
Reviews (8)
byWSergii, February 3, 2019
What minimum rights should the user have for the script to work correctly?
byrblaas1975, February 15, 2018
Hi,

I think you should update the documentation..

as for the check_mssql_database.py there is an option mandatory -T
I cannot read anything about this.

As said I am unable to get a database size

Could you please update the documentation?
bysnord, March 2, 2016
I have grabbed Chad's updated plugin as SQL2012 relies on sys.dm_os_performance_counters. Only the default time2connect metric works. Any other metric throws:

Caught unexpected error. This could be caused by your sys.dm_os_performance_counters not containing the proper entries for this query, and you may delete this service check.

I have verified I can get the data with a manual tsql query.
byChadSikorra, January 13, 2014
Thanks for this plugin. By far the easiest to setup and use for MS SQL checks. I've done some modifications to allow checking all databases in an instance in addition to just a specific one. Perhaps check it out sometime and merge the changes if possible?

https://github.com/ChadSikorra/check_mssql_collection
bywillemdh, November 1, 2013
Hello,

In http://support.nagios.com/forum/viewtopic.php?f=16&t=21791
I've mentioned a bug.
Another issue in the check_mssql_database.py we noticed is that

[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]

In fact does the same as

[code]'loggrowths' : { 'help' : 'Log Growths',
'stdout' : 'Log Growths is %s',
'label' : 'log_growths',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]

So we changed this manually to

[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Data File(s) Size (KB)',
'type' : 'standard'
},[/code]

After which we got the corrrect DB Size. Maybe you can investigate this and if you agree, update the check_mssql_database.py plugin?
byHamzab, September 28, 2013
Thank you for this great plugin.

Though, with some databases I get the SQL Server message 208 with a message "Invalid object name 'sysperfinfo'."

any idea about the cause of that?

thanks
byazn.junix, December 13, 2011
How do i set the option to connect to SQL Server with instance?
bymeatball, November 1, 2011
Really great plugin.
The only thing i would like is to get the DB size in bytes or MB
Thanks!