sqlserver
SQL Server Monitor

The sqlserver probe will run selected SQL's to extract vital information about your SQL Servers. The following information iis presented to database-administrator as alarms and/or as a report:
  • Database uptime
  • Number of databases available
  • The data-file size for each database
  • Log-file size for each database
  • File-group size for each database
  • Table size for each database
  • The buffer cache-hit ratio
  • The log-file cache-hit ratio
  • The number of active users
  • The number of users currently logged onto the server
  • Number of deadlocks pr. second
  • Number of transactions pr. second
  • Number of database page reads/writes pr. second
  • Number of flush waits pr. second
  • Number of latch requests pr. second
  • Number of full scans (table or index) pr. seconds
  • The usage (growth/shrinking) of the transaction logs
  • Table/index fragmentation
  • Memory resources
  • CPU and I/O resources
  • Locking and locking resources
  • Free connections
  • Backup status
  • Long running queries (SQL Server 2005 only)
  • Long running jobs
  • Active connection ratio
  • Mirroring state of Mirror Database
  • Status of Mirroring Witness Server
  • Status of SQL Server instance hosting mirror database

Installation notes

Install probe on a robot running MS-SQL server or a PC installed with client software for SQL Server.

Known SQL Server 2000 problem

Sometimes SQL Server internal tables are not upated with current size values. This can cause negative size values in checkpoitns database_size, free_size, fg_free_size and table_size. Use command DBCC UPDATEUSAGE to correct the values in SQL catalog (Running this command on large databases can take long time).

Known SQL Server 2005 problem with av_fragmentation

SQL Server 2005 changed SQL syntax for some commands during SP2 maintenance. If you want to run the checkpoint av_fragmentation, you need to apply this maintenance, otherwise checkpoint will return an error 0x80040e14.

Migration considerations

Checkpoints "logic_fragment" and "scan_density" are not supported on SQL Server 2005. Use "av_fragmentation" instead. The checkpoint "database_status" has been replaced by "database_state". Database status values from SQL Server 2000 (and lower) are mapped to database state as defined in SQL Server 2005.

V2 QoS compatibility Mode

In the V3 the has_max flag has been added to following checkpoints: alloc_space fg_free_space free_connections free_space locks_used logic_fragment scan_density server_cpu server_io user_cpu workspace_memory If you have created the QoS definition for any of these checkpoints under V2 sqlserver probe, you will need to enable the checkbox "QoS V2 compatibility" on the general tab of the GUI to make sure all data is inserted correctly into the QoS database. If you want to use the V3 format (has_max), you have to delete the V2 generated QoS definitions for these checkpoints (all the data for these chekpoints will then de deleted). For any questions related to this, please contact tech support

Permissions Required

