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 -