Get SQL Server Database Owner, Size, Last Backup Times and other useful info

I’ve been working on Databases Inventory and wrote this small query to get information for our SQL Server database repositary. This is a very basic query and I was hoping to get something from Google, but couldn’t find anything that includes all the essential information about databases such as Data and Log files sizes, last backup time and size, etc. So, I had to write it by myself and decided to share it with anybody who wants to save 5 minutes of their time by googling it. This T-SQL query will work on any version starting from SQL Server 2005.

SELECT AS 'DatabaseName'
       , SUSER_SNAME(sd.owner_sid)  AS 'DbOwner'
       , sd.compatibility_level AS 'Compatibility'
       , sd.recovery_model_desc AS 'Recovery'
       , sd.page_verify_option_desc AS 'ChecksumLevel'
       , sd.snapshot_isolation_state_desc AS 'Snapshot'
       , sd.state_desc AS 'Status'
       , sd.user_access_desc  AS 'UserAccess' 
       , CAST(DATABASEPROPERTYEX(, 'Updateability') AS varchar(256)) AS 'Updateability'
       , CAST(mfdata.DataSize*8/1024 AS int) AS 'DataSizeMB'
       , CAST(mflog.LogSize*8/1024 AS int) AS 'LogSizeMB'
       , CAST(b.backup_size/1024/1024 AS int) AS 'LastBackupSizeMB'
       , m.physical_device_name AS 'BackupDevice'
       , b.backup_start_date AS 'LastBackupStart'
       , b.backup_finish_date AS 'LastBackupFinish' 
       , sd.create_date AS 'DbCreateDate'
       , sd.collation_name AS 'Collation'
       , sd.is_auto_close_on AS 'IsAutoClose'
       , sd.is_auto_shrink_on AS 'IsAutoShrink' 
       , sd.is_auto_create_stats_on AS 'IsAutoCreateStatistics' 
       , sd.is_auto_update_stats_on AS 'IsAutoUpdateStatistics' 
       , sd.is_fulltext_enabled AS 'IsFulltextEnabled'
       , sd.is_parameterization_forced AS 'IsParameterizationForced'
FROM master.sys.databases AS sd
LEFT JOIN (SELECT database_id, SUM(size) DataSize FROM master.sys.master_files WHERE type = 0 GROUP BY database_id, type) mfdata 
ON mfdata.database_id = sd.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM master.sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog 
ON mflog.database_id = sd.database_id
LEFT OUTER JOIN	msdb.dbo.backupset b 
ON ( = b.database_name) AND b.backup_start_date = 
	         (SELECT MAX(backup_start_date) FROM msdb.dbo.backupset WHERE database_name = b.database_name AND type = 'D')
LEFT OUTER JOIN msdb.dbo.backupmediafamily m 
ON (b.media_set_id = m.media_set_id)
WHERE sd.database_id > 4 
--Last condition omits all system databases. It can be changed to include them as well

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>