SQL-Quick tip #12 - Available disk space

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Available disk space

Having your database server running out of disk space is a very bad thing. Its the kind of showstopper that can really mess up your day, week or maybe the entire month.
So you should have some kind of monitoring in place to warn you if your disk is running low on space. You can buy some expensive enterprise all-inclusive software the monitors everything, or you start small by implementing scheduled task that runs a query to fetch information about the available disk space and then send you an email with the numbers.
The first script below will show how much disk space is left on the drive where your database is located and the second script will show how much disk space is currently allocated to the database and how much free space is available in the filegroup.

SELECT DISTINCT dovs.volume_mount_point AS Drive, CONVERT(INT,dovs.available_bytes/1048576.0) AS [Free Space In MB]
  FROM sys.master_files mf
 CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
 ORDER BY [Free Space In MB] ASC

SELECT DS.name [Filegroup name],
       DF.name [Filename],
       SUBSTRING(DF.physical_name, 1, 1) [Drive],
       DF.physical_name [Physical name],
       size/128 [Total size in MB],
       size/128.0 - CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS int)/128.0 [Available space in MB],
       CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS int)/128.0 [Actual space used in MB],
       (CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. [% Space used]
  FROM sys.database_files DF
  LEFT OUTER JOIN sys.data_spaces DS ON DF.data_space_id = DS.data_space_id
 ORDER BY DF.Name ASC;