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. To monitor SQL server disk space is amongst them and users consider it 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. Moreover, there can be more than one reasons for such issues as fragmentation in SQL server can also be the reason. Let’s begin the article to know SQL server disk space monitoring ways in detail.
Table of Content
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.
Now, after you monitor SQL server disk space, let’s move further. It is obvious that after querying the information, 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.
Bringing It All Together
Finally, we can come to the conclusion that the above-mentioned ways are the best to implement to disk space alert in SQL server as well as SQL server disk space monitoring for users. However, users must execute these operations carefully without any sort of disruptions at all. Even a small mistake to monitor SQL server disk space can result in severe consequences.