365DBA Blog

Database Stuff

Database Free Space

Here is a very simple SQL Server script that has come in handy throughout the years; it displays the total space, used space, and free space (bloat) for each data file within every database on the instance. Remove the commented line to limit the results to a specific drive (in case you are running out of room on a specific drive and are only interested in files which reside on that drive). 

*Disclaimer: Keep in mind however, that shrinking files is against best practices and causes everything to get fragmented, etc., but in real life sometimes you need to do it regardless of how ugly it is.

If Object_ID('tempdb.dbo.#UsedSpace') > 0
    Drop Table #UsedSpace
Create Table #UsedSpace (DBName VarChar(150), FileID SmallInt, FileGroup SmallInt, TotalExtents Int, UsedExtents Int, 
Name NVarChar(256), FileName NVarChar(512))
exec sp_msforeachdb 'use [?]
Declare @SQL VarChar(8000)
Set @SQL = ''DBCC SHOWFILESTATS WITH TABLERESULTS''
Insert Into #UsedSpace (FileID, FileGroup, TotalExtents, UsedExtents, Name, FileName)
Exec(@SQL);
Update #UsedSpace Set DBName = ''?'' Where DBName Is Null;
'
Select DBName, Name, (TotalExtents * 64) / 1024 As TotalSpace,
(UsedExtents * 64) / 1024 As UsedSpace, 
((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) As FreeSpace
From #UsedSpace
--Where Left(FileName, 1) = 'F'
Order By ((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) Desc
Drop Table #UsedSpace
Loading