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:
Post a Comment