SQL Server - List available fixed Hard Drive with Free space details from SSMS

Monitoring free disk space on SQL Server is very important if database growth is relatively fast. As DBA one of your responsibilities is to monitor the disk space and always make sure you have enough space. There is number of different methods you can use , one of them is undocumented SQL server extended stored procedure  ‘xp_fixeddrives’.

Xp_FixedDrives is very simple and you can use it from SSMS / Query Analyzer.

EXEC master..xp_fixeddrives

This results in record set that contains the number MBs of free space for each physical drive associated with SQL server machine.

Below query gives the similar results

SELECT 

      *

FROM   

      OPENQUERY(SQLServerName,'set fmtonly off; exec master..xp_fixeddrives')

You can also store xp_fixeddrives results in table and thru SQL server agent better manage drive space with automated job.

CREATE TABLE #DriveFS

    (

      Drive CHAR(1) ,

      FreeSpace INT

    )

INSERT  INTO #FreeSpace

        EXEC xp_fixeddrives

Note : Xp_FixedDrives does not show any information for Mounted Volumes.