A Monthly Health Check You Can Run in Ten Minutes

A SQL Server health check reviews recent evidence quickly, but cannot establish complete database health in ten minutes. Use this read-only review to find missing checks and follow-up work, not to replace backups or integrity testing.

Make the Review Repeatable

Keep the following blocks together as one review script. Start in the database whose users you want to inspect. Several queries review the whole instance, while the user check is database-specific. Repeat that check in each application database rather than treating one result as server-wide coverage.

Run with the documented monitoring permissions and record the capture time. Missing permissions can hide evidence or return an error. Keep errors in the review record. Don’t translate a failed collection into a green status merely because the script continued.

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    @@SERVERNAME AS server_name,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS update_level;

Check Backup Coverage Against Requirements

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.database_id <> 2
GROUP BY d.name, d.recovery_model_desc;

Compare those dates with the recovery requirements for each database. A recent full backup doesn’t establish acceptable point-in-time recovery for a full-recovery database. Check the log chain and its retention. Also verify that the required backup files still exist.

History can be purged or stored on another replica, and database names can be reused. Investigate gaps rather than immediately declaring failure. Keep successful restore-test evidence separately. A backup history row proves that an operation was recorded, not that tomorrow’s recovery has been rehearsed.

Review Integrity Evidence Without Rerunning Everything

SELECT
    name,
    DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS last_good_checkdb
FROM sys.databases
WHERE database_id <> 2;

This property provides a useful pointer to successful integrity-check evidence on supported releases. Review the maintenance output as well, including the command and options used. A missing or old value needs investigation. It doesn’t justify calling the database clean.

Don’t start a full DBCC CHECKDB across every large database merely to finish this short review. Integrity checking needs its own planned resources and schedule. The ten-minute idea refers to reviewing evidence, not a promised runtime for the underlying maintenance.

Look for Repeated Growth and Low Space

SELECT
    DB_NAME(f.database_id) AS database_name,
    f.name, f.type_desc,
    f.size * 8.0 / 1024 AS file_mb,
    f.growth, f.is_percent_growth,
    v.volume_mount_point,
    v.available_bytes / 1048576.0 AS volume_free_mb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS v;

Compare free space with expected growth and backup activity. The same volume can appear once per file, so don’t sum those free-space values. Review growth increments in their proper units. Percentage growth and page-based growth are different settings.

DECLARE @trace nvarchar(260) =
    (SELECT path FROM sys.traces WHERE is_default = 1);
IF @trace IS NOT NULL
    SELECT DatabaseName, EventClass, COUNT(*) AS growth_events,
           MIN(StartTime) AS first_event, MAX(StartTime) AS last_event
    FROM sys.fn_trace_gettable(@trace, DEFAULT)
    WHERE EventClass IN (92, 93)
    GROUP BY DatabaseName, EventClass;

The default trace is a limited, deprecated source of retained growth events. It can be disabled or have already rolled over. No rows do not prove that growth never happened. Use a planned Extended Events collection when you need dependable history beyond the retained trace window.

Find Users That Need Mapping Review

SELECT dp.name AS database_user, dp.type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.authentication_type_desc = 'INSTANCE'
  AND dp.type = 'S'
  AND dp.principal_id > 4
  AND sp.sid IS NULL;

This checks instance-authenticated SQL users in the current database for a matching visible server principal. It deliberately doesn’t classify contained users the same way. Review the result with adequate metadata visibility. An intentional loginless user or inaccessible metadata needs context before any repair.

Use an approved login mapping when repair is required. Don’t grant broad roles to make an application connect. That exchanges an identity problem for a permissions problem and hides the original cause.

Turn Findings Into Follow-Up Work

Compare the captured build with Microsoft’s current release table for that major version and servicing branch. The query itself cannot know whether a later update exists. Read applicable fixes and known issues before planning maintenance. Record the source and review date outside the script.

Finish with a short list of gaps, owners, and next checks. Include backup restore tests, missing integrity output, and uncertain collection coverage. A monthly review is useful only when its findings lead somewhere. Keep the previous result so you can see whether the same issue returns.

A health check is not a certificate of health, it is a review of evidence and the gaps that need attention.

This post was rewritten from scratch in September 2026. The original, published on 2010-06-25, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.