The server can look quiet while the oldest backup is missing and the disk is nearly full. A weekly routine gives SQL Server administration a fixed order of checks that fits an hour when alerts already cover urgent failures.
Start the Weekly Routine With Recovery Evidence
Look first at whether the databases can be restored. Review full, differential, and log backup recency against each database’s recovery goal. Check failed jobs, retained files, copy destinations, and the date of the last real restore test. A backup history row is useful, but it cannot prove the file still exists on independent storage. Record the oldest restore point the current chain supports.
I start here because every later tuning idea assumes recoverability. If a critical backup is missing, fix that before investigating a small plan regression. Which database would be hardest to restore today? Put its owner and recovery path at the top of the follow-up list.
Read Job Failures and Error Logs
Review SQL Server Agent failures where Agent exists, scheduled operating system tasks where it does not, and recent SQL Server error log entries. Group repeated alerts by root cause. A backup job that fails every night should not become background noise. Check whether a successful retry left the promised recovery window intact.
I compare the current week with the prior one. New login failures, I/O messages, or service restarts deserve context even if applications recovered. Avoid clearing an alert just because the server is running now. Save the error number, time, database, and related job result so the next investigation begins with evidence.
SELECT TOP (30) j.name AS job_name,
h.run_date, h.run_time, h.run_status, 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
ORDER BY h.instance_id DESC;Check Space and Growth
Review free space on data, log, tempdb, and backup volumes. Then check database file growth and recent trend rather than treating one large file as a problem by itself. A full volume can stop backups or writes. A log file that grew after a long transaction calls for a different response from steady application growth.
I note which files are close to a known operational limit and who owns the capacity decision. Do not shrink a file just to make a weekly chart look tidy. That trades one number for fragmentation and future regrowth. Ask whether the workload, retention, or a failed process explains the change.
Review Integrity and Restore Tests in the Weekly Routine
Check the latest DBCC CHECKDB results and whether the scheduled coverage includes every production database. Investigate any integrity error promptly with the recovery plan in hand. For very large databases, the organization can use a carefully designed schedule, but coverage and exceptions must be visible. A job marked successful without checking its output is not enough.
I also review the restore-test record. Did the selected backup set restore on another approved instance? Were required certificates and keys available? Did application checks pass? A weekly review does not require restoring every database each time, but it should confirm that the rotation is current and failures have owners.
Look for Performance Changes
Use Query Store or approved monitoring to find significant regressions in the application’s important queries. Compare like periods and note deployments, statistics changes, and traffic shifts. A single high CPU minute does not describe the week. Wait statistics and current requests can give clues, but they need workload context.
I choose one or two actionable investigations, not a list of every plan warning. Start with a query whose user-visible impact is clear. Check estimates against actual rows, index choices, and memory grants before changing instance-wide settings. The weekly routine should prevent slow drift, not become an unplanned tuning marathon.
Check Security and Configuration Drift
Review new privileged logins, failed access patterns, disabled audits, and changes to key instance settings. Confirm that service accounts and certificates have planned rotation dates. Do not print secrets into a weekly report. Record the approved change ticket beside any intentional difference. Unexpected drift needs a separate investigation.
This read-only query shows selected server configuration values. Keep the accepted baseline elsewhere and compare the two. A setting is only one part of security, but it gives the review a repeatable starting point.
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'max server memory (MB)', N'max degree of parallelism',
N'cost threshold for parallelism', N'backup compression default')
ORDER BY name;Finish the Weekly Routine With Owners and Deadlines
Spend the final part of the hour turning findings into work. State the observed fact, risk, owner, and next check. A low-priority trend can be monitored. A broken backup chain needs immediate action. Separate the two so the report does not make every item sound equally urgent.
I keep the weekly note short enough that another DBA will read it. Link each item to its evidence in the internal system, then carry unresolved items into the next review. What changed since last week, and what decision is needed now? That question gives the meeting a useful ending.
The weekly routine should have a way to record exceptions. A failed job, a growth spike and a new database without an owner are different kinds of follow-up, but each needs an accountable person and a due date. I keep the report short enough that someone can act on it. An inbox full of green status messages is not monitoring; it is a colorful filing cabinet.
Review trends instead of taking one snapshot as a verdict. A database that grew yesterday can reflect a planned import, while slow steady growth can exhaust a volume quietly. Compare with a baseline and write down the expected explanation. The same principle applies to backup age, job duration and storage headroom. When an item is resolved, record the evidence that it is resolved. A weekly checklist is valuable because it turns observations into actions and makes ownership visible before a problem becomes urgent.
The weekly routine also needs a stop rule. If a check exposes a possible integrity or recovery problem, escalate it now rather than waiting for next week’s report. Keep the routine repeatable, but do not let its calendar replace judgment. I set aside a few minutes to ask whether a newly discovered database is covered by the same backup, monitoring and ownership rules as the established ones. New objects are where tidy checklists quietly lose coverage.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Database Performance Health Check: Six Months Slow, Fixed in 75 Minutes.
A weekly checklist is not a health certificate, it is a reliable way to find work before work finds you.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
