Database File Usage & Growth

In this article, I would focus on finding empty claimable spaces within databases. Once found, the space can be claimed back using various methods which we would discuss in a later article.

The following script provides the details on a database, its data and log files, the respective filegroups which they logically belong to, the current fize size, the actual space used in the file and the growth factor for the file. I have used this code with the system stored procedure, sp_msforeachdb so as to gather this data for all databases on the server. It may be noted that the temporary table that is being used here, #temp_Data_Sizes is compressed. The next SELECT statement then displays those database files that have 50% or more free space in them. The TSQL code here is for Microsoft SQL Server 2008 and later.

if OBJECT_ID ('tempdb..#temp_Data_Sizes') is not null
drop table #temp_Data_Sizes
go

create table #temp_Data_Sizes
(DatabaseName varchar(255) not null
,Fully_Qualified_File_Name varchar(512) not null
,File_Type varchar(45) not null
,FileGroup varchar(25) not null
,Current_File_Size_GB varchar(25) not null
,Used_File_Space_GB varchar(25) not null
,Growth_Factor varchar(25) not null
,Constraint PK Primary Key clustered (DatabaseName, Fully_Qualified_File_Name) with (fillfactor = 50)
)
with (data_compression = Page)
go

set nocount on;
insert into #temp_Data_Sizes (DatabaseName, Fully_Qualified_File_Name, File_Type, FileGroup, Current_File_Size_GB, Used_File_Space_GB, Growth_Factor)
exec sp_msforeachdb ' use [?]
select DB_NAME(database_id) DatabaseName
,physical_name Fully_Qualified_File_Name
,case [type]
when 0 then ''Data''
when 1 then ''Log''
end +
case fileproperty (name, ''IsLogFile'')
when 0 then case fileproperty (name, ''IsPrimaryFile'')
when 0 then '' - Secondary Data File''
when 1 then '' - Primary Data File''
end
when 1 then ''''
end File_Type
,isnull(Filegroup_name (data_space_id), '''') FileGroup
,convert(decimal(28,2), (size * 8192.0) / (1024.0 * 1024.0 * 1024.0)) Current_File_Size_GB
,convert(decimal(28,2), (fileproperty (name, ''spaceused'') * 8192.0) / (1024.0 * 1024.0 * 1024.0)) Used_File_Space_GB
,case
when is_percent_growth = 0 then convert(varchar(20), convert(decimal(28, 4), (growth * 8192.0) / (1024.0 * 1024.0 * 1024.0))) + '' GB''
when is_percent_growth = 1 then CONVERT(varchar(3), growth) + ''%''
end Growth_Factor
from sys.master_files
where database_id = DB_ID ()'

select * from #temp_Data_Sizes
where 100.00 * ((CONVERT(Decimal(28, 4), Current_File_Size_GB) - CONVERT(Decimal(28, 4), Used_File_Space_GB)) / CONVERT(Decimal(28, 4), Current_File_Size_GB)) >= 50.00
and CONVERT(Decimal(28, 4), Current_File_Size_GB) > 0.0000
order by CONVERT(Decimal(28, 4), Current_File_Size_GB) desc

set nocount off;

if OBJECT_ID ('tempdb..#temp_Data_Sizes') is not null
drop table #temp_Data_Sizes
go