How to Monitor SQL Server Disk Space? Its Only A 4 Step Solution
Are you the one who is looking for approaches on how to monitor SQL server disk space?
You are getting different approaches on different websites due to which you are confused with what actually to do?
Lots of confusion in mind but, getting nothing in hand! Don’t worry here we came with solution to your problem. Checking the Microsoft SQL Server disk space is one of the essential task for all DBAs around the world. They can avoid data loss or sudden downtime at the time of monitoring procedure with help of the method mentioned in this post.
It’s A Good Habit To Check / Monitor Disk Space Usage SQL Server
DBAs must monitor MS SQL server disk space for ensuring that machines and user databases files are having enough storage space. If an administrator ignores this checking task, he or she might face following problems in SQL Server :
- All the jobs in SQL server will get terminated on their own, without any prior notice. If the database file is not having required amount of space, file will not be able to record operations. This will lead to repetitive termination when an individual tries to perform jobs on the server.
- The transaction log file might enter and all operations of DML on database might get failed. When this happens, a message is displayed through which admin came to know that time recovery is lost.
- If log files or tempdb data are unable to grow for accommodating the server activities, it will lead to failure in use of tempdb.
- Automatic server operations will not work in a way they should work.
Steps to Monitor SQL Server Disk Space
There exists four major steps to check disk space on SQL server.
a) Auto-growth Settings for Databases
b) Monitoring SQL Database File Sizes
c) Make use of SQL Server trace files
d) Perform monitoring in SQL Server
Description of these four steps is mentioned in brief below!
#1 : Auto-growth Settings for Databases
In Microsoft SQL Server, the database files and logs grow on the basis of auto-growth settings. These settings are inherited from the database model. Auto-growth setting comprises of three different options :
Several database administrator go with these default options but, it is not a good idea. Reasons for the same are :
- Continuous auto-growing technique makes log files and database fragmented.
- This might impact the ongoing performance of apps associated with SQL server.
Now you must be thinking that what to do then? Don’t worry, continue reading this measures!
The suitable measure for this step to monitor SQL server disk space is to minimize total number of auto-growths.
For example – If you are assuming the database to grow 10 percent in one year, you might desire size value 30% for enabling plenty of space for starting.
In case of database files, the suitable measures is not to go for growth in percentage but, in megabytes. You have to select a numeric value, which will work for your server database files.
For example – small database requires growth in megabytes to 1Gig in which VLDBs may get set to 10Gig or above. The reason behind doing so is ‘the initial percentage growth for 400 Gig database will be 40 Gigs, which is undoubtedly a huge jump at one time’. Defining settings either in 1 Gig or more, will result in auto-growths creation in a controlled manner.
Now its time to define the size of transaction log file, which can be done via following steps :
- Shrink the log file as much possible
- Copy them in every 10 to 20 minutes
- Weekly monitor the growth by keeping maximum size value in mind
- At last, copy the log, shrink it again to its minimum size and then, manually observe it to determine its maximum size.
#2 : Monitoring SQL Database File Sizes
Xp_fixeddrives indicates the free space, present in the local fixed drives. You have to use following script to determine disk sizes :
When you are done with script execution in SQL Server, run the following script too :
The outcome of this script execution will be in form of a table structure. You can sum up the values to achieve the total size occupied by all database files present in your server.
#3 : Make Use of SQL Server Trace Files
This step to monitor SQL Server disk space is considered as the best. It is so because it enables administrators to detect actual data time and auto-growths of log files. You can setup trace in your server with help of following script :
After this, you can read your trace with help of following SQL server script :
#4 : Perform Monitoring in SQL Server
Finally, here comes the last stop to check SQL server disk space. The server users can make use of performance monitor to check log auto-growth – there is no option availed for growths of data files. This step enables admins to analyze the performance of SQL server database Autogrowth, percent log used, and data files size.
Performance monitor enables DBAs to have a look on all database or particular one. If required, one can raise an alter for sending net notifications or writing events to the Microsoft Windows NT application log.
Its Time to Wrap Up
It is an essential task for all DBAs to monitor SQL server disk space. Failure in this task might result in unexpected data loss and downtime. So, why to ignore the things that are important for the server management. Use the method provided in this post and check the size of SQL server disk on regular basis.
