SQLKnowledge.com

Day by day Experience of a Microsoft SQL Server production DBA & Consultant..

Script to find used and free space in Database files

Posted on | February 17, 2010 | No Comments

Just imagine, you have over 100+ databases available on the server and suddenly disk space alarm hit your inbox. What will you do, storage can not be added or expended on the fly. Here is he tested script to find used and free space from SQL Server database’s data files that you can shrink to deal the current situation.

Declare @DatabaseName varchar(500)
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, Name1 varchar(25),NameofFile Varchar(900) )
Declare curDB cursor for select name from master..sysdatabases where name not in(‘master’,'model’,'msdb’,'Northwind’,'pubs’)
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
if databasepropertyex(@DatabaseName,’Status’) = ‘ONLINE’
begin
insert into #DBSpace exec (‘USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS’)
end
fetch curDB into @DatabaseName
end
close curDB
deallocate curDB

– Get the results
select sd.name AS ‘DatabaseName’, TotalSpace/16 as ‘Total Database Size (MB)’, Used_Space/16 as ‘Total Used Space (MB)’,
(TotalSpace/16)-(Used_Space/16)as ‘FreeSpace’ from #DBSpace dbs
join master..sysdatabases sd on sd.filename=dbs.NameofFile order by ‘FreeSpace’ desc
– Drop the temp table after results
Drop table #DBSpace

Category: SQL BI/DW/SSRS, SQL DBA, SQL Dev, Scripts, Uncategorized
Tags:

Comments

Leave a Reply