How to List Fixed Drives and Their Free Space from SQL Server

There is an undocumented extended stored procedure that returns amount of free space for all local hard drives in MB (it will not list mapped drives in the result):

EXEC master..xp_fixeddrives


You can insert the results of the xp_fixeddrives stored procedure into the table, and monitor change of the amount of free space over time.
First we will create a table where we will store the results of the stored procedure together with the date and time of the check:

CREATE TABLE DriveFreeSpace(
Drive char(1),
Free_Space_MB int,
Check_Date datetime DEFAULT (GETDATE()))

To insert results into the table run:

INSERT INTO DriveFreeSpace (Drive, Free_Space_MB) EXEC master..xp_fixeddrives

...or schedule a job that will run it and fill the table with the results.

The contents of the table will look similar to this:

SELECT * FROM DriveFreeSpace