I found a cool series of scripts from Jeremiah Peschka on getting disk space from SQL Server. They’re useful queries, and I think for the most part I like getting the information from sys.sysaltfiles for information on my databases.

However, I thought the comment for this query was misnamed:

-- how full is each drive?
SELECT drive_letter ,
       SUM(size_in_mb) AS size_in_mb
FROM   #db_files AS df
GROUP BY drive_letter
ORDER BY drive_letter ;

Sys.sysaltfiles doesn’t have the drive size. In fact. it’s hard to get the drive size in T-SQL.

Finding the drive size is complex. I’ve seen lots of people using sp_OA procedures, or the CLR, or even xp_cmdshell. Powershell makes it easy, but not everyone has that installed (yet).

I’m amazed that xp_fixeddrives doesn’t give this. I know it’s undocumented, but why doesn’t it grab mount points and total size? Why isn’t it documented? That’s a topic for another day, but I’d have thought that at some point we could get more information about the OS from SQL Server easily.

For now, I’d use the xp_fixeddrives to get the local disk drive name and then the TotalSize property from the FileSystemObject using sp_OA procedures as outlined in the first link above.