Invalid object name 'sysperfinfo'

[WillemDH]

Hello,

As I have not been looking into MSSSQL DB monitoring, but my boss decided today I have to set up some baselines, because we have some major db's going into prodcution around that time. Now I just tested the MSSQL database and MSSQL server wizards and these seem to work partially, but I have several issues...

When I try to monitor the size of a database, I get

[root@nagios01 libexec]# ./check_mssql_database.py -H database server -U nagisql -P password -I MSSI -T OperationsMgrDW --datasize -w 70000 -c 99000
SQL Server message 208, severity 16, state 1, line 1:
Invalid object name 'sysperfinfo'.
DB-Lib error message 208, severity 16:
General SQL Server error: Check messages from the SQL Server

When I try the same with the master db of the instance it works, same goes for all checks.. Most of them seem to work on the master db, but not on other db's...

[abrist]

So your checks only work on the default instance and fail on the others?

[WillemDH]

Yes and the account used to make the checks have the same permissions on both master and the other db..

[abrist]

The following links suggests that 'sysperfinfo' may only be available on the master db:
http://thomaslarock.com/2011/03/march-m ... sperfinfo/
http://forums.asp.net/t/1027698.aspx

[WillemDH]

Yes, ok, but DB size is something that would be considered as 'standard monitoring'? And I did specify --datasize.. The datasize of master db is not worth monitoring imo?

[abrist]

Apologies, I don't think I was very clear. As the plugin checks a metric for an instance, where the metric resides in the master db, the user you are connecting with needs select access to the sysperfinfo table of the master database

[WillemDH]

Ok, I wll check this with our MS SQL DB engineer.

[abrist]

great.

[WillemDH]

Andy,

In the meantime we made this work! The problem was not with permissions for the local user Nagios executes the check, but with the sysperfinfo object. We had to edit it to sys.sysperfinfo in order to make it work.

Code: Select all

BASE_QUERY = "SELECT cntr_value FROM sys.sysperfinfo WHERE counter_name='%s' AND instance_name='%%s';"
DIVI_QUERY = "SELECT cntr_value FROM sys.sysperfinfo WHERE counter_name LIKE '%s%%%%' AND instance_name='%%s';"

Another issue in the check_mssql_database.py we noticed is that

Code: Select all

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

In fact does the same as

Code: Select all

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

So we changed this manually to

Code: Select all

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

After which we got the corrrect DB Size. Maybe you can investigate this and if you agree, update the check_mssql_database.py plugin?

Willem

[slansing]

This is something you should talk to the creator of that plugin about :

http://exchange.nagios.org/directory/Pl ... ts/details

Great to hear that you got it figured out!