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
SELECT DB_NAME(dbid) AS DatabaseName,
CAST((SUM(CAST(size AS BIGINT)) * 8) / (1024.0 * 1024.0) AS DECIMAL(10, 2)) AS DbSizeGb
FROM sys.sysaltfiles
GROUP BY DB_NAME(dbid);
Thanks Pinal!!
Is there a way to see if SQL Server is running a sql script out of disk space? because I’m having a trouble with low disk space, and then SQL Server does not respond, then when I restart the instance, the Database becomes in Recovery model.
is it possible to find the same type of storage space on both primary and secondary replica of Alwayson
@Arunkumar Babu – Can you please elaborate the question.
how to change SSMS query results cache to another drive.?
You need to modify TEMP environment variable to point to different drive.
Could you please share a query to find local disk space (drive capacity and free space) along with ip address or machine name or database name in SQL Server 2005/2008 r2.
Check this query I modified it to take only drives where SQL Data and log files reside.
Works on SQL 2005/08 as well
/*******************************************************/
/* Enabling Ole Automation Procedures */
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
/*******************************************************/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace float(10),
TotalSize float(10) ) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
‘Scripting.FileSystemObject’,@fso OUT IF @hr 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
IF @hr 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,’TotalSize’, @TotalSize OUT IF @hr 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr 0 EXEC sp_OAGetErrorInfo @fso
SELECT
distinct substring(smf.physical_name, 1,1) as Drive, TotalSize as ‘Total(MB)’, FreeSpace as ‘Free(MB)’,
ROUND(FreeSpace/TotalSize*100,2) as “% Free Space” , HOST_NAME() as HostName
FROM sys.master_files smf
cross apply #drives
where substring(smf.physical_name, 1,1) = drive
order by drive
DROP TABLE #drives
GO
/*******************************************************/
/* Disabling Ole Automation Procedures */
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 0;
GO
RECONFIGURE;
/*******************************************************/
GO
Hi Dave,
Is there a way to find out Percentage of free space on Drive that contains no DBs,
like a backup Drive or OS Drive?
Thank you.
SELECT distinct dovs.logical_volume_name, dovs.volume_mount_point, PercentFree = CAST(dovs.available_bytes as float)/dovs.total_bytes
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
Pinal, your articles are always valuable thank you. In this case I believe MS has a bug in sys.dm_os_volume_stats. It does not report mount points that only contain restoring database files. This creates significant reporting gaps on log shipped systems. Tested this in 2008 R2 SP3 as well as 2014 SP2. Any ideas on an alternate mount point reporting option that doesn’t include xp_cmdshell?
Method #2 is really helpful, thank you, Pinal. I’ve never seen this query before, and I’ve been around a bit :)
Thank you very much, very help full.