sqlserver probe checkpoint fail logfile_usage_with_avail_disk

Products

• DX Unified Infrastructure Management (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

Release : 20.3

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?
https://knowledge.broadcom.com/external/article?articleId=44005

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

What are the required permissions for the sqlserver probe?
https://knowledge.broadcom.com/external/article?articleId=34640

UIM sqlserver probe user does not have permission to perform this action for checkpoints
https://knowledge.broadcom.com/external/article?articleId=111971