check_mssql_health is a plugin, which is used to monitor different parameters of a MS SQL server.


Command line parameters

  • –hostname <hostname>
    The database server
  • –username <username>
    The database user
  • –password <password>
    The database passwort
  • –port <port>
    The port, where the server listens (Default: 1433)
  • –server <server>
    An alternative to hostname+port. <server> will be looked up in the file freetds.conf.
  • –mode <modus>
    With the mode-parameter you tell the plugin what you want it to do. See list below for possible values.
  • –name <objectname>
    Several checks can be limited to a single object (e.g. a specific database). It is also used for mode=sql. (See the examples)
  • –name2 <string>
    If you use –mode=sql, the SQL-statement will be shown in the plugin output and the performance data (which looks ugly). The parameter name2 can be used to provide a used-defined string.
  • –warning <range>
    Values outside this range result in a WARNING.
  • –critical <range>
    Values outside this range result in a CRITICAL.
  • –environment <variable>=<wert>
    It is possible to set environment variables at runtime with htis parameter. It can be used multiple times.
  • –method <connectmethode>
    With this parameter you tell the plugin, which connection method it should use. Known values are: dbi for the perl module DBD::Sybase (default) and sqlrelay for the SQLRelay proxy..
  • –units <%|KB|MB|GB>
    This parameter adds units to the performance, when using mode=sql
  • –dbthresholds
    With this parameter thresholds are read from the database table check_mssql_health_thresholds


Keyword Meaning Threshold range
connection-time Measures how long it takes to login 0..n Sek (1, 5)
connected-users Number of connected users 0..n (50, 80)
cpu-busy CPU Busy Time 0%..100% (80, 90)
io-busy IO Busy Time 0%..100% (80, 90)
full-scans Number of full table scans per second 0..n (100, 500)
transactions Number of transactions per second 0..n (10000, 50000)
batch-requests Number of batch requests per second 0..n (100, 200)
latches-waits Number of Latch-Requests per second, which could not be fulfilled 0..n (10, 50)
latches-wait-time Average time a Latch-Request had to wait until it was granted 0..n ms (1, 5)
locks-waits Number of Lock-Requests per second, which could not be satisfied 0..n (100, 500)
locks-timeouts Number of Lock-Requests per second, which resulted in a timeout 0..n (1, 5)
locks-deadlocks Number of Deadlocks per second 0..n (1, 5)
sql-recompilations Number of Re-Compilations per second 0..n (1, 10)
sql-initcompilations Number of Initial Compilations per second 0..n (100, 200)
total-server-memory The main memory reserved for the SQL Server 0..n (nearly 1G, 1G)
mem-pool-data-buffer-hit-ratio Data Buffer Cache Hit Ratio 0%..100% (90, 80:)
lazy-writes Number of Lazy Writes per second 0..n (20, 40)
page-life-expectancy Average time a page stays in main memory 0..n (300:, 180:)
free-list-stalls Free List Stalls per second 0..n (4, 10)
checkpoint-pages Number of Flushed Dirty Pages per second 0..n ()
database-online Prüft, ob eine Datenbank online ist und Verbindungen akzeptiert -
database-free Free space in a database (Default is percent, but –units can be used also). You can select a single database with the name parameter 0%..100% (5%, 2%)
database-backup-age Elapsed time since a database was last backupped (in hours). The performancedata also cover the time needed for the backup (in minutes) 0..n
database-logbackup-age Elapsed time since a database log was last backupped (in hours). The performancedata also cover the time needed for the backup (in minutes) 0..n
database-file-auto-growths The number of File Auto Grow events (either data or log) in the last <n> minutes (use –lookback) 0..n (1, 5)
database-logfile-auto-growths The number of Log File Auto Grow events in the last <n> minutes (use –lookback) 0..n (1, 5)
database-datafile-auto-growths The number of Data File Auto Grow events in the last <n> minutes (use –lookback) 0..n (1, 5)
database-file-auto-shrinks The number of File Auto Shrink events (either data or log) in the last <n> minutes (use –lookback) 0..n (1, 5)
database-logfile-auto-shrinks The number of Log File Auto Shrink events in the last <n> minutes (use –lookback) 0..n (1, 5)
database-datafile-auto-shrinks The number of Data File Auto Shrink events in the last <n> minutes (use –lookback) 0..n (1, 5)
database-file-dbcc-shrinks The number of DBCC File Shrink events (either data or log) in the last <n> minutes (use –lookback) 0..n (1, 5)
failed-jobs The number of jobs which did not exit successful in the last <n> minutes (use –lookback) 0..n (1, 5)
sql Result of a user-defined SQL statement, which returns a numerical value. The statement is passed to the plugin as an argument to the –sql parameter. A label for the performancedata can be defined with the –name2 parameter. A unit can be appended by using –units. If the SQL statement contains special characters, it is recommended to encode it first by calling check_mssql_health with the –encode parameter and sending the statement to STDIN 0..n
sql-runtime Runtime of a custom sql statement in seconds 0..n (1, 5)
list-databases Returns a list of all databases -
list-locks Returns a list of all locks -

