Monitor SQL Server Disk Space And Implement Alert System
A server administrator’s job is to keep tab on all the activities and status of the platform. Monitoring SQL Server disk space on the server is amongst them and is considered extremely important from a DBA’s perspective. Once the server runs out of storage space, everything relying on it will start to crash and come to a halt. Even though database and log sizing is one of the finest technique to implement for managing disk space in an organized way. However, there are certain conditions where database are supposed to be left for growing which sometimes results in the disk running out of space for storage.
Monitor SQL Server Disk Space – Limitations
It is a trivial matter to detect the available disk space on a server while it is running live. However, it can still be configured in a way that enables an administrator to receive alerts when the server starts to run low on disk space.
Checking Available Space on Disk
Before the 2008 R2 Service Pack 1 version of SQL Server, the only best method is to monitor SQL Server disk space available on the server was to make use of the undocumented extended procedure – ‘xp_fixeddrives’.
Its usage is simple yet you can’t do a lot with the outcome directly. Moreover, one must output the result to a table variable or temporary table if you need to perform any programmatic evaluation using the results of the extended procedure. Following is an example of the same where the query results in a list of any / all drives that have disk space less than 5 GB available:
R2 Service Pack 1 and Later Versions of SQL Server
Microsoft in fact made it a lot easier for administrators to query the server for available disk space information with its R2 SP1 and later versions. This provision has been done with the help of SUPPORTED dynamic management function, i.e. sys.dm_os_volume_stats().
The best thing about the DMF is that it won’t provide access to all the volumes available on the server but only those with your database LIVE. This makes it easier for the administrator at no extra time has to be invested upon filtering the results via table / variable creation.
NOTE: The available space is reported in ‘bytes’ and not in MBs therefore elevating the level of difficulty a little bit.
It is obvious that once you have queried the information then just setting up a stored procedure or a self-generated script is required for its regular execution via the server Agent job for sending out alerts in case a threshold has been hit.
Method 1: This one can be implemented in SQL versions 2000 and above as it employs a CDO for sending out alert emails through a call to a ‘custom’ SP, i.e. sp_send_cdosysmail. This stored procedure is a medium for sending alerts through the mail from the servers that do not have SQL Mail (Outlook) installed on them.
Method 2: Similarly, someone with SQL 2008 R2 SP1 and later can go for the new ‘sys.dm_os_volume_stats()’ DFM.
Nevertheless, both ways, implementing an alert system that processes every 5 minutes, for instance, is the best way to monitor SQL Server disk space to ensure that ample of storage is available in the database and logs.