For SQL Server versions 9 and 10, set VIEW SERVER STATE permission on master database.
Also GRANT SELECT permission to a user for these tables
Table Names
  • master.sys.databases
  • master.dbo.sysperfinfo
  • msdb.dbo.sysjobsteps
  • msdb.dbo.sysjobs
  • .sys.database_files
  • .sys.partitions
  • .sys.allocation_units
  • .sys.internal_tables
  • .sys.filegroups

    For SQL Server version 8, GRANT SELECT permission to a user for these tables


    Table Names
  • master.dbo.sysprocesses
  • master.dbo.sysperfinfo
  • master.dbo.sysdatabases
  • msdb.dbo.backupset
  • master.dbo.sysfiles
  • master.dbo.sysindexes
  • master.dbo.sysfilegroups
  • Revision history
    Date Description State Version
    06.11.2020

    What's New:

    • Added support for SQL Server 2019.
    • VS2017 redist package version is upgraded to 1.01 from 1.00 as 1.00 was having a reboot issue.

    Template Migration Support:

    • sqlserver Enhanced MCS template version 5.42 to sqlserver Enhanced MCS template version 5.45.

    Fixed Defects:

    • Fixed an issue where the Agent job failure metric was not able to generate an alarm in an enhanced template profile. Support case number: 01328176
    • Fixed an issue where custom checkpoint testing is not working when TLS is enabled. Support case number: 31828218
    • Fixed an issue where custom checkpoints failed working on the update of the profile. Support case number: 01277100
    • Fixed an issue where the buffer_cache_hit_ratio metric data was not correct. Support case number: 20155334
    • Fixed an issue where sqlserver probe upgrade from lower version to 5.42 stops sending QoS. Support case number: 01316302
    • Fixed an issue where logfile_usage checkpoints for few databases are not working. Support case number: 01314137
    • Fixed an issue where robot authentication does not support multiple instances if configure through IM. Support case number: 32146644
    • Fixed an issue where SQL Server (Enhanced) template does not support "robot authentication" for multiple instances. Support case number: 32218256
    MD5 Checksum: 46ec3849a22bb297ba62281898678eb6
    SHA-1 Checksum: a6c0ab0f5fa1f9bda0475fd4399f7be08d130b11
    GA 5.45
    11.01.2019

    What's New:

    • Added Robot Service authentication as an authentication type for local SQL Server instances.
    • Added the Send Alarm option which can be enabled independent of the checkpoint.

    Fixed Defects:

    • Fixed an issue where the probe was crashing frequently due to a memory access violation. Support case number: 01192217
    • Fixed an issue where Status tab for log file sizes and free space percentages in the the sqlserver probe and the SQL DB server were displaying different values. Support case number: 01158019

    For Detailed Release Notes-Please refer

    https://docops.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN  

    Note: Support case(s) may not be viewable to all

    MD5 Checksum: d870611e01e517c550cd3ce04c69416d
    SHA-1 Checksum: 0585398fb8bcc0d8b44253c324d6d3b52f2d8150
    5.42
    09.08.2018

    Fixed Defects:

    • The probe was unable to connect to the SQL database when TLS 1.0 was disabled. Support case number: 01124963
    • The probe was unable to connect to the MSSQL database. Support case number: 01137055

    For Detailed Release Notes-Please refer

    https://docops.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Support case(s) may not be viewable to all

    MD5 Checksum: 3851deb57aff11e12fd82f858160c90c
    SHA-1 Checksum: 43d2de448248c6dd88d3bf26acdde1397e641c91
    5.41
    29.06.2018

    Fixed Defects:

    • Added support for SQL Server 2017.
    • Added a new field Connection (IM GUI) to remove the requirement of entering credentials again while adding custom checkpoints. For more information, see the (Optional) Create Custom Checkpoint section in sqlserver IM Configuration.

    Fixed Defect:

    • The probe crashed and did not start after maximum retries. Support case number: 00920071

    For Detailed Release Notes-Please refer

    https://docops.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Support case(s) may not be viewable to all

    MD5 Checksum: 2fa50ce1fa7737c6b6f522538f2200eb
    SHA-1 Checksum: c9e1e740dcd89ddea120c0e1326fe788586725fd
    5.40
    21.11.2017

    What's New:

    For Detailed Release Notes-Please refer to

    https://docops.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Support case(s) may not be viewable to all

    md5sum: 881203d06439a4ec14f6d0ac1a5ee0a6
    sha1sum: 3ad32f7c788c285a5ef1e5795643254db1cd9c30
    5.30
    24.03.2017 What's New:
    Added support for:
    • AES-128 encryption that is required for FIPS compliance, only through the IM interface.
    • Monitor Windows systems where FIPS encryption is enabled
    For more information, see Enable FIPS Encryption and Probe Specific Changes After Upgrade sections.

    md5sum: daa6057f0b331aa825b9a925b140b55d
    sha1sum: 682a72211d8b8e2ad7ec5741a9ef96e681e4ea27
    5.20
    30.09.2016 What's New:
    1. Added support for SQL Server 2016.
    2. Enhanced the aag_listener_state checkpoint. For more information, see the Probe Generates False Alarms for aag_listener_state Checkpoint section in sqlserver Troubleshooting .
    3. Added support to map checkpoint alarms to database instance name. For more information, see Known Issues and Workarounds.
    4. The probe supports monitoring with an IPv6 environment.
       Important! The system, where CA UIM is installed, must be configured with dual stack (IPv4-IPv6).

    Fixed Defects:
    1. The probe generated incorrect alerts for the probe backup checkpoints. Support case number 499988 (On Admin Console GUI) After launching the probe, it automatically deactivated the login_count, buf_cachehit_ratio, server_cpu, check_dbalive checkpoints. Support case number 363810
    2. The probe generated an application eventlog error in the destination node whenever a shared resource migrated between nodes of a 2-node Windows cluster. Support case number 326191
    3. The probe displayed incorrect unit of the QOS_SQLSERVER_LOGFILE_SIZE in the Performance Report. Support case number 246565

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Salesforce case(s) may not be viewable to all
    5.10
    08.07.2016 What's New:
    1. Added support to monitor the Always On Availability Groups in SQL Server 2012, and 2014.
    2. Added checkpoints to monitor the statistics of the wait checkpoints enabled in your profile.

    Fixed Defects:
    1. The probe successfully connected to the SQL Server database even when incorrect credentials were provided. Support case number 270048
    2. The probe returned a closed state for a command object when the query execution command is executed. Support case number 276257
    3. The probe now generates QoS value for log_size and used variables in the database_size checkpoint. Support case number 381334
    4. The probe was unable to connect to localhost or remote connections if the probe was installed on Windows Server 2008. Support case number 412841

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Salesforce case(s) may not be viewable to all
    5.00
    11.04.2016 What's New:
    Added support to unlock the database when the probe retrieves monitoring information. For more information, see sqlserver Troubleshooting.

    Fixed Defects:
    1. Updated the Release Notes for the required settings for a user without db_owner and sysadmin permissions. Support case number 00289881
    2. The probe generated corrupt QoS data when monitoring multiple targets. Support case number 292771
    3. The probe displayed incorrect unit for the allocated variable in the alarm message for the logfile_usage checkpoint. Support case number 319212

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Salesforce case(s) may not be viewable to all
    4.95
    22.01.2016 Fixed Defects:
    1. Probe was not generating QoS Messages at the specified poll interval. Support case number 246108
    2. Probe was not displaying the value in Bytes when lock_memory, connection_memory, optimizer_memory, sqlcache_memory, and total_memory checkpoints were executed. Support case number 246026

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Salesforce case(s) may not be viewable to all
    4.94
    30.10.2015 Fixed Defects:
    1. Probe was reporting a connection timeout in a time less than that specified in the checkpoint interval.Salesforce case 00154897
    2. Probe was displaying an error message while executing agent_job_failure checkpoint using Windows authentication. Salesforce case 00155101

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note: Salesforce case(s) may not be viewable to all
    4.93
    31.07.2015 Fixed Defects:

    1. Fixed a defect where the tempelate checkpoint was overriding the static checkpoint. Salesforce case 00165905
    2. Fixed a defect where the QOS was not always coming through Use FQDN As QoS Source. Salesforce case 00144129
    3. Fixed a defect where the remote probe was reporting a connection timeout before the scheduled checkpoint interval. Salesforce case 00154897
    4. Added user setting information for SQL Server 2014. Salesforce case 00162883

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN

    Note:Salesforce case(s) may not be viewable to all
    4.92
    29.12.2014 Fixed a defect where the Use Excludes check box is disabled when a new custom checkpoint is created. (Salesforce Case: 00151079)
    Fixed a defect where the user_cpu checkpoint returns the CPU usage value greater than 100%. (Salesforce Case: 00147822)

    For Detailed Release Notes-Please refer
    https://wiki.ca.com/rest/ca/product/latest/topic?format=rendered&language=&space=UIMPGA&hid=sqlserver_RN
    Note:Salesforce case(s) may not be viewable to all
    4.91
    12.12.2014 1. Added support for SQL Server 2014.
    2. Fixed a defect where the connection timeout value was always set to 2 seconds even after assigning a different value. (SalesForce Case ID: 00147043)
    3. Fixed a defect where the check_dbalive_2 default message is enclosed in quotes. (Salesforce Case: 00151089)
    4. Fixed a defect where the probe did not start if the profile name length exceeded 60 characters. (Salesforce Case: 00148591)
    For Detailed Release Notes-Please refer
    https://wiki.ca.com/display/UIMPGA/SQL+Server+Monitoring+%28sqlserver%29+Release+Notes
    Note:Salesforce case(s) may not be viewable to all
    4.90
    13.10.2014 1. Fixed a defect where the probe was unable to create and save files for some metrics. (Salesforce Case: 00129730)
    2. Fixed a defect where the probe was unable to unlock the configuration file. (Salesforce Case: 00142853)
    For Detailed Release Notes-Please refer
    http://docs.nimsoft.com/prodhelp/en_US/Probes/AdminConsole/sqlserver/ReleaseNotes/index.htm
    4.85
    17.01.2014 Defect Fixed:
    1.On creating a threshold for alarms from Status tab, the object name was not appearing as per expectations.

    2.During unauthorized query changes for the custom checkpoints, the alarm was generated and was repeating the message again and again. This defect has been fixed. The alert message is generated once and the checkpoint is deactivated as expected.
    For Detailed Release Notes-Please refer
    http://docs.nimsoft.com/prodhelp/en_US/Probes/AdminConsole/sqlserver/ReleaseNotes/index.htm
    4.84
    09.10.2013 Issue with sqlserver probe and delay threshold settings. 4.83
    26.06.2013 Modified Probe Defaults 4.82
    28.03.2013 Fixed: Garbled data coming in alarm message for user_waits checkpoint. 4.81
    14.03.2013 added features in user_waits for blocking length and the include, exclude feature in exclude of user_waits. Fixed: Multiple conf_sqlserver_4.40.exe are created while testing sqlserver probe. Fixed: Changes getting applied on click cancle button also. Fixed: Details of one custom checkpoint gets displayed on another custom checkpoint. Added feature in backup_status for ignoring databases for no of days, restoring databases and database snapshots Added feature for clear alarm for profile level alarms Fixed: Errors wrt checkpoint logfile_usage and logfile_size Fixed: Alarms related to float values Fixed: av_fragmentation checkpoint reporting NULL table values Fixed: Probe shows FQDN as QOS source Added feature in long_jobs for active jobs, their name, job run time and name of category 4.80
    24.01.2013 Fixed a defect where checkpoint's check_interval was not working. 4.72
    24.12.2012 Updated the mirror state values in the hints section of mirror_state checkpoint. 4.71
    14.12.2012 Added a feature in custom checkpoints which will allow thresholds to be defined for multiple columns. Added functionality to set up a schedule for each alarm in custom as well as built in checkpoints. Added functionality to set up key specific alarms in custom checkpoints. Added new checkpoint(fg_freeSpace_with_avail_disk) which monitors free space in filegroups after considering the available disk size. Fixed check_dbalive checkpoint to send value "0" instead of "NULL" as QoS for connection failure. Fixed an issue related to incorrect calculation for fg_free_space checkpoint. Fixed an issue related to negative values for the checkpoints fg_free_space and free_space. Fixed an issue in custom checkpoints where NULL QoS with invalid keys were generated in case of any database related errors. Fixed an SOC issue of CM Authentication Failure. 4.70
    27.09.2012 Fixed an issue where the "Windows Authentication" method allows you access to the server with or without providing a valid user account. Fixed an issue where the "Windows Authentication" method on local system is connecting using system account rather than the domain account configure in the connection. Added a feature "suppress all alarms" so that all the alarms will be suppressed. Added a feature so that a monitoring profile does not run concurrently and the delay alarm raised whenever the profile run is delayed. Fixed an issue where the profile shows template checpoints even though the group is selected. Fixed the issue with logfile_usage checkpoint which was reporting incorrect values. Fixed an issue where probe was crashing if "detect domain automatically" is selected on a machine which is not on domain. Added Support for MS-SQl2012 4.60
    20.06.2012 Fixed an issue with backup_status checkpoint was reporting wrong value. 4.41
    01.06.2012 Added a new checkpoint suspect_pages for reporting if suspect pages are logged for databases.
    Added a new checkpoint agent_job_failure for reporting failed agent jobs within a defined interval.
    Added new checkpoints ls_primary_status, ls_secondary_status, ls_primary_time_since_last_backup, ls_secondary_time_since_last_copy, ls_secondary_time_since_last_restore and ls_secondary_last_restored_latency checkpoints for monitoring Log Shipping in SQLServer 2005 and above.
    Added a callback that able to specify wildcard or regex in profile_name value to fetch active profiles.
    Fixed an issue where active_connection_ratio checkpoint was not working.
    Fixed an issue where checkpoint schedule was running for an extra minute.
    4.40
    29.03.2012 Fixed an issue where the probe was failing to pick up no. of samples(overridden) correctly for static checkpoints.
    Fixed a logging issue where the probe was incorrectly logging sqlserver password in plain text.
    Fixed an issue where in some cases the probe was failing to return any rows for custom checkpoint queries.
    Fixed QOS V2 compatibility issue, earlier the probe was not able to send the QOS as per V2 QOS specification
    Fixed an issue in manual signed stored procedure feature related to permissions.
    Fixed an issue where the probe was incorrectly converting metric units(KB/MB/GB etc.) for some checkpoints
    Fixed an issue where the probe was not able to return any rows in case of complex custom checkpoint queries.
    Fixed an issue in logfile_size and logfile_usage checkpoints where the checkpoints were failing when any database is in the middle of recovery. The probe now skips the databases which are being recovered until the recovery is complete and database is online.
    4.30
    08.03.2012 Fixed Misspelling in description of lock_requests checkpoint
    Fixed Size reporting for large databases.
    4.22
    30.12.2011 Fixed SOC issues 4.21
    25.08.2011 SOC Support Added. Added support for signed store procedure for standard and custorm checkpoints queries. Probe can be run in standard as well as in sign mode. Added new checkpoints mirror_state, mirror_witness_server and mirror_sqlinstance for monitoring Database Mirroring state, status of witness server and status of sql server instance hosting mirroring database. Fixed an issue where sqlusr_cpu store procedure are not deleted after executing queris in case of SQL Server 2000. Modified qos_key value for user_cpu checkpoints for avoiding large amount of QoS. Fixed an issue related to subsystemid field where subsystemid shows wrong value. Fixed an issue where long_jobs checkpoint do not send any alarms. Fixed an issue where logic_fragment checkpoint gives Lock request time out error. Fixed Handle leak issue. Added support for configuring unit as minutes, hours and days in backup_status, transaction_backup_status and differential_backup_status checkpoints. Added a new error alarm message that will be send in case of checkpoint query execution failure. 4.20
    08.04.2011 Added support for internationalization.
    Added support for reading alarm tokens from cfg.
    4.11
    30.09.2010 Fixed division by zero errors for Logfile_Usage and Logfile_Size checkpoints.
    Added interval_value variable in custom checkpoints, so QoS can be sent on interval_value.
    Updated QoS definition of logfile_size i.e. removed qos_max.
    4.01
    14.09.2010 Added a new checkpoint logfile_size for reporting database log file size in MB.
    Initial implementation of sqlserver probe based on V4 database framework.
    Fixed defects in probe and GUI
    Added support for extended NIS database information.
    Implemented support for alarms.cfg .
    Added a new checkpoint blocked_users for calculating blocked user connections
    Added support for authentication on untrusted domain
    Fixed an issue in backup_status checkpoint where it was incorrectly reporting logfile_usage QOS
    Fixed a crash in GUI configurator related to editing message variables
    Fixed the white space issue in GUI configurator
    Added support for includes functionality in the probe. This feature works just the same as exclude feature
    Fixed an issue in fg_free_space checkpoint where incorrect values are being reported by the probe
    Added support for reporting QoS values for long_queries checkpoint
    Added a new checkpoint active_connection_ratio for reporting active connection ratio
    Added two new checkpoints transaction_backup_status and differential_backup_status for reporting transaction and differential backup status
    Fixed an issue in GUI configurator where the "Alarm Severity filter" field on the setup tab was editable, now the field is made non-editable
    Added support for creating custom checkpoints for reporting per-second metric values
    Added support for configuring separate sql timeouts for checkpoints
    Added 64-bit support
    Updated cfx file for message variables in 'server_startup' checkpoint.
    4.00
    14.08.2010 Fixed security token leak by closing the security tokens when they are not required.
    3.14
    15.09.2009 In case of custom checkpoints, the query password was not always saved properly. Fixed the query password encryption in GUI.
    Note: If any custom checkpoints are deactivated by the probe, those checkpoints will have to be deleted from the GUI and will have to be added again in the probe.
    3.13
    23.01.2009 SQL Native Client 2008 support. 3.11
    18.12.2008 Query for checkpoint free_connections corrected.
    Treshold migration for check_dbalive corrected.
    V2 QoS compatibility modus
    3.07
    02.10.2008 Problem with crashes in multithreaded environment fixed.
    Query database_size for SQL Server 2000 corrected.
    Several other queries for SQL Server 2000 corrected.
    Memory leak problem fixed.
    3.05
    02.09.2008 Problem with empty password solved 3.03
    28.08.2008 Windows authorization support corrected.
    queries logfile_usage and fg_free_space corrected
    3.02
    15.05.2008 Multithreading - every profile runs in a separate thread.
    Scheduling - every checkpoint can have its own schedule.
    Multiple thresholds - every checkpoint can have more than one threshold.
    Message pool - messages can be customized and reused among checkpoints.
    Connection retry - for every connection there can be number of attempts defined, before the probe raise an alarm.
    Resizable GUI windows - all wndows with lists are resizable.
    Multiple QoS per checkpoint
    Exclude lists
    # of samples for alarming
    Auto-update (GUI)
    Customer-created checkpoints
    New checkpoints: long_queries, long_jobs, backup_status
    Problem with occasional "probe may be hanging" message fixed
    Problem with database names containing "-" or other special charatec solved
    Support for case-sensitive database added
    "No response" parameter added into GUI
    3.00
    12.06.2007 problem with database names containing "-" or other special charatec solved
    support for case-sensitive database added
    "No response" parameter added into GUI
    2.14
    17.11.2006 Added library to enable support for SQL Server 2005.
    Integer overflow in free space calculation fixed.
    Hardcoded "oracle.log" removed.
    Long domain instance name possible.
    2.12
    07.04.2006 New checkpoint database_status
    Database name added to "locked_users" message
    2.10
    18.11.2005 new checkpoint alloc_space
    buf_cachehit_ratio corrected for SP3
    domain user-id support for Windows security
    Severity for "Database report generation time..." alarm changed (default "critical" now)
    2.09
    Requirements
    Platform: Please refer to the Platform Support Matrix located in the Download section of http://support.nimsoft.com
    Software: MDAC 2.5 or higher + ADO provider for database to be connected
    SQL Server Native Client (SQLNCLI), SQL Server 7.0 or higher
    Hardware: None