THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
BEGIN

    SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10)
    SET @sql_string = @sql_string + 'SELECT
                                        DB_NAME()
                                        ,sysfilegroups.groupid
                                        ,sysfilegroups.groupname
                                        ,fileid
                                        ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
                                        ,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used
                                        ,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space
                                        ,sysfiles.name
                                        ,sysfiles.filename
                                    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
END

--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 07, 2009 11:34 AM by ejohnson2010
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

michel said:

thanks

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

by

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

thanks

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

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement