Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...