Summary: Managing multiple SQL Server databases requires a quick way to audit health. This guide provides a comprehensive script to track database status, size, recovery models, compatibility levels, and the last known backup time.
SQL Server Database Properties and Status Audit
In SQL Server, every database within an instance is represented in the system catalog. Regularly monitoring the status, recovery models, and configuration of these databases is essential for ensuring high availability and data integrity.
Database Visibility & Permissions
Visibility of database rows depends on state and user permissions. If a database is not ONLINE, or if AUTO_CLOSE is enabled, certain properties may return as NULL. To view rows for OFFLINE databases, users require ALTER ANY DATABASE or CREATE DATABASE permissions in the master database.
Comprehensive Database Audit Script
Use the script below to retrieve a high-level overview of your instance health, including data/log file sizes and backup history.
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 100 THEN '100 (SQL Server 2008)'
WHEN 110 THEN '110 (SQL Server 2012)'
WHEN 120 THEN '120 (SQL Server 2014)'
WHEN 130 THEN '130 (SQL Server 2016)'
WHEN 140 THEN '140 (SQL Server 2017)'
WHEN 150 THEN '150 (SQL Server 2019)'
WHEN 160 THEN '160 (SQL Server 2022)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END + ' - ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER'))
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC), '-') AS [Last backup],
page_verify_option_desc AS [page verify option],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update stats' ELSE '' END AS [stats_update]
FROM sys.databases DB
ORDER BY DB.name;
Common Recovery Models
- FULL: Best for production; allows point-in-time recovery.
- SIMPLE: No log backups; risk of data loss since last full/diff backup.
- BULK_LOGGED: Optimized logging for large bulk operations.
Database State Reference
- ONLINE: Database is ready for queries.
- RESTORING: A restore operation is in progress.
- SUSPECT: Recovery failed; data may be corrupted.
- RECOVERING: Automatic startup recovery is running.
Additional Administration Resources
DBA Tip: Keep an eye on the Last Backup column. If it says 'NEVER' or shows a high number of days, your Disaster Recovery plan is at risk!
