Comprehensive SQL Server Database Audit: Logs, Recovery Models, and Settings

Summary: Use this advanced T-SQL query to audit all databases on your instance. Track Transaction Log usage, Recovery Models, Compatibility Levels, and TDE Encryption status in a single view.

Deep Dive: Auditing SQL Server Database Health and Configuration

Monitoring the health of your databases requires more than just checking if they are "online." A proactive DBA needs to understand the Transaction Log usage, Recovery Models, and critical configuration flags that impact performance and disaster recovery.

The following query provides a powerful, single-pane-of-glass view into every database on your instance, gathering crucial insights from across multiple system views and performance counters.

SQL Database Audit Script

-- Comprehensive Database Property and Log Usage Audit
SELECT 
    db.[name] AS [Database Name],
    SUSER_SNAME(db.owner_sid) AS [Database Owner],
    db.recovery_model_desc AS [Recovery Model],
    db.state_desc AS [State],
    db.log_reuse_wait_desc AS [Log Reuse Wait],
    CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)],
    CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)],
    CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %],
    db.[compatibility_level] AS [Compatibility],
    db.page_verify_option_desc AS [Page Verify],
    db.is_auto_create_stats_on AS [Auto-Create Stats],
    db.is_auto_update_stats_on AS [Auto-Update Stats],
    db.is_read_committed_snapshot_on AS [RCSI Enabled],
    db.is_auto_close_on AS [Auto-Close],
    db.is_auto_shrink_on AS [Auto-Shrink],
    db.is_query_store_on AS [Query Store],
    db.is_encrypted AS [Is Encrypted],
    de.encryption_state AS [TDE State],
    db.is_accelerated_database_recovery_on AS [ADR Enabled]
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) 
    ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
    ON db.name = ls.instance_name
LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK) 
    ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
    AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
    AND ls.cntr_value > 0
ORDER BY db.[name]
OPTION (RECOMPILE);
        

Why This Matters for Performance

• Log Reuse Wait: If your log is full, this column tells you why. Common reasons include LOG_BACKUP (needs a transaction log backup) or ACTIVE_TRANSACTION (an uncommitted transaction is pinning the log).
• Auto-Close/Auto-Shrink: Red Flag! In production, these should almost always be 0. Auto-close causes performance overhead on every connection, and Auto-shrink causes massive fragmentation.
• Compatibility Level: Ensure your databases are taking advantage of the latest Query Optimizer features by aligning this with your current SQL Server version.
• RCSI Enabled: Read Committed Snapshot Isolation can significantly reduce locking and blocking in highly concurrent environments.

---

This query leverages two key Dynamic Management Views (DMVs) for its data:

• sys.dm_os_performance_counters: Provides real-time log usage data.
• sys.dm_database_encryption_keys: Essential for tracking the progress of Transparent Data Encryption (TDE) operations.

Is your Transaction Log out of control? Identifying the Log Reuse Wait Description is the first step toward fixing log growth issues and avoiding "Disk Full" errors!