Please note, that the thresholds must be specified according to the Nagios plug-in development Guidelines.

  • “10” means “Alarm, if > 10” and
  • “90:” means “Alarm, if < 90”

Preparation of the database

In order for the plugin to operate correctly, a database user with specific privileges is required.

The most simple way is to assign the Nagios-user the role “serveradmin”. As an alternative you can use the sa-User for the database connection. Alas, this opens a serious security hole, as the (cleartext) administrator password can be found in the nagios configuration files

Birk Bohne wrote the following script which allows the automated creation of a minimal, yet sufficient privileged monitoring-user.

declare @dbname varchar(255)
declare @check_mssql_health_USER varchar(255)
declare @check_mssql_health_PASS varchar(255)
declare @check_mssql_health_ROLE varchar(255)
declare @source varchar(255)
declare @options varchar(255)
declare @backslash int

SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'Rolename'


- Example for Windows authentication:
  SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
  SET @check_mssql_health_ROLE = 'Rolename'

- Example for SQL Server authentication:
  SET @check_mssql_health_USER = 'Username'
  SET @check_mssql_health_PASS = 'Password'
  SET @check_mssql_health_ROLE = 'Rolename'

It is strongly recommended to use Windows authentication. Otherwise
you will get no reliable results for database usage.


SET @backslash = (SELECT CHARINDEX('\', @check_mssql_health_USER))
IF @backslash > 0
    SET @source = ' FROM WINDOWS'
    SET @options = ' WITH ' + @options
    SET @source = ''
    SET @options = ' WITH PASSWORD=''' + @check_mssql_health_PASS + ''',' + @options

PRINT 'create Nagios plugin user ' + @check_mssql_health_USER
EXEC ('CREATE LOGIN ' + @check_mssql_health_USER + @source + @options)
EXEC ('USE MASTER GRANT ALTER trace TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobhistory TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobschedules TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobs TO ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' created.'

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Grant permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] CREATE ROLE ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT EXECUTE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] CREATE USER ' + @check_mssql_health_USER + ' FOR LOGIN ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] EXEC sp_addrolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" granted.''')
      fetch next from dblist into @dbname
close dblist
deallocate dblist

Please keep in mind that check_mssql_health’s functionality is limited when using SQL Server authentication. This method is strongly discouraged . Normally there is already a Nagios-(Windows-)-user which can be used for the Windows authentication method.

Another script from the same author removes the monitoring user from the database.

declare @dbname varchar(255)
declare @check_mssql_health_USER varchar(255)
declare @check_mssql_health_ROLE varchar(255)

SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
SET @check_mssql_health_ROLE = 'Rolename'

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Revoke permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] EXEC sp_droprolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] DROP USER ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE EXECUTE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] DROP ROLE ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" revoked.''')
      fetch next from dblist into @dbname
close dblist
deallocate dblist

