Finding datafile sizes for all db on server

Question

Post reply

Finding datafile sizes for all db on server

n.heyen

SSCrazy

Points: 2849
More actions
November 3, 2011 at 2:37 pm

#251176

There is a command DBCC SQLPERF (logspace) that gives the size and % used for log files for all databases on an instance. Is there an equivalent that does that for the datafiles? Or at least the aggregate of total disk used by all the datafiles in a database?
I am using SQL Server 2008.
I've looked at sys.master_files but that doesn't show the % used or anything that I could see to calculate that.
Yes, I'm looking to see which databases are near hitting autogrow events and which ones are grossly oversized.
Any input is appreciated, and thank you for your time.
Norman

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

I use both of these:

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB

FROM sys.master_files

order by SizeMB desc

and

exec sp_MSforeachdb @command1='use [?] exec sp_spaceused'

go

_________________________________
seth delconte
http://sqlkeys.com

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

Actually this looks like what you need:

http://www.mssqltips.com/sqlservertip/1629/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases/[/url]

I just created the usp_Sizing sproc and it indeed returns a Free Space % column, along with lots of other statistics.

_________________________________
seth delconte
http://sqlkeys.com

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

seth delconte (11/3/2011)
I use both of these:
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
order by SizeMB desc
and
exec sp_MSforeachdb @command1='use [?] exec sp_spaceused'
go

You can add FileProperty(Name, 'spaceused') to the first script above to get the space used in each file. YOu still need the calculation because this function returns pages not KB.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

n.heyen SSCrazy Points: 2849 More actions · Answer 4

Thank you Jack and Seth, while not as pretty, I can make these work for what I need to do.

Microsoft: This isn't a unique thing to do, why don't you make it easy?

Thanks!

Norman