THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

File and Filegroup Space Details

Here is a little SQL Script I wrote that gives you file and filegroup size details for all databases on SQL Server 2005 or 2008. This returns all the vital information I am looking for when I look at database sizes and growth such as file size, space used, free space, and the physical filename. There is also a second result set with details rolled up to the filegroup. If you were so inclined, you could capture this information on a regular basis and do some trending. Script attached.

DECLARE @database_id int
DECLARE @database_name sysname
DECLARE @sql_string nvarchar(2000)
DECLARE @file_size TABLE
    [database_name] [sysname] NULL,
    [groupid] [smallint] NULL,
    [groupname] sysname NULL,
    [fileid] [smallint] NULL,
    [file_size] [decimal](12, 2) NULL,
    [space_used] [decimal](12, 2) NULL,
    [free_space] [decimal](12, 2) NULL,
    [name] [sysname] NOT NULL,
    [filename] [nvarchar](260) NOT NULL

SELECT TOP 1 @database_id = database_id
    ,@database_name = name
FROM sys.databases
WHERE database_id > 0
ORDER BY database_id

WHILE @database_name IS NOT NULL

    SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10)
    SET @sql_string = @sql_string + 'SELECT
                                        ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
                                        ,convert(decimal(12,2),round(fileproperty(,''SpaceUsed'')/128.000,2)) as space_used
                                        ,convert(decimal(12,2),round((sysfiles.size-fileproperty(,''SpaceUsed''))/128.000,2)) as free_space
                                    FROM sys.sysfiles
                                    LEFT OUTER JOIN sys.sysfilegroups
                                        ON sysfiles.groupid = sysfilegroups.groupid'

    INSERT INTO @file_size
        EXEC sp_executesql @sql_string   

    --Grab next database
    SET @database_name = NULL
    SELECT TOP 1 @database_id = database_id
        ,@database_name = name
    FROM sys.databases
    WHERE database_id > @database_id
    ORDER BY database_id

--File Sizes
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename
FROM @file_size

--File Group Sizes
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, SUM(file_size) as file_size, SUM(space_used) as space_used, SUM(free_space) as free_space
FROM @file_size
GROUP BY database_name, groupid, groupname

Eric Johnson
SQL Server MVP
Co-Host CS Techcast
Published Friday, August 7, 2009 11:34 AM by ejohnson2010
Filed under: , ,



michel said:


i have solved my problem

August 17, 2010 8:43 AM

mark said:

Nice script many thanks

December 29, 2011 5:20 AM

EricAzur said:

Thanks it's simple and effective :)

but replace

WHERE database_id > @database_id


WHERE database_id > @database_id AND state = 0

for not check offline datbases or you get an error.

February 21, 2013 10:33 AM

suubu said:

kindly add file group maxsize column also to this script.reply plz urgent

April 25, 2014 12:20 PM

karl oliver said:

very useful script


July 8, 2014 10:35 PM

Phillip said:

Thanks for the script! It's saved me valuable time!

July 30, 2014 8:11 PM

dan said:

can you plz kindly explain how should I run this script?

September 5, 2014 12:39 PM

David said:

This is awesome!!!!! I have hundreds of files and file groups that are all way too over sized and causing disk errors, I believe, so now I can shrink them accordingly, Thank you so much.  Sorry for any spelling errors :)

January 26, 2015 7:17 PM

Greg said:

I'm so hoping you're the same Eric Johnson that did the guitar work on Cliffs of Dover...  Please please tell me you're the same guy and you're just digging SQL Server now.

August 9, 2016 2:12 PM
New Comments to this post are disabled

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Privacy Statement