Check Disk Space

How to check for available free disk space and report databases files which will be affected

This involved two steps.
Use the sys_master_files to find the location of files and the size of each files.This table is similar to the one names sysaltfiles which is present in master DB in SQL 2000,but in SQL 20005 sys_master_files can be referred from any database not necessarily master. Below mentioned is the query for fetching the same.And second step is to append the result with xp_fixeddrives procedure.


DECLARE @Free_Space TABLE
( DriveLetter CHAR,FreeSpace BIGINT )
INSERT INTO @Free_Space(DriveLetter,FreeSpace)
EXEC master..xp_fixeddrives
SELECT DB_NAME(database_id) As DBName
,CASE TYPE_DESC WHEN 'ROWS' THEN 'DATA'ELSE 'LOG' END As FileType
,SUBSTRING(physical_name,1,1) As DriveLetter
,CEILING(CONVERT(NUMERIC(10,0),SUM(SIZE*8))/1024) As [SpaceUsed(in MB)]
,FreeSpace As [FreeSpace(in MB)]
from sys.master_files, @Free_Space
WHERE SUBSTRING(physical_name,1,1) = DriveLetter
group by db_name(database_id),CASE TYPE_DESC WHEN 'ROWS' THEN 'DATA'
ELSE 'LOG' END,
SUBSTRING(physical_name,1,1),FreeSpace
ORDER BY 1,2