SQL Server: Server Level Database Space Utilization Report

Hi Friends,

After i posted “How to find the free space in Database files?“, I recieved multiple emails asking for a way to create a Database Space Utilization Report for all Databases on a server. Here i’ve created a script which will create a Server Level Report for all your Databases and list down the File, Free & Used Size for all Data Files.

--Step1 
Create database reports
go
select DB_Name = db_name(),
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
	 
into reports..Space_Report 
from
dbo.sysfiles a
go
truncate table reports..Space_Report
go
 
--Step2
sp_msforeachdb 'use ? insert into reports..Space_Report
select DB_Name = db_name(),
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a'
go
	 
	 
--Step3 Report Preview
select * from reports..Space_Report 
*/

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook