SQL Server Monitoring: A Daily, Weekly and Monthly DBA Checklist

SQL Server monitoring answers two different questions. The first is “has something just broken?”, and it needs alerts that notify someone within minutes. The second is “is something about to break?”, and it needs regular checks, trends and a baseline. Many environments only have the first; some have neither, and problems are noticed when users pick up the phone.

This guide brings together what needs real-time alerts in SQL Server, the daily, weekly and monthly routine checks, and how to automate them, in a single checklist.

What Needs a Real-Time Alert

These events can’t wait; they must reach the right person as they happen:

• Service stops and restarts: an unexpected restart of the Database Engine or Agent service.
• Serious errors: errors with severity 17–25, and errors 823, 824 and 825, which indicate disk corruption.
• Disk space: data, log and backup disks dropping below a threshold.
• Log filling up: a transaction log close to full, and the reason it can’t be reused.
• Long blocking: a session blocking others for longer than a set time (for example 60 seconds).
• Failed jobs: especially backup, CHECKDB and critical ETL jobs.
• Missed backups: no log or full backup within the expected time.
• Always On: replicas falling out of sync, queues growing, or a failover.

Our SQL Server alerts guide explains how to set up error and severity alerts in detail. A single query shows why a log can’t be cleared:

SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE log_reuse_wait_desc NOT IN (N'NOTHING', N'CHECKPOINT');

Daily Checks

Alerts are the red light; daily checks are the dashboard. What a DBA should review in 15–20 minutes at the start of the day:

• Jobs that failed in the last 24 hours.
• The last full and log backups of every database.
• Free disk and file space.
• New errors and failed login attempts in the error log.
• Long-running queries and deadlocks overnight.
• The state of Always On replicas.

-- Job steps that failed in the last 24 hours
SELECT j.name AS job_name, h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at,
       LEFT(h.message, 300) AS message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status = 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) > DATEADD(DAY, -1, GETDATE())
ORDER BY run_at DESC;

-- Free disk space (every drive holding database files)
SELECT DISTINCT vs.volume_mount_point,
       vs.total_bytes / 1073741824.0     AS total_gb,
       vs.available_bytes / 1073741824.0 AS free_gb,
       CAST(100.0 * vs.available_bytes / vs.total_bytes AS decimal(5, 1)) AS free_pct
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY free_pct;

You can read overnight deadlocks from the system_health Extended Events session, which runs by default, without setting up anything new; details are in our deadlock guide.

Weekly Checks

• CHECKDB results: the last successful CHECKDB date for every database.
• Maintenance jobs: whether index and statistics maintenance completed, and whether it’s taking longer.
• Growth: changes in the size of databases and the largest tables.
• Changes: changes to server settings, jobs and schema (DDL).
• Security: new sysadmin members, new logins and the source IPs of repeated failed logins.

Monthly Checks

• Capacity trend: the monthly trend of disk, CPU and memory usage; when the disk will fill at the current rate.
• Baseline comparison: wait statistics and the most expensive queries compared with last month.
• Patch level: new cumulative updates and known problematic builds.
• Restore test: actually restoring at least one critical database from backup.
• Permission review: accounts of people who have left and permissions broader than needed.
• Report: a short status report for management and application teams.

Baseline: What Is “Normal”?

When someone complains that “the server is slow”, the first question should be “compared with what?”. Recording wait statistics, the most expensive queries, CPU and disk latency in a table at regular intervals gives you a baseline. During an incident, comparing today’s values with the same hour last week turns guesswork into diagnosis. Query Store already keeps this history at query level inside the database; make sure it’s enabled.

What to Automate

Even on servers without a monitoring license, a few simple Agent jobs make a big difference:

• A job that writes a snapshot of running requests to a table every few minutes and purges old rows. It’s the cheapest way to answer “why was the server slow last Tuesday at 3 p.m.?”.
• A job or DDL trigger that records changes to server settings, jobs and schema.
• A job that records database and table sizes weekly.
• A daily report summarizing failed login attempts with their source IPs.

Larger environments need a monitoring platform and a team on 24/7 call. The places our team checks first during an incident follow the same list: monitoring alerts, the error log, active sessions, Agent jobs, resource usage, disk latency and how CPU is spread across logical processors.

Monitoring Checklist

• Alerts exist for critical events and reach the right people.
• Database Mail works; a test email is sent regularly.
• Daily checks are a routine or an automated report.
• CHECKDB, backup and maintenance results are reviewed weekly.
• A monthly restore test is run and its duration recorded.
• A baseline is kept for wait statistics and growth.