Show free space for all database files

Free space for all database files

By James Stuckert↗, 2013/07/02

This script is great for determining which files are taking up the most space on a SQL Server. It collects the free space on the drive, space used, the data/log file size, capped size of the file, filegroup, and file name.

If there is a specific drive that is having space issues, you can set that variable to pull data for only those data/log files. You also have the option to report on specific databases and even file IDs.

SET NOCOUNT ON  
DECLARE @drive CHAR(2)  
   ,@database VARCHAR(75)  
   ,@file_id SMALLINT  
DECLARE @xpfd TABLE  
    (  
     drive CHAR(1)  
    ,[FREE_SPACE_DRIVE_MB] INT  
    )  
  
SET @drive    = NULL    -- Leave NULL for all database files  
SET @database = NULL    -- Leave NULL for all databases  
SET @file_id  = NULL    -- Leave NULL for all files, 1 for MDFs, 2 for LDFs  
  
INSERT  INTO @xpfd  
        EXEC master..xp_fixeddrives  
  
IF OBJECT_ID('tempdb..#space_available ') IS NOT NULL   
    DROP TABLE #space_available   
CREATE TABLE #space_available  
    ([FILE_ID] SMALLINT  
    ,MAX_SIZE_MB VARCHAR(25)  
    ,FILE_SIZE_MB DECIMAL(12, 2)  
    ,SPACE_USED_MB DECIMAL(12, 2)  
    ,FREE_SPACE_MB DECIMAL(12, 2)  
    ,[DATABASE] VARCHAR(260)  
    ,[FILEGROUP] VARCHAR(75)  
    ,[LOGICAL_NAME] VARCHAR(260)  
    ,FILENAME VARCHAR(260)  
    )  
INSERT  INTO #space_available  
        EXEC sys.sp_MSforeachdb @command1 = 'USE [?] SELECT a.FILEID  
,       [MAX_SIZE_MB] = ISNULL(cast(NULLIF(CONVERT(DECIMAL(12, 2), ROUND(a.maxsize / 128.000, 2)),-0.01) as VARCHAR),''Unlimited'')  
,       [FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))  
,       [SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name, ''SpaceUsed'') / 128.000, 2))  
,       [FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name, ''SpaceUsed'') ) / 128.000, 2))  
,           DB_NAME() as [Database]  
,       isnull(b.groupname,'''') as [FILEGROUP]  
,       NAME = LEFT(a.NAME, 260)  
,       FILENAME = LEFT(a.FILENAME, 260)  
FROM    sys.sysfiles a  
FULL JOIN sys.sysfilegroups b on b.groupid = a.groupid  
where a.FILEID IS NOT NULL'  
SELECT  [DATABASE]  
       ,CASE WHEN [FILE_ID] = 2 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE  
       ,[FILE_ID]  
       ,CAST(xpfd.[FREE_SPACE_DRIVE_MB] AS DECIMAL(12, 2)) [FREE_SPACE_DRIVE_MB]  
       ,[FREE_SPACE_MB] [FREE_SPACE_FILE_MB]  
       ,[SPACE_USED_MB] [SPACE_USED_FILE_MB]  
       ,[FILE_SIZE_MB]  
       ,[MAX_SIZE_MB] [MAX_SIZE_FILE_MB]  
       ,[FILEGROUP]  
       ,[LOGICAL_NAME]  
       ,[FILENAME]  
FROM    #space_available  
        INNER JOIN @xpfd AS xpfd ON xpfd.drive = LEFT(FILENAME, 1)  
WHERE   (@drive IS NULL  
         OR LEFT([FILENAME], 1) = @drive)  
        AND ([DATABASE] LIKE @database + '%'  
             OR @database IS NULL)  
        AND ([FILE_ID] = @file_id  
             OR @file_id IS NULL)  
ORDER BY FREE_SPACE_MB DESC  
  
IF OBJECT_ID('tempdb..#space_available ') IS NOT NULL   
    DROP TABLE #space_available