How to Monitor Low Disk Space in SQL Server

As a SQL server user, you should be aware of disk space availability in SQL server because once SQL Server runs out of disk on the primary host, then everything comes to a crashing halt. In this case, the SQL server engine expands the size of database file. Every SQL database has the growth option setting and all the database files depend on it. This problem cannot create any trouble if you identify on time and solve it quickly. Every database has a default size and you can also increase the size of it. To increase the database size then, use this command:

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
   (NAME = databasename,
    SIZE = 20MB);
GO

Many users set the database on auto-growth option. When the auto-growth event occurs then SQL server find the more space on the disk. This space can be anywhere on the disk rather than right next to the existing database space and become the reason of database fragmentation. We have an xp_fixeddrives extended procedure to check the available disk space. You can use this procedure like this:

EXEC MASTER..xp_fixeddrives

GO

Above query will return two columns. First is drive name and second is MB free. You have another option to know about the free disk space. Use this query:

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,

dovs.volume_mount_point AS Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY FreeSpaceInMB ASC

GO

This query will return three columns; Logical name, drive name and free space in MB.

This is the way by which you can check the available disk space in SQL Server. If you are interested to know about the used space by indexes then you can use usp_SpaceUsedAnalyzer stored procedure. You can download usp_SpaceUsedAnalyzer as 100213.zip by here.

Note: This procedure won’t work with SQL Server 2000 and earlier versions because the SP uses system database table which is available in SQL Server 2005 and later versions. 

The above stored procedure is inspired by sp_spaceused stored procedure. Now we will see the working of sp_spaceused.

I have discussed with some DBAs about this SP and few of them are not aware about the terms “unallocated space” and “unused space”. 

USE [AdventureWorks2012]

GO

EXEC sp_spaceused

GO

This query will give you the following result:

This query will give you the following result:

Let’s understand the column names.

1. database_size: It is sum of data files and log files. In this database it is 205.75 MB.
2. unallocated space: A space which is not reserved for data files or log files. It is 14.95 MB.
3. reserved: A space which is reserved for use by either data files or log files. It is 194608 KB and I will convert from KB to MB by this formula: 194608 KB/1024=190.05 MB.
4. data: Space used by data = 97016 KB/1024= 94.74 MB.
5. index_size: Space used by indexes = 88048 KB/1024 = 85.99 MB.
6. unused: It is a part of reserved space which is not used by any data files or log files. 9544 KB/1024 = 9.32 MB.
7. used: data+index_size = 94.74+85.99=180.73 MB.

You can remember the simple formula:

Final words:  So these are few simple methods by which we can check the disk space allocated by SQL Server database. If you are running the database without monitoring the disk space availability of your SQL Server database then you are running it on high risk. So check the disk space of your database to prevent your data from corruption.