There are countless methods to obtain drive size information, each with its own set of benefits and limitations. I use a few different methods depending on if I have to type out the solution quickly or if I need a comprehensive view.
Method 1 – xp_fixeddrives
The most basic command to get drive sizes is easy to remember, so I use it when I need a fast result. This script leaves a lot to be desired though. It returns all drive letters on the queried SQL server, but only displays one column, MB free.
EXEC master.dbo.xp_fixeddrives
You could get extra information by inserting the results to a temp table and formatting it, but that defeats the purpose of this quick command. If I want more information, I move onto a more robust script entirely.
Method 2 – dm_os_volume_stats
In newer versions of SQL Server (2008R2+) you have the opportunity to use the dynamic management function dm_os_volume_stats. This function returns data on all drive letters with SQL files (Data, Log, Temp) and you can easily modify the select statement to tailor the results to your desires.
SELECT [Drive] = volume_mount_point ,[FreeSpaceMB] = available_bytes/1024/1024 --/1024 ,[SizeMB] = total_bytes/1024/1024 --/1024 ,[PercentFree] = CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id) --Optional where clause filters drives with more than 20% free space WHERE CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) < 20 GROUP BY volume_mount_point ,total_bytes/1024/1024 --/1024 ,available_bytes/1024/1024 --/1024 ,CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) ORDER BY [Drive]
I’ve returned the results in MB to match the result set in xp_FixedDrives, but feel free to divide by another 1024 bytes to convert to gigabytes. (That’s normally what I do). My query includes Free Space, Total Size, and Percent Free space. I also included an optional WHERE clause to filter out any drives that have over 20% Free space. Simply open up a multi-server query, copy and paste the script, and you have current data for drives with low space.
This is great for most situations…but this method fails to return all drives I’m concerned about. I do not get feedback on the C drive nor D Drive, which is where I have SQL and other applications installed. Not everyone will worry about system drives, but I want to know if someone is throwing data where it doesn’t belong. I bet you care about that too. In that case, I use another resource.
Method 3 – PowerShell (preferred)
When I need data on every drive, I turn to PowerShell. PowerShell allows me to perform a full drive query against servers with versions prior to 2008R2 as well. The script isn’t overly difficult, but you will need to supply a list of computers to run against. I suggest saving your SQL Server computer names into a notepad file and having PowerShell read from that.
If you want a quick and dirty way to get your computer names, open a multi-server query using your CMS and run:
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBios')
SELECT NodeName FROM sys.dm_os_cluster_nodes
That will return all computers running SQL and all cluster nodes – active or inactive. If you combine these as a distinct list, you should have all your computers. I didn’t say it was clean, I said it was quick.
The PowerShell script returns all drive letters, but you can filter a specific drive or name if you so desire. You can manipulate the WHERE clause by following the format of the one I provided.
For example, change $_.PercentFree to $_.VolumeName and -lt (which means less than) to -eq (equals). Replace 20 with the drive name in quotes, for example, 'Data'. If you need multiple clauses, the format is WHERE { $_.Value1 -eq '1' -AND $_.Value2 -eq '2' }
$Computers = ( Get-Content C:\Users\$Env:UserName\Desktop\Computers.txt )
Get-WmiObject -class Win32_LogicalDisk -ComputerName $Computers -Filter "DriveType=3" |
Select-Object SystemName,DeviceID,VolumeName,
@{n='FreeSpace(MB)';e={$_.FreeSpace / 1MB -as [int]}},
@{n='Size(MB)';e={$_.Size / 1MB -as [int]}},
@{n='PercentFree';e={$_.FreeSpace / $_.Size * 100 -as [int]}} |
Where { $_.PercentFree -lt 20 } |
Sort SystemName,DeviceId | FT -AutoSize
SQL Sanctum 