MS SQL Server Capacity missing

vadim
vadim
in LPAR2RRD
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

Comments

  • damerva
    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
    vadim
    edited December 2021
    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
    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
    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
    damerva
    seems like we will have to find a workaround for earlier versions, we will look into it
  • vadim
    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
    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
    vadim
    The patch helps, we have capacity now. Thanks a lot!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion