Click here to monitor SSC
SQLServerCentral is supported by Redgate
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 – Query to find data,log,Total Size and other Useful information of all databases – SQL 2000/2005/2008/R2

I’ve encountered a situation where I need to find out the database file,log file, total size and other useful information about a database on SQL Server 2000/2005/2008/R2 versions. The requirement is to use same query and it has to be executed 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.


(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,’Updateability’)) Updateability,
convert(sysname,DatabasePropertyEx(name,’UserAccess’)) UserAccess ,
convert(sysname,DatabasePropertyEx(name,’Recovery’)) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,’Version’)) Version ,
CASE cmptlevel
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ’100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), crdate, 103) + ‘ ‘ + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date],
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘+ ‘seconds)’
FROM msdb.dbo.backupset BK WHERE BK.database_name = ORDER BY backup_set_id DESC),’-') AS [Last backup]
FROM sysdatabases DB
ORDER BY dbName, [Last backup] DESC, NAME

Download the code here DB Useful Information

Output :-



Leave a comment on the original post [, opens in a new window]

Loading comments...