SQL Server Basic Healthcheck Queries
DBA Toolkit: SQL Server Health Check Queries This guide provides a suite of T‑SQL queries that give you a quick snapshot of your SQL Server’s health. The toolkit covers: Database Health Status Top 5 Resource‑Intensive Queries (by CPU time, in seconds) Top 5 Largest Objects (Tables) by Size Backup Status – including full, differential (incremental), and transaction log backups Database Size Summary Memory (RAM) Usage 1. Database Health Status This query returns basic state and configuration info for each database: SELECT name, state_desc, -- e.g. ONLINE, OFFLINE, RECOVERING, SUSPECT, etc. recovery_model_desc, -- SIMPLE, FULL, or BULK_LOGGED user_access_desc, -- MULTI_USER, SINGLE_USER, RESTRICTED_USER is_read_only, is_auto_close_on, is_auto_shrink_on FROM sys.databases; 2. Top 5 Resource‑Intensive Querie...