PRINT 'drop Nagios plugin user ' + @check_mssql_health_USER
EXEC ('DROP LOGIN ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' dropped.'

Many thanks to Birk Bohne for the excellent scripts.


nagsrv$ check_mssql_health --mode mem-pool-data-buffer-hit-ratio
CRITICAL - buffer cache hit ratio is 71.21% | buffer_cache_hit_ratio=71.21%;90:;80:

nagsrv$ check_mssql_health --mode batch-requests
OK - 9.00 batch requests / sec | batch_requests_per_sec=9.00;100;200

nagsrv$ check_mssql_health --mode full-scans
OK - 6.14 full table scans / sec | full_scans_per_sec=6.14;100;500

nagsrv$ check_mssql_health --mode cpu-busy
OK - CPU busy 55.00% | cpu_busy=55.00;80;90

nagsrv$ check_mssql_health --mode database-free --name AdventureWorks
OK - database AdventureWorks has 21.59% free space left | 'db_adventureworks_free_pct'=21.59%;5:;2: 'db_adventureworks_free'=703MB;4768371582.03:;1907348632.81:;0;95367431640.62

nagsrv$ check_mssql_health --mode database-free --name AdventureWorks \
--warning 700: --critical 200: --units MB
WARNING - database AdventureWorks has 694.12MB free space left | 'db_adventureworks_free_pct'=21.31%;0.00:;0.00: 'db_adventureworks_free'=694.12MB;700.00:;200.00:;0;95367431640.62

nagsrv$ check_mssql_health --mode page-life-expectancy
OK - page life expectancy is 8950 seconds | page_life_expectancy=8950;300:;180:

nagsrv$ check_mssql_health --mode database-backup-age --name AHLE_WORSCHT \
--warning 72 --critical 120
WARNING - AHLE_WORSCHT backupped 102h ago | 'AHLE_WORSCHT_bck_age'=102;72;120 'AHLE_WORSCHT_bck_time'=12

Using environment variables

You can omit the parameters –hostname, –port (or the alternative –server), –username und –password completely, if you pass the respective data via environment variables. Since version 3.x of Nagios you can add your own attributes to service definittions (custom object variables). They appear as environment variables during the runtime of a plugin.

The environment variables are:

  • NAGIOS__SERVICEMSSQL_HOST (_mssql_host in the servicedefinition)
  • NAGIOS__SERVICEMSSQL_USER (_mssql_user in the servicedefinition)
  • NAGIOS__SERVICEMSSQL_PASS (_mssql_pass in the servicedefinition)
  • NAGIOS__SERVICEMSSQL_PORT (_mssql_port in the servicedefinition)
  • NAGIOS__SERVICEMSSQL_SERVER (_mssql_server in the servicedefinition)


This Plugin requires the installation of the Perl-module DBD::Sybase.
After you unpacked the archive you have to execute ./configure aufgerufen. With ./configure –help you get a list of possible options.

  • –prefix=BASEDIRECTORY - The directory where check_mssql_health will be installed (default: /usr/local/nagios)
  • –with-nagios-user=SOMEUSER - The user who owns check_mssql_health sein. (default: nagios)
  • –with-nagios-group=SOMEGROUP - The group which owns check_mssql_health Binaries. (default: nagios)
  • –with-perl=PATHTOPERL - The path to a perl interpreter if you want to use a non-standard one. (default: the perl found in $PATH)

Security advice

The Perl-module DBD::Sybase is based on an installation of FreeTDS auf. This package is responsible for the communication with the database server. The default settings use protocol version 4.x which results in cleartext passwords sent over the wire. Please do change the following parameter in the file /etc/freetds.conf.

# TDS protocol version
# tds version = 4.2
tds version = 8.0


If multiple named instances are listening on the same port of your database server, you need to register them individually in the file /etc/freetds.conf.

        host =
        port = 1433
        instance = instance01

        host =
        port = 1433
        instance = instance02

Now you can address the instances e.g. with --server dbsrv1instance02 . By using --host --port 1433 you would reach the Default instance.

With recent versions of fteetds it is no longer necessary to maintain a freetds.conf. With --server <FQDN>\<Instanz> it should be possible to establish a connection. This requires a running Service Browser on the database server.




  • 2016-09-20
    ignore database snapshots in database-free
  • 2016-08-12
    fix a bug in database-free (was introduced in
  • 2016-08-10
    added Extraopts to the dist
  • 2016-08-05
    availability-group-health only for primary replica
  • 2016-08-03
    Remove a uninitialized-message in database-free&db-user
  • 2016-07-29
    availability-group-health only for versions >= 11.x
  • 2016-07-26 2.6.4
    add –mode availability-group-health
  • 2016-06-23
    use AlwaysOn tables only if enabled
  • 2016-06-23 2.6.3
    fix create-database-user, fix execute error handling
  • 2016-06-20
    update GLPlugin (encode)
    cleanup deprecated files
  • 2016-06-03
    update GLPlugin
  • 2016-05-11
    unset syb_flush_finish
    use NOLOCK for msdb.dbo.backupset, otherwise selects during backups take too long
  • 2016-05-02
    set syb_flush_finish
  • 2016-04-19 2.6.2
    let sql statements run into their private timeouts
    fix create-monitoring-user
  • 2016-04-04 2.6.1
    improve backup-age runtime for large number of databases
  • 2016-03-23 2.6
    add modes database-*free-details, database-filegroup-free, database-file-free
    improved calculation of free space
  • 2016-02-09 2.5
    add glplugin submodule
  • 2016-01-27
    bugfix in backup-age (The multi-part identifier “ar.replica_id” could not be bound). Korinthenkacker!
  • 2016-01-27 2.3.1
    bugfix in database-modes (“-“ in database name)
  • 2016-01-24 2.3
    add method sqsh and sqlrelay (to redesign)
  • 2016-01-24 2.2.2
    add sybase database* (to redesign)
  • 2016-01-19 2.2.1
    fix aps detection
  • 2016-01-15 2.2
    add aps modes
  • 2015-08-10 2.1
    add more modes
  • 2015-07-13 2.0.3
    new directory layout, standalone
  • 2015-04-23
    update GLPlugin[SNMP]
  • 2.0.2 2015-01-08
    mitigation of stderr and sql errors in sql-runtime is possible
  • 2.0.1 2014-10-01
    update GLPlugin
  • 2.0 2014-07
    complete redesign, based on
  • 2014-06-06
    allow mitigation for failed-jobs if no jobs were run
  • 2014-06-03
    add –commit which forces auto-commit on
  • 2014-04-01
    implement –negate old_level=new_level
    output also ok-messages for my-modes
    allow floating point numbers in thresholds
  • 2014-02-28
    bugfix in transactions. handles databases with auto-close
  • 2014-01-07
    add mode jobs-enabled (Thanks Thomas Gelf)
  • 1.5.20 2013-12-06
    handle wrong io_busy and cpu_busy values (hickups of 500% caused by counter overflows
    are replaced by the last valid value. if the error persists dirung 5 plugin runs
    the obviously wrong value is reported)
  • 2013-09-11
    fix an uninitialized state_desc
  • 2013-09-06
    parameter –notemp is now usable for many modes
  • 2013-05-27
    fixed a bug in batch-requests, which affected case sensitive colletion systems like SAP (Thanks Andreas Seemueller)
  • 1.5.19 2013-02-28
    rewrote database-free for sybase
  • 2013-01-22
    fixed a bug in sybase database-free
  • 1.5.18 2012-01-03
    added asciidoc
  • 1.5.17 2012-12-20
    fixed a bug in database-free for sybase (many thanks to siemens audiologische technik gmbh!!)
  • 1.5.16 2012-11-29
    fixed a bug in database-free (where the offline state of 1 db was propagated to some others)
    implemented all sorts of thresholds
    add mode sql-runtime
  • 2012-11-22
    catch generic error-messages
  • 2012-11-19
    catch a “insufficient-rights”-error-message
  • 1.5.15 2012-11-16
    add parameter mitigation (which can reduce errorlevels for offline databases or dbs which were never backed up)
    tracedebug ouputs a bit more information now
  • 1.5.14 2012-11-07
    database-free can now handle offline databases
    add –offlineok
    exclude dbs with recovery model simple from database-logbackup-age
  • 1.5.13 2012-10-25
    add failed-jobs
    add database-online
  • 1.5.12 2012-10-24
    add database-file-auto-growths (and database-logfile-auto-growths, database-datafile-auto-growths)
    add database-file-auto-shrinks (and database-logfile-auto-growths, database-datafile-auto-growths)
    add database-file-dbcc-shrinks
  • 1.5.11 2012-07-05
    add selects for cpu-busy to see the return values with -v
    add some enhancements writen by Pall Sigurdsson
  • 1.5.10 2012-06-27
    split database-backup-age to database-backup-age and database-logbackup-age (Thanks Simon Meggle)
    fix warnings for newer Perl versions
    fix cpu-busy & io-busy
  • 2012-04-12
    fix warnings for newer Perl versions (Thanks Stephan Classen)
  • 2012-03-15
    bugfix in timeout-alarm handling under windows
  • 2011-09-19
    fix a bug in –currentdb (with a “-“ in the database name”). (Thanks Markus Stollwerk)
    single ticks around the –name argument under Windows CMD will be removed auto matically
  • 1.5.9 2011-08-12
    fix a bug in save_state for statefilesdirs with capial letters
  • 2011-06-29
    fix a bug in sybase chained transaction handling
  • 2011-06-03
    sites in an OMD ( environment have now private statefile directories
    fix a bug in extra-opts
    conection-time, connected-users, database-free and backup-age can be used with sybase ase 15.x servers
  • 2011-01-19
    output a nicer error message if a sqlrelay connection fails
  • 2011-01-03
    bugfix in –mode sql (numeric vs. regexp result)
  • 1.5.8 2010-12-20
    mode sql can now have a non-numerical output which is compared to a string/regexp
    new parameter –dbthresholds
    new mode report can be used to output only the bad news (short,long,html)
  • 1.5.7 2010-09-10
    fixed a bug in database-free (too much free space was calculated when more than one datafile of a database was on the same filesystem). (Thanks Juergen Essberger)
    new parameter extra-opts
  • 1.5.6 2010-08-12
    new parameter –dbthresholds. thresholds can now also be deposited in the table check_mssql_health_thresholds
    added –currentdb so that the plugin can connect to a non-default database
    –with-mymodules-dyn-dir on the commandline overrides the configure-option of the same name
  • 1.5.5
    backup-age is now an alias for the “official” database-backup-age
    catch the “can’t change context to database” error
    –mode sqlcmd
  • 1.5.3 2009-11-02
    fixed a bug in mode database-free (results are more accurate now)
    added new mode backup-age which checks the age (in hours) of the last backup
  • 1.5.2
    fixed a bug where database names with special characters showed errors. (Thanks Hugh Ranalli)
  • 1.5.1 2009-05-26
    added –server which can be used instead of –hostname/port (Thanks Mark Monaghan)
    lots of fixes. sql server 2000 should now be fully supported. (Thanks Mereghetti Stefano)
    –warning/–critical were ignored for memory-pool-related modes (Thanks Mereghetti Stefano)
  • 2009-05-05
    fixed a bug which led to error messages when using the plugin with sql server 2000 (Thanks Christian Mies)
    fixed a bug so sql server 2000 can list-databases (Thanks Mereghetti Stefano)
  • 2009-04-29
    added a security advice to the README (Thanks Mathieu Barret)
    fixed a bug in database-free (Thanks Michael Luebben)
    fixed a typo (Thanks Bernd Staudacher)
  • 1.5 - 2009-03-20
    rewrote database-free so that unrestricted growth is taken into account (limit is disk)
    added support for SQLRelay
  • 1.2 - 2009-03-19
    added support for object_name <> SQLServer:… (this caused “unable to aquire”-errors sometimes)
    fixed a bug in the PNP template
  • 1.1 - 2009-03-11
    added modes: transactions, latches-wait-time, locks-waits, locks-timeouts,
    locks-deadlocks, sql-recompilations, total-server-memory
    beautified the PNP template
    fixed counter rollovers after database restart
  • 1.0 - 2009-03-10
    Initial release

Gerhard Laußer
Check_mssql_health is published under the GNU General Public License. GPL


Gerhard Laußer ( will gladly sell you consulting for MS SQL monitoring.