T-SQL Script to find out the database file size, space used and available free space


While troubleshooting disk space issue, it is essential to know about the database file size statistics. You can execute below script to get database file size information.

set nocount on

create table #dbfileInfo(
name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))

declare @mySQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR

SET @cur_DBName = CURSOR FOR
select name from sys.databases

OPEN @cur_DBName
FETCH NEXT
FROM @cur_DBName INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName
if DATABASEPROPERTYEX(@dbName, 'status') = 'ONLINE'
begin
select @mySQL = 
    '
        use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a
    '
    exec sp_executesql @mySQL
end
FETCH NEXT
FROM @cur_DBName INTO @dbName

END
CLOSE @cur_DBName
DEALLOCATE @cur_DBName
GO

select * from #dbfileInfo
drop table #dbfileInfo

FileOutput

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s