Space on Server Disk Drives

11 Dec 2010 One-minute read Al Eardley
On-PremData Platform

A common requirement when managing the resources of a database server is to be able to monitor the space available of the disk drives. Although there is no obvious way to do this through T-SQL there is an undocumented extended procedure called xp_fixeddrives.

As always with the undocumented procedures, it is not recommended to use this procedure in a production environment as Microsoft reserve the right to change or remove this procedure as pat of hot fixes, cumulative updates or service packs.

Warning out of the way, this is how I tend to use this procedure:

DECLARE @tblFixedDrives TABLE (
    DriveLetter    nchar(1)    NOT NULL
,    MBFree        decimal        NOT NULL

INSERT INTO @tblFixedDrives
    EXEC xp_fixeddrives

SELECT * FROM @tblFixedDrives

I use this procedure in many ad-hoc reporting procedures as it provides valuable information that in conjunction with the other data about the size of databases can be very useful.

Comment on this post: