Geeks With Blogs
SharePoint & SQL Thoughts
SQL Tidbits
 
Have you ever had a need to see storage information for all your databases in a SQL Server? As they say there are many ways to skin a cat all based on your need.  Back in the day I was taught to use SYS.SYSFILES, its handy and it does give you what you need but suppose you wanted a picture of your entire SQL  Server? Then this solution is not viable that’s when we divert to SYS.MASTER_FILES according to MSDN http://msdn.microsoft.com/en-us/library/ms186782.aspx  this system view contain a row per file of a database as stored in the Master database simply put as contains all the information you need to know regarding your database(s) storage questions.
 
Here is a query that sums it all up excluding the system databases.
 
SELECT DB_NAME([database_id]) AS N'database_name'
, [name] AS N'logical_name'
, [Physical_Name]
,(size*8)/1024  as 'Physical_File_Size (MB)'
, SUM((size*8)/1024) Over(PARTITION by [database_id]) as 'DatabaseSize (MB)'
, [growth]
, [is_percent_growth]
FROM SYS.MASTER_FILES WHERE [database_id] > 4;
 
I hope this saves someone a headache.
Posted on Wednesday, June 8, 2011 11:02 PM | Back to top


Comments on this post: All databases storage information

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net