Checking for Available Free Space on SQL Server

There is a T-SQL extended stored procedure, xp_fixeddrives, that gives you visibility into the Windows drives. Calling xp_fixeddrives on its own will list each drive letter for the Windows host followed by its available disk space in MB.

Here is an example of how you can check for free space on any drive with a simple piece of SQL, compared to a threshold. Note the "where clause" on the select statement which returns just those drives containing less than 100MB of free space.

create table #appd4db_drive_check( drive varchar(100), mb_free int );
insert into #appd4db_drive_check EXEC master.dbo.xp_fixeddrives;
select * from #appd4db_drive_check where mb_free < 100;
drop table #appd4db_drive_check;