Learn How to Monitor SQL Server Disk Space in an Easy Way

Keeping eye on the amount of disk space available on SQL Servers is something that every database administrator duty. However, if SQL Server runs out of the disk space, then everything comes to a crashing halt. Therefore, it is important for DBA to monitor the SQL Server disk space on daily basis carefully to make sure that all databases have enough disk space for the applications using SQL Server.

Monitoring SQL Server instances and database disk space provides sufficient information required to diagnose and troubleshoot the SQL Server issues. Slow reporting, hiccups and bottlenecks can badly affect the business. Therefore, it is important to monitor SQL Server disk space regularly because regularly changing schema, configuration, and the data need some additional and manual tuning. Thus, in order to have a smooth tuning DBA needs to a proactive and monitor the performance constantly.Lets us learn how to monitor SQL Server disk space. But first let us understand the reason to monitor disk space in SQL Server

Why you need to Monitor SQL Server Disk Space?

• If the MSDB database is unable to grow, then all SQL Server jobs fail automatically. Moreover, the job information also fails to be logged and a user is not allowed to start all of the SQL Server jobs.
• All that which make use of a large amount of tempdb such as sorts, aggregates and operations may get fail if all the log files and the tempdb data cannot grow to adapt these operations.
• All operations on the database get to fail and the transaction log may get fill up completely and the point in time recovery is lost.
• It can also be possible that all files of the database may fill up themselves and all DML operations on the database may not be successful.
• If the database suffering from no growth of its transaction log or data files, then it may gets fill up the whole disk that makes other databases applications to fail also.

Auto-grow & Monitor SQL Server Disk Space – Know Different Methods

The most necessary operation that each DBA must do is monitor SQL Server database files. It is because monitoring helps in downtime and prevent data loss. Thus, in this segment, we will be discussing different methods to monitor auto-growths of SQL Server disk space.

Method #1: Log Files Auto-growth Settings

Auto-growth settings inherited from the model databases helps in growing the SQL Server logs and database files.

One can use the three options for auto growth:

• Enabled or disabled (the default for both data and log files is enabled).
• Increase in percent or MB (i.e. the default growth for log files is 10% and the default growth for data files is 1 MB)
• The maximum size of file/ unrestricted growth (it enabled by default for the both log & data files).

Except for these defaults, most of the DBAs having SQL Server database files auto growing, which is not at all a good idea because:

• If the data files & log files are growing, then the transactions are serialised that can result performance issues for the applications that use the database.
• Complete database & log files are fragmented due to the constant auto-growing.
• If the database or log file is growing continuously, then it can consume all available drive free space. This can creates space problems for all other applications and databases.

The best thing that a user can do is minimise the number of auto-growths. In addition to this, it also suggested using MB for the growth of data file rather than a percentage. Now, to prevent so many virtual log files that result in performance problem, size the transaction log files:

• Try to shrink the log files as much as possible
• Dump the log files after every 10-20 minutes.
• Monitor its growth every week to confirm the maximum size.

Method #2: Monitor SQL Server Disk Space

There are two parts to monitor SQL Server database size and are mentioned below:

• Existing Size
• Auto-Growth

In order to check the free space available on the disk in MB, it suggested to use xp_fixeddrives procedure. It is a good procedure if a user needs only that information but if a user wants to have the drive size idea, then it fails.

To have all the required information, for example, execute a query given below:

The result of this query is structured like this one:

One can easily sum up by drive to estimate the total size used-up by all of the database files. A user needs to execute the script written above in a predefined interval of time to make sure that database is not running out of the space.

Now, to check where the database files will auto-grow use sp_spaceused for this. However detecting auto-growth is a more complex task.

Detecting auto-growths

One can detect the auto-growth by using the Performance Monitor:

Performance Monitor

To monitor the log auto-growth, one can use performance monitor. This performance monitor helps me in monitoring log growths, percent log used and data file size. It also allows DBA to look for a specific database and if important, raise alerts to send net messages also.

The Bottom Line

We have discussed the important activity performed by the DBA, ie how to monitor SQL Server disk space. If the monitoring process fails, then it leads to SQL Server downtime and data loss. Thus, in this blog, we have discussed different manuals to monitor size of database files in SQL Server. One can easily refer them to prevent all issues related to SQL Server disk space.