MS SQL Server Capacity missing

vadim

MS SQL Server monitoring can get almost all the data, but cannot get Capacity:

undefined sqlserver_configuration_Capacity__size: last day
/home/lpar2rrd/lpar2rrd/data/SQLServer/sql-fc1-i02/ais/Capacity/capacity.rrd does not exist /home/lpar2rrd/lpar2rrd/bin/detail-graph-cgi.pl:36945 

[lpar2rrd@ctc-xorux lpar2rrd]$ ll /home/lpar2rrd/lpar2rrd/data/SQLServer/sql-fc1-i02/ais/*
/home/lpar2rrd/lpar2rrd/data/SQLServer/sql-fc1-i02/ais/Counters:
total 9060
-rw-r--r-- 1 lpar2rrd lpar2rrd 9275096 Dec 20 15:10 counters.rrd

/home/lpar2rrd/lpar2rrd/data/SQLServer/sql-fc1-i02/ais/Virtual:
total 2332
-rw-r--r-- 1 lpar2rrd lpar2rrd 2385512 Dec 20 15:10 virt.rrd

[lpar2rrd@ctc-xorux lpar2rrd]$ tail -n 12 /home/lpar2rrd/lpar2rrd/logs/error.log-sqlserver

[Mon Dec 20 15:40:04 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:40:04 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:03 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.
[Mon Dec 20 15:45:04 2021] sqlserver-db2json.pl: DBD::ODBC::st fetchrow_hashref warning: no select statement currently executing at /home/lpar2rrd/lpar2rrd/bin/sqlserver-db2json.pl line 401.

SQL Server 2012
ODBC driver 17

damerva

Hello,

1. Do you see all the DBs that are missing capacity in Health Status?

2. Have you granted this permission? "GRANT CONNECT ANY DATABASE TO lpar2rrd;"?

vadim

1. Yes, Health Status shows all DBs. All are green.

2. SQL Server 2012 has no "CONNECT ANY DATABASE" permission, so I mapped lpar2rrd to db_datareader in each DB.

damerva

I see.

1. Are there tables under Capacity? (Relations, Filegroups)
2. Can you try to run this query on one of the DBs and let us know its result? "exec sp_spaceused @oneresultset = 1"

vadim

1. Relations and Filegroups show data. They are ok.

2. "@oneresultset" results in error:

Msg 8145, Level 16, State 1, Procedure sp_spaceused, Line 0
@oneresultset is not a parameter for procedure sp_spaceused.

damerva

seems like we will have to find a workaround for earlier versions, we will look into it

vadim

Many thanks.

Also there are some other gaps or oddities (in Configuration, Sessions, Latency etc) that I will not describe here (since this is not very important for me now), but if you need this information on SQL 2012, please let me know.

damerva

This patch should help with getting the capacity data for SQL Server instances with versions lesser than 2016. Would you mind testing it?

https://download.lpar2rrd.com/patch/7.30-1-4-g6e97f/sqlserver-db2json.pl.gz

Gunzip it and copy to /home/lpar2rrd/lpar2rrd/bin (755, lpar2rrd owner)

-rw-rw-r-- 1 lpar2rrd lpar2rrd 23629 Jan  6 11:56 sqlserver-db2json.pl

If your web browser gunzips it automatically then just rename it: mv sqlserver-db2json.pl.gz sqlserver-db2json.pl

Assure that file size is the same as on above example

vadim

The patch helps, we have capacity now. Thanks a lot!