Databases seldom fail out of the blue. Before the outage there is usually a disk filling up slowly, a backup job that has been failing for weeks, or a query that became ten times slower after a large data load. The catch is that nobody is watching. This checklist covers what is worth reviewing regularly on a SQL Server instance, using read-only queries that change nothing on the server.
The queries rely on system views (DMVs, catalog views and msdb tables). Try them on a test environment first if you have one. As for permissions, VIEW SERVER STATE alone is not enough:
VIEW SERVER STATEgrants access to server-level DMVs such assys.dm_os_wait_statsandsys.dm_os_volume_stats;VIEW ANY DEFINITIONexposes metadata that otherwise comes back only partially:sys.master_filesand the full list of logins insys.server_principalsandsys.sql_logins;- the backup and job queries read tables in
msdb, which requires read access there (for example, thedb_datareaderrole inmsdb); - the statistics query uses
sys.dm_db_stats_properties, which requires read access to the tables involved (for example,db_datareaderin the database being checked) or thedb_ownerordb_ddladminroles.
A sysadmin login sees all of this at once, but for a recurring check a dedicated login with only the permissions above is the safer choice. In SQL Server 2022, Microsoft split VIEW SERVER STATE into more granular permissions such as VIEW SERVER PERFORMANCE STATE and VIEW SERVER SECURITY STATE; a login that already has VIEW SERVER STATE remains covered.
1. Backups: do they exist, are they recent, do they match the recovery model?
Start with the obvious question: when was each database last backed up? Then the less obvious one: does the backup type match the recovery model? A database in FULL recovery without log backups has a transaction log that keeps growing, and it cannot be restored to a point in time.
SELECT d.name,
d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_differential,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;
Look for:
- databases with no full backup at all,
masterandmsdbincluded; FULLrecovery databases whose last log backup is empty or old;- backups written to the same disk or server as the database, with no copy elsewhere.
Having a backup file does not mean it will restore. That test deserves its own routine, covered in A backup you have never restored is not a backup.
2. Page integrity
Data corruption usually comes from the storage layer and goes unnoticed until someone reads the damaged page. Two simple safeguards:
- each database’s
PAGE_VERIFYoption should beCHECKSUM(seepage_verify_option_descinsys.databases); DBCC CHECKDBmust run regularly, on production or on a restored copy, and someone must read the output.
It also pays to create SQL Server Agent alerts for errors 823, 824 and 825 and for severities 19 through 25. They point to read failures or serious faults, and without an alert they only land in the error log.
3. File growth and disk space
A file set to grow by a percentage grows by a larger amount every time and, on a nearly full disk, can fail all at once. Growth increments that are too small cause the opposite problem: hundreds of expansion events, each one pausing writes.
SELECT DB_NAME(mf.database_id) AS database_name,
mf.name AS file_name,
mf.type_desc,
CAST(mf.size AS bigint) * 8 / 1024 AS size_mb,
CASE WHEN mf.is_percent_growth = 1
THEN CONCAT(mf.growth, ' %')
ELSE CONCAT(CAST(mf.growth AS bigint) * 8 / 1024, ' MB') END AS growth,
vs.volume_mount_point,
vs.available_bytes / 1048576 AS free_on_volume_mb
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY free_on_volume_mb;
If a transaction log is large, check log_reuse_wait_desc in sys.databases. It tells you what is preventing log reuse: a missing log backup, an open transaction, stalled replication and so on. Shrinking the file without fixing the cause only postpones the problem.
4. Statistics and indexes
The query optimizer picks execution plans based on statistics. When they drift too far from the actual data, it may choose a poor plan, and a query that used to take seconds starts taking minutes.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
s.name AS stats_name,
sp.last_updated,
sp.rows,
sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY sp.modification_counter DESC;
This query only covers the database it runs in. To cover the whole instance, run it in each user database (with USE database_name first) or in a loop over sys.databases.
On indexes, three practical notes:
- Fragmentation matters less on SSD storage than it did on spinning disks. Rebuilding everything every night burns maintenance window and log space for little gain.
- Missing index suggestions from the DMVs are hints, not instructions. Creating all of them leads to duplicates and slower writes.
- Unused indexes (see
sys.dm_db_index_usage_stats) cost writes and space. Before dropping one, make sure the instance was not restarted recently, since these counters reset on restart.
5. Wait statistics
Whenever a task has to wait for something (disk, CPU, a lock, the network), SQL Server records it. Added up, these waits show where the server is losing time.
SELECT TOP (15)
wait_type,
waiting_tasks_count,
wait_time_ms / 1000.0 AS wait_s,
signal_wait_time_ms / 1000.0 AS signal_wait_s
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
AND wait_type NOT LIKE 'BROKER%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN ('LAZYWRITER_SLEEP', 'CHECKPOINT_QUEUE', 'LOGMGR_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'WAITFOR', 'DIRTY_PAGE_POLL',
'SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;
This filter removes only some of the harmless waits; more complete lists are shared in the community. Common wait types and what they often suggest:
PAGEIOLATCH_*: reading pages from disk, often due to a missing index or not enough memory;WRITELOG: latency writing to the transaction log;LCK_M_*: sessions blocked by other sessions;CXPACKETandCXCONSUMER: parallelism, which is not always a problem;SOS_SCHEDULER_YIELD: CPU pressure.
These figures accumulate from the last restart. For troubleshooting, compare two snapshots taken a known interval apart, ideally during peak hours.
6. SQL Server Agent jobs
A job that fails without notifying anyone is one of the most common reasons backups go missing.
SELECT j.name AS job_name,
h.run_date,
h.run_time,
h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.step_id = 0
AND h.run_status = 0
ORDER BY h.run_date DESC, h.run_time DESC;
Also confirm that the Agent service starts automatically, that an operator and Database Mail are set up to report failures, and that no important job has been disabled.
7. Security and logins
Excessive permissions will not bring the database down, but they turn a mistake or a leaked password into a serious incident. Start with who has full control:
SELECT p.name, p.type_desc, p.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS p ON p.principal_id = rm.member_principal_id
WHERE r.name = 'sysadmin';
Run this query with a login that has VIEW ANY DEFINITION (or is a member of sysadmin or securityadmin). Without it, SQL Server only returns the logins the current user is allowed to see, and the list comes back incomplete with no warning.
Watch out for:
- applications connecting as
sysadminordb_ownerwithout a real need; - the
salogin enabled and in use; - SQL logins with
is_policy_checked = 0insys.sql_logins, meaning no password policy; - generic accounts shared by several people;
- orphaned users that exist in a database but have no matching login.
8. Version, updates and lifecycle
Know exactly what is installed:
SELECT SERVERPROPERTY('ProductVersion') AS product_version,
SERVERPROPERTY('ProductLevel') AS product_level,
SERVERPROPERTY('ProductUpdateLevel') AS update_level,
SERVERPROPERTY('Edition') AS edition;
Every SQL Server version has a lifecycle, with an end of mainstream support followed by an end of extended support. Once out of support, a version no longer receives security fixes. Dates differ by version, so check the lifecycle page on Microsoft’s website against the output above. Also check whether the instance is on the latest cumulative update for its version, after testing it in a non-production environment.
How often to check
A split that works for many environments:
- daily: last night’s backups, failed jobs, disk space;
- weekly:
DBCC CHECKDBresults, file growth, waits compared with the previous week; - monthly: statistics and indexes, members of administrative roles, version and pending updates, one restore test.
Ideally the daily checks become automatic alerts, so that people spend their review time on what actually needs judgment.
Want a second opinion?
If you answered “I don’t know” to several items on this list, an assessment of your environment is a good place to start: Trustwise reviews these points on your instance and tells you what to address first. See how SQL Server database management works or request an assessment.