SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Drive Space

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.