check_mssql_health Database Free reporting as Critical

  • Hi,



    Running the check_mssql_health plugin to check the database free size, we've got a database currently at around 30gb of size and it's reporting as critical with -161% free space. Has anyone else seen had this issue and resolved it.



    Thanks

     


    ./check_mssql_health --hostname=server


    --username=user --password=password --port=1433 --database=database --mode=dat


    abase-free --warning=200: --critical=300:


    CRITICAL - database ARSystem has -161.86% free space left | 'db_database_free_pc


    t'=-161.86%;200:;300: 'db_database_free'=-21909MB;27072.00:;40608.00:;0;13536.00


    'db_database_allocated_pct'=294.27%

  • Hi,


    --warning 200: --critical 300: means "warning if less than 200% free space left, Critical if less than 300% free space left". This makes no sense to me.
    I would expect something like "--warning 10: --critical 5:" or expressed in MB "--warning 100: --critical 50: --units MB".
    In order to find out why there is a negative value, please re-run the plugin with the -v option and post the result here.


    Gerhard

  • Hi,



    Results below.



    ./check_mssql_health --hostname=servername --username=word --password=word --port=1433 --database=Databasename --mode=database-free --warning=100: --critical=50: --units MB -vThu Aug 13 08:33:57 2009: SQL: SELECT @@VERSION ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ 'Microsoft SQL Server 2000 - 8.00.2282 (Intel X86) Dec 30 2008 02:22:41 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)' ]; Thu Aug 13 08:33:57 2009: SQL: SELECT SYSTEM_USER ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ 'word' ]; Thu Aug 13 08:33:57 2009: SQL: SELECT @@SERVICENAME ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ 'MSSQLSERVER' ]; Thu Aug 13 08:33:57 2009: SQL:  SELECT name, dbid FROM master.dbo.sysdatabases ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ [ 'master', 1 ], [ 'tempdb', 2 ], [ 'model', 3 ], [ 'msdb', 4 ], [ 'pubs', 5 ], [ 'Databasename', 6 ], [ 'dulstest', 7 ] ]; Thu Aug 13 08:33:57 2009: SQL:  SELECT * FROM #FreeSpace ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ [ 'C', '2532' ], [ 'D', '6767' ], [ 'E', '2875' ], [ 'F', '106719' ], [ 'T', '2320' ] ]; Thu Aug 13 08:33:57 2009: SQL:  SELECT SUM(CAST(used AS BIGINT)) / 128 FROM Databasename.dbo.sysindexes WHERE indid IN (0,1,255) ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ '35443' ]; Thu Aug 13 08:33:57 2009: SQL:  SELECT RTRIM(a.name), RTRIM(a.filename), CAST(a.size AS BIGINT), CAST(a.maxsize AS BIGINT), a.growth FROM Databasename.dbo.sysfiles a JOIN Databasename.dbo.sysfilegroups b ON a.groupid = b.groupid ARGS:$VAR1 = []; Thu Aug 13 08:33:57 2009: RESULT:$VAR1 = [ [ 'Databasename_dat', 'D:\\MSSQL\\Data\\Databasename.mdf', '4860016', '-1', 38400 ], [ 'Databasename_dat2', 'D:\\MSSQL\\Data\\Databasename2.NDF', '238600', '-1', 10 ] ]; CRITICAL - database Databasename has -21909.00MB free space left | 'db_databasename_free_pct'=-161.88%;0.74:;0.37: 'db_databasename_free'=-21909.00MB;100.00:;50.00:;0;13534.00 'db_databasename_allocated_pct'=294.32%Thu Aug 13 08:33:57 2009: DESTROY DBD::MSSQL::Server::Database with handle null nullThu Aug 13 08:33:57 2009: DESTROY DBD::MSSQL::Server::Database exit with handle null nullThu Aug 13 08:33:57 2009: disconnecting DBD with handleThu Aug 13 08:33:57 2009: DESTROY DBD::MSSQL::Server with handle null nullThu Aug 13 08:33:5 
    7 2009: DESTROY DBD::MSSQL::Server exit with handle null null

  • I finally found the bug. It's around line 750. Please change

    Code
    1. if ($maxsize == -1) {
    2. $calc->{datafile}->{$name}->{maxsize} =
    3. exists $calc->{drive_mb}->{$drive} ?
    4. $calc->{drive_mb}->{$drive} : 4 * 1024;


    to


    Code
    1. if ($maxsize == -1) {
    2. $calc->{datafile}->{$name}->{maxsize} =
    3. exists $calc->{drive_mb}->{$drive} ?
    4. ($calc->{datafile}->{$name}->{allocsize} +
    5. $calc->{drive_mb}->{$drive}) : 4 * 1024;


    or just wait until tomorrow. I will release an updated version of the plugin tomorrow.


    Gerhard

  • See this:


    root@pp-netserver:/usr/local/nagios/libexec# ./check_mssql_health -server pp01 -username=sa -password='123456' --database=N2_05_01 --mode=database-free --units MB
    OK - database N2_05_01 has 33125.44MB free space left | 'db_n2_05_01_free_pct'=98.83%;0.01:;0.01: 'db_n2_05_01_free'=33125.44MB;5.00:;2.00:;0;33516.44 'db_n2_05_01_allocated_pct'=2.61%


    ????? 33125.44MB free space - sucks. not true


    This is true:
    execute on the server ms sql 2005
    oSQL.EXE -S pp01 -d N2_05_01 -U sa -P 123456 -p -Q "EXEC sp_spaceused"


    Result:


    database_name
    database_size


    unallocated space


    ------------------------------------------------------------------------------


    -------------------------------------------------- ------------------


    ------------------


    N2_05_01


    1312.56 MB


    50.26 MB




    reserved data index_size unused


    ------------------ ------------------ ------------------ ------------------


    843960 KB 532384 KB 188392 KB 123184 KB


    What do you thing ? ?(

  • free space which can be allocated in the future



    I'm not neeeded monitoring this information, this is meaningless.


    how to avoid it ??