SQL – Find Database File Usage Details – 2000/05/08/R2

This post is to find the database file usage details of all databases of an instance. The query runs on all versions of sql 2000/2005/2008/R2 . The requirement is to use same query to be executed it across all version of SQL SERVER.  We can  also do this using dynamic SQL’s.

Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.

Download here –Database_File_Usage_Details

***************************

USE MASTER

Go

CREATE table ##temp
(
Name varchar(100),
groupid int,
SizeMB decimal(10,2),
SpaceUsed decimal(10,2),
FreeSpace decimal(10,2)
)

EXEC master..sp_MSForeachdb ‘
USE ?
BEGIN
insert into ##temp(Name,GroupId,SizeMB,SpaceUsed, FreeSpace)
SELECT ”[”+”?”+”]” as databasename,groupid
, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB
, CAST(FILEPROPERTY(name, ”SpaceUsed”)/128.0 as DECIMAL(10,2)) as Space_Used
, CAST(size/128.0-(FILEPROPERTY(name, ”SpaceUsed”)/128.0) AS DECIMAL(10,2)) AS Available_Space
FROM sysfiles
END

–select * from ##temp

SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(50), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
–(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
–(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid!=0) AS [DataMB],
(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid=0) AS [LogMB],
(SELECT SUM(SpaceUsed) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid!=0) AS [DataSpaceUsedMB],
(SELECT SUM(SpaceUsed) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid=0) AS [LogSpaceUsedMB],
(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid!=0) AS [FreeDataSpaceMB],
(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid=0) AS [FreeLogSpaceMB],
(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid!=0) +(SELECT SUM(FreeSpace) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid=0)
AS [TotalFreeSpaceMB],
(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid!=0)+(SELECT SUM(SizeMB) FROM ##temp WHERE Name = ‘[‘+DB.name+’]’ AND groupid=0) TotalSizeMB
FROM master.dbo.sysdatabases DB where DATABASEPROPERTYEX(name, ‘Status’) =’Online’
ORDER BY dbName

drop table ##temp

*******************************************************************************

Output –

Image

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s