Check Available and Free Disk Space in SQL Server


Without having the remote access to the server, it becomes a blind-spot to figure the available and free disk space in SQL Server. While I have been working on different areas of accessing the files, exchanging files, and setting appropriate files in respective disks, the hard drive location happens to be very vital. At times, I see that the logical files had to be put based on free space availability. Using the below scripts can help you figure out the drive space in no time. 
Further, there are two options to figure out the total and free space of a disk. Let’s look at the methods to check them.

1. Using the xp_fixeddrives Extended Stored Procedure

The below extended stored procedure gives us two columns, one with the drive name and the other with the free space in drive in MB. Just execute the SP in master as below.

EXEC master..xp_fixeddrives







2. Using sys.master_files + sys.dm_os_volume_stats

The query using the sys.master_files and sys.dm_os_volume_stats can give us a result set with four columns – LogicalName, Drive, TotalSpaceInMB and AvailableSpaceMB.

USE MASTER

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.total_bytes/1048576.0) AS TotalSpaceInMB,
CONVERT(INT,dovs.available_bytes/1048576.0) AS AvailableSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY AvailableSpaceInMB ASC
GO








Probably, you should go with option 2 to check the drive space as it can facilitate in comparing the drive total space available with the free space available. 

0 comments: