A very common question I often receive is how to detect if the disk space is running low on SQL Server.
There are two different ways to do the same. I personally prefer method 2 as that is very easy to use and I can use it creatively along with database name.
Method 1:
EXEC MASTER..xp_fixeddrives GO
Above query will return us two columns, drive name and MB free. If we want to use this data in our query, we will have to create a temporary table and insert the data from this stored procedure into the temporary table and use it.
Method 2:
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
The above query will give us three columns: drive logical name, drive letter and free space in MB.
We can further modify above query to also include database name in the query as well.
SELECT DISTINCT DB_NAME(dovs.database_id) DBName, 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 will give us additional data about which database is placed on which drive.
If you see a database name, multiple times, it is because your database has multiple files and they are on different drives. You can modify above query one more time to even include the details of actual file location.
SELECT DISTINCT DB_NAME(dovs.database_id) DBName, mf.physical_name PhysicalFileLocation, 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
The above query will now additionally include the physical file location as well.
As I mentioned earlier, I prefer method 2 as I can creatively use it as per the business need. Let me know which method are you using in your production server.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
38 Comments. Leave new
Might we worth noting the data collector here Dave. Trend analysis is often better than point in time analysis.
Dave
Hi Pinal,
Could you please suggest some solutions for getting ‘TotalSpaceInMB’ column also with ‘FreeSpaceInMB’ column?
Thanks in advance. :)
Regards,
Pawan
here it is:
IF SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘productversion’)),1,2) < 10 –2000,2005
BEGIN
EXEC MASTER..xp_fixeddrives
END
ELSE –2008 and up.
BEGIN
SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0/1024.0) AS FreeSpaceInGB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0/1024.0) AS TotSpaceGB,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0) AS TotalSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC
END
GO
Only Method 1 works in SQL Server 2008 (10.0.5500).
The error you will get from all of the other methods described in this article is this one:
Invalid object name ‘sys.dm_os_volume_stats’.
Hi Pinal,
This script is monitoring the fixed drives only could you please share if there is wa y to track the disk space of the mount points also, as in our environment the DB files are spread across anchor points as well as mount points.
yes…only first method works in SQL Server 2008(10.0.5512). for second method its showing error.Invalid object name ‘sys.dm_os_volume_stats’.
Hi Pinal,
Is there a way where we can detect the memory, on web server we are allocating specific memory. So, is there a way where we can detect low memory on server.
Thanks,
Nilesh
This errors on the reference to sys.dm_os_volume_stats because that is only valid for SQL Server 2012
correction from 2008 R2 and up
Follow-up: I run a vb script that accounts for disks that are mount points (if you use mount points, EXEC MASTER..xp_fixeddrives will not provide you with all the storage information you need). Below is what I use.
strComputer = “.”
Set objArgs = WScript.Arguments
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\CIMV2”)
Set colItems = objWMIService.ExecQuery( “SELECT * FROM Win32_Volume WHERE Capacity>0”,,48)
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set OutputFile = fso.CreateTextFile(objArgs(0), True)
‘OutputFile.WriteLine “Caption,Capacity,FreeSpace,DriveLetter,MPDisk”
For Each objItem in colItems
OutputFile.WriteLine objItem.Caption & “,” & objItem.Capacity & “,” & objItem.FreeSpace & “,” &LEFT(objItem.Caption,1) & “,Y”
‘Wscript.Echo objItem.Caption & “,” & objItem.Capacity & “,” & LEFT(objItem.Caption,1) & “,Y”
Next
OutputFile.close
Note, I write the file out as a comma-delimited file and use BCP to load a table to report on it later. NOTE: This is only good for Windows 2003 and up.
Regards
IF SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘productversion’)),1,2) < 10 –2000,2005
BEGIN
EXEC MASTER..xp_fixeddrives
END
ELSE –2008 and up.
BEGIN
SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0/1024.0) AS FreeSpaceInGB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0/1024.0) AS TotSpaceGB,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB,
CONVERT(INT,dovs.total_bytes/1024.0/1024.0) AS TotalSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC
END
GO
Fantastic post Pinal, thanks for sharing.
To further clarify, the function “sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) ” is not valid for SQL Server 2008 base (I am running SP3 currently). I did some further in-depth research and I found I was not quite correct from my earlier statement. The function is only available for SQL Server 2008 R2 SP1 (NOT in SQL Server 2008 R2 RTM) NOR SQL Server 2008 (non-R2). Yes, there are a lot of us still running SQL 2008 non-R2.
SO, the code resubmitted by Suran will not work for the SQL Server 2008 or SQL Server 2008 R2 (RTM) so you are stuck and still cannot look a the mount points.
Sorry!… it is available for SQL Server 2008 R2 SP1 and Higher… Cheers!
If you want to show the percentage of free space, just add this
Convert(INT,((dovs.available_bytes/1048576.0/1024.0)/(dovs.total_bytes/1024.0/1024.0/1024.0)) * 100) AS PercentageFree
thanks for sharing
wiwats – thanks for liking it.
good scripts unfortunately method 2 does not show space on non database drive
from method 1
drive MB free
C 41260
D 37853
E 175595
H 409910
I 446827
K 4983
L 7003
M 189319
P 21046
S 5547
T 5543
X 375060
method 2
LogicalName Drive FreeSpaceInMB
TempDB T:\ 5543
SQL LOGS L:\ 7003
SQL GET DATA H:\ 409910
SQL EDW DATA I:\ 446827
Love you posts, is there a way to add a volume to the script that doesnt contain an entry in sys.master_files? Specifically a backup drive?
Script was useful but when i run on (Microsoft SQL Server 2005 – 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ) SQL Server 2005 i was getting below error — Invalid object name ‘sys.dm_os_volume_stats’.
Appreciate if you can help on this
Hi Sir,
how to check recently added space history of data/log files
Thank you sir
satya
Hi,
I used your script on an SQL 2008 R2 SP1 and get the error:
Invalid Syntax near ‘.’.
It is on the line:
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
Do you know whats the issue?
Thanks
Interesting script. Wondering how you guys are using this and for what purpose. I mean, if I need to know the disk space, there is no need to run a SQL script to figure out what it is. I could just open “Computer” to see all drives and amount of space.
Ideally the script should run daily, and store the value for each of the drives so it can look at it again when it runs the following day. Then when it runs the following day, it should determine the difference between free space between the two days. If you make the assumption that the amount of disk space could grow again the following day by the same amount, then based on that assumption, you could set the script to email you an alert X days before you run out of disk space and be pro-active in dealing with lack of disk space instead of reactive.