sqlserver probe checkpoint fail logfile_usage_with_avail_disk

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Next error/alarm is returned by the probe:

Profile: ProfileName- IAPI/logfile_usage_with_avail_disk/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'space_available', table 'tempdb.dbo.#space_tab__________________________________________________________________________________________________________000000000249'; column does not allow nulls. UPDATE fails.

 

Environment

• Component: UIM - SQLSERVER

Cause

For the logfile_usage_with_avail_disk the script used is 'fg_freeSpace_with_avail_disk'.

This is documented in:

What Queries (updated ) are used by the sqlserver Probe in UIM?

To gather the system fixed drive free space, the  (hidden) Stored Procedure 'xp_fixeddrives' is used.

In the script, when defining the temp table #space_tab, a 'NOT NULL' constraint is added to the column 'space_available'. The column 'space_available' is updated at the end of the script with the output of 'xp_fixeddrives'.

The checkpoint FG free space requires system rights (granted via sysadmin role) for the user used in sqlserver probe This role is required to run the SP: master.dbo.xp_fixeddrives

Resolution

fg_freespace_with_avail_disk and logfile_usage_with_avail_disk will only work for admin users.

To resolve the issue, you need to add the sysadmin role to the Login user used by the probe.

Additional Information

Required permissions for the sqlserver probe

UIM sqlserver probe user does not have permission to perform this action for checkpoints