Overview
For flawless working of SQL Server, administrator needs to keep check on disk space availability in the Server. It is important for a DBA to manage and monitor the disk space usage in SQL Server. Every time the data present in SQL server is keeps on growing as there are transactions, operations and data backup taking place in Server.
If the admin do not check the space availability, the server will run out of disk space which will restrict the user to deploy any operation on SQL Server. For instance if the user will add any data in SQL server, it will not accept as the disk space is full. To monitor server disk space in SQL server there is monitoring tools that will help DBA to identify the status disk space. In this article we will discuss the different space availability monitoring tools.
SQL Space Availability Monitoring Tools
Database admin has a responsibility to monitor the disk space availability, they must make sure that there is an enough space available for the databases, database backups and for any other types of files that they wants to store in server. If there is no enough space available in server, it will not accept any incoming process, which will results failure of that particular process. Monitoring server disk space in SQL server can be done with the help of procedures. There is a SQL Server procedure namely xp_fixeddrives that will returns the free space available in SQL server.
More About xp_fixeddrives Procedure
With the help of xp_fixeddrives procedure one can review the free space available but sometimes this procedure of monitoring free space will becomes very time consuming. DBA can automate the process of running this procedure periodically. When the DBA automate this command, it will return the free space value after specific duration of time and in addition, it will alert the DBA when free space drops below a specific range. xp_fixeddrives also keep a track of free space status over a time for disk space capacity management.
Practical Implementation Of xp_fixeddrives Procedure
In order to understand xp_fixeddrives procedure in depth, lets take an example:
Firstly build a process that will notify the DBA when SQL Server disk drive falls below a specified threshold.
In order to automate the altering process, user needs to get xp_fixeddrives information by extending store procedures into a Server tables. To get the xp_fixessrives information into SQL temporary table, create a temporary table as give below:
here we have created a table namely 'information' that will insert the records that xp_fixeddrives will results.
This code itself does not notify the DBA, the code will only get the free space information from this temporary table.
To alert DBA about free disk space periodically, the SQL Server agent will collects the free space information for each SQL Server drive into a temporary table. Then for each drive user can get the free space information from this temporary table and can compare it to a threshold value that has been exist for each drive. If the availability of free space drops below the particular threshold value then it will automatically send an email to DBA using xp_sendmail.
Conclusion
In the end we conclude that monitoring server disk space in SQL Server is a very crucial task for database administrator. xp_ fixeddrives is a great tool that DBA can use to monitor disk space in SQL Server. By keeping a proper check on SQL server space usage, one can prevent SQL Server data from many critical problems.
EmoticonEmoticon