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.
USE MASTER
Go
SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(25), 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((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name 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],
ISNULL((SELECT TOP 1
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 = DB.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
Pingback: Query to get database information | Random Technical Thoughts
This is some great information. There were some errors in your script (you can’t just copy and paste it in SSMS to get it to run). I posted about your script on my blog (giving you credit and linking back your blog) and I corrected the script.
Here is a link to my posting:
http://wp.me/psiID-80
LikeLike
Thank you for correcting. Somehow the copy and paste did not work hence I used to attach the script as an attachment
LikeLike
No problem, they were little changes like sysdatabases should be sys.databases.
This is a great script thanks for coming up with it. I’m glad to put it in my toolbox.
LikeLike
This T-SQL need to be run under master database. The intention is to run the queries on all version of SQL Server from 2000 onward. I just updated the SQL to use Master database. You have a great collection of good articles, Keep it up. Thanks again.
LikeLike
Pingback: bhaveshkhanpara | Most useful Sql Queries
I think you can take a look at
http://www.dotnetspan.com/2014/06/get-database-size-in-sql-server.html
It’s very easy select statement
LikeLike
Very nice script, thanks !!
LikeLike
Hi Prahsanth, Do you have powershell script for this.?
LikeLike
Hi RK,
You can call the query in PowerShell using Invoke-SqlCmd.
–Prashanth
LikeLike