There’s a specific kind of dread every DBA knows. You open your laptop, coffee in hand, and before you’ve even logged in you’re already bracing for a Slack message that starts with “Hey, is the database down?” or an email chain titled “URGENT – Report not loading.”
Here’s the thing: almost every one of those fire drills was preventable. Somewhere in the night, a job failed silently, a disk crept past 90%, a backup didn’t run, or a query started chewing through CPU nobody noticed until users did. A good morning health check isn’t busywork. It’s the difference between you finding the problem at 8:15 AM with your coffee still warm, versus your VP finding it at 11 AM with an angry customer on the phone.
I’ve been doing this for 18+ years across on-prem SQL Server and Azure SQL environments, and the checklist below is essentially the muscle memory I’ve built up over that time turned into something you can follow step by step, whether you’re two weeks into your first DBA job or you’ve been doing this for a decade and just want a reality check on your own routine.
Treat this as a script. Run it in order. Don’t skip steps because “it’s probably fine” the checks that get skipped are always the ones that bite you.
A quick note before we start: not every environment needs a human to eyeball all 25 of these every single day. Ideally, most of this is automated into alerts (SQL Agent, Azure Monitor, dashboards) and your “morning check” becomes a five-minute review of an exceptions report. But you have to build that automation on top of understanding what to check and why — so read this as the foundation, then automate as you go.
Before You Start: Set Up Your Workspace
Open a query window connected to your central management server (or loop through your instance list), and keep this article open in another tab the first few times you run through it. Within a couple of weeks, this becomes automatic.
If you manage more than a handful of instances, register them under a Central Management Server (CMS) so you can run these checks against all of them at once instead of connecting one by one.
1. Check SQL Server Agent Job Failures
This is check #1 for a reason. Job failures are the single most common thing that goes wrong overnight, and they’re silent unless you look.
What to check:
SELECT
j.name AS JobName,
h.run_date,
h.run_time,
h.run_status,
h.run_duration,
h.message
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.step_id = 0
AND h.run_status <> 1 -- 1 = Succeeded
AND h.run_date = CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))
ORDER BY h.run_date DESC, h.run_time DESC;
What you’re looking for: Any row where run_status is 0 (failed), 3 (canceled), or 4 (in progress but stuck).
The fix: Open the job step, read the actual error message (don’t guess), and re-run manually if it’s safe to do so. If it’s a recurring failure — same job, same error, multiple days — that’s not a “re-run it” problem anymore, that’s a “fix the root cause” problem. Common culprits: expired credentials, a linked server that went down, a file path that changed, or someone editing the job step without telling you.
2. Verify Last Night’s Backups Actually Ran and Are Restorable
A backup job showing “Succeeded” and a backup you can actually restore are two different things. Don’t confuse the two.
What to check:
SELECT
d.name AS DatabaseName,
d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.database_id > 4 -- skip system DBs
GROUP BY d.name, d.recovery_model_desc
ORDER BY LastFullBackup ASC;
What you’re looking for: Any database where LastFullBackup is older than your RPO allows, or where a database in FULL recovery model has no recent log backup (your log file is probably ballooning right now, too — see check #11).
The fix: Kick off a manual backup immediately for anything out of policy. Then, separately — not urgently, but on your list for the week — actually test a restore of a random database to a scratch instance. A backup you’ve never restored is a theory, not a backup plan. Do this monthly at minimum for your critical databases.
3. Scan the SQL Server Error Log
The error log is where SQL Server tells you things are wrong before anyone else notices. Most DBAs only open it when something’s already broken — check it proactively instead.
What to check:
EXEC xp_readerrorlog 0, 1, N'error';
EXEC xp_readerrorlog 0, 1, N'failed';
EXEC xp_readerrorlog 0, 1, N'corrupt';
EXEC xp_readerrorlog 0, 1, N'timeout';
What you’re looking for: Login failures spiking (possible attack or an app with a bad connection string), I/O warnings, memory pressure messages, or anything mentioning corruption.
The fix: Correlate the timestamp with what else was happening then (a deploy? a backup window? a batch job?). One-off transient errors are usually fine to note and move on. Repeated errors at the same time every night mean something in your environment is consistently causing that condition — go find it.
4. Check for Database Corruption (DBCC CHECKDB Results)
This is the check that, when it flags something, should jump to the top of your day.
What to check:
SELECT
d.name,
dbi.last_good_check_date
FROM sys.databases d
CROSS APPLY (
SELECT DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS last_good_check_date
) dbi
WHERE d.database_id > 4;
Also review your scheduled DBCC CHECKDB job output directly if you have one running nightly or weekly.
What you’re looking for: Any database where the last good check is older than expected, or where CHECKDB reported allocation or consistency errors.
The fix: If corruption is found, do not panic-run repair commands immediately. First, isolate what’s affected, check if you have a clean recent backup, and if this is a system that matters, get a second pair of eyes. REPAIR_ALLOW_DATA_LOSS is a last resort, not a first response. Often the safer path is restoring the affected pages or the whole database from a known-good backup.
5. Check Disk Space on All Drives
Boring. Unglamorous. Also the cause of an enormous number of 2 AM pages.
What to check:
SELECT
vs.volume_mount_point,
CONVERT(DECIMAL(10,2), vs.available_bytes / 1073741824.0) AS FreeSpaceGB,
CONVERT(DECIMAL(10,2), vs.total_bytes / 1073741824.0) AS TotalSpaceGB,
CONVERT(DECIMAL(5,2), (vs.available_bytes * 100.0) / vs.total_bytes) AS PercentFree
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
GROUP BY vs.volume_mount_point, vs.available_bytes, vs.total_bytes
ORDER BY PercentFree ASC;
What you’re looking for: Anything under 15-20% free. Data drives fill up faster than you expect, and log drives can fill up in minutes during a runaway transaction.
The fix: Short-term: shrink what’s safe to shrink, clean up old backup files sitting on the same drive (a classic mistake), or move a file to another drive. Long-term: set up proactive alerting at 20% free so you’re never reading this in a panic, and build actual capacity planning into your quarterly routine instead of reacting drive by drive.
6. Check Database File Auto-Growth Events
If your files are growing constantly overnight, that’s not “working as intended” — that’s a sizing problem in disguise.
What to check:
SELECT
d.name AS DatabaseName,
mf.name AS FileName,
mf.type_desc,
fs.event_time,
fs.duration
FROM sys.fn_trace_gettable(
(SELECT TOP 1 path FROM sys.traces WHERE is_default = 1), DEFAULT) fs
INNER JOIN sys.databases d ON fs.DatabaseName = d.name
WHERE fs.EventClass IN (92, 93) -- Data/Log file auto-grow
AND fs.StartTime > DATEADD(HOUR, -24, GETDATE());
(On newer versions, pull this from the default trace or Extended Events session instead if the default trace is disabled.)
What you’re looking for: Frequent small growth events, especially on the log file. Each one is a mini-pause for your application while SQL Server zeroes out the new space.
The fix: Resize the file manually to a sensible size upfront, and change the auto-growth setting from a small percentage to a fixed, reasonably large MB value. Growing in 1MB or 10% increments dozens of times a night is a performance tax you’re paying for no reason.
7. Review Overnight Blocking and Deadlocks
What to check:
SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.status,
t.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;
For deadlocks specifically, check your system_health Extended Events session — it captures deadlock graphs automatically without you setting anything up.
What you’re looking for: A chain of blocking right now is urgent. A deadlock graph from 2 AM is historical, but still worth understanding — deadlocks between the same two objects, repeating night after night, mean there’s a real fix needed (usually indexing or transaction order), not just bad luck.
The fix: For active blocking, identify the head blocker and decide whether to let it finish or kill it — don’t kill first and ask questions later. For recurring deadlocks, look at the access order of the objects involved and consider adding a covering index so one of the transactions grabs fewer locks, or shortening the transaction so it holds locks for less time.
8. Check Long-Running and Currently Executing Queries
What to check:
SELECT
r.session_id,
r.start_time,
DATEDIFF(SECOND, r.start_time, GETDATE()) AS RunningSeconds,
r.status,
r.command,
t.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY RunningSeconds DESC;
What you’re looking for: Anything that’s been running far longer than it should for what it is — a report query running for 4 hours, or an INSERT that’s normally instant now sitting there for 20 minutes.
The fix: Check the execution plan for the running query if you can (sys.dm_exec_query_plan), see if it’s blocked (tie back to check #7), and check if it’s waiting on I/O, CPU, or locks. If it’s genuinely just a bad plan or missing index, that’s your investigation for later in the day — don’t rewrite a query while it’s mid-flight in production.
9. Check CPU Utilization
What to check:
SELECT TOP 1
SQLProcessUtilization AS SQLServerCPU,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessCPU,
SystemIdle
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
FROM (
SELECT CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
) AS x
) AS y
ORDER BY record_id DESC;
What you’re looking for: Sustained high CPU (not a brief spike). If SQL Server’s own CPU usage is consistently above 80-85%, you have a real problem, either from query load or a runaway process.
The fix: Identify the top CPU-consuming queries via sys.dm_exec_query_stats sorted by total_worker_time, and see if there’s a pattern — one bad query, a new report someone scheduled, or genuine growth in load that means it’s time to talk about scaling up (or, in Azure SQL, moving to a higher service tier).
10. Check Memory Pressure and Page Life Expectancy
What to check:
SELECT
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Page life expectancy', 'Buffer cache hit ratio', 'Free Memory (KB)')
AND object_name LIKE '%Buffer Manager%';
What you’re looking for: Page Life Expectancy (PLE) that’s low relative to your buffer pool size, or dropping sharply — that means pages are getting flushed out of memory fast, which means SQL Server is going back to disk more than it should.
The fix: A single low PLE reading isn’t an emergency — a sustained low or crashing PLE means either you need more memory, or a query is doing a huge scan and blowing through the buffer cache. Check for that query first; adding RAM to mask a bad query just delays the real fix.
11. Check Transaction Log Space Usage
What to check:
DBCC SQLPERF(LOGSPACE);
What you’re looking for: Any database with log space used over 70-80%, especially ones in FULL recovery model without regular log backups (tie this back to check #2).
The fix: If it’s genuinely full and growing, take a log backup now (that’s usually the actual fix, not a shrink). Only shrink the log file after backing it up and understanding why it grew — shrinking without understanding the cause just means it’ll grow right back tonight.
12. Check for Suspect or Offline Databases
What to check:
SELECT name, state_desc, is_read_only
FROM sys.databases
WHERE state_desc NOT IN ('ONLINE');
What you’re looking for: Anything showing SUSPECT, RECOVERY_PENDING, or OFFLINE that shouldn’t be.
The fix: This is a drop-everything check. A SUSPECT database usually means corruption or a failed recovery — check the error log immediately (check #3) for the specific reason before attempting anything. Don’t run ALTER DATABASE ... SET EMERGENCY as a reflex; understand what happened first.
13. Check Failed Login Attempts
What to check:
EXEC xp_readerrorlog 0, 1, N'Login failed';
What you’re looking for: A sudden spike in failed logins, especially from unfamiliar IPs or against the sa account. This is often the first sign of a brute-force attempt.
The fix: If it looks like an attack, involve your security team, consider blocking the source IP at the firewall, and confirm sa is disabled (it should be, always). If it’s an application account, check whether a password rotated somewhere and an app config wasn’t updated — that’s the more common, boring cause.
14. Check AlwaysOn Availability Group Health (if applicable)
What to check:
SELECT
ag.name AS AGName,
ars.role_desc,
ars.synchronization_health_desc,
drs.synchronization_state_desc,
drs.log_send_queue_size,
drs.redo_queue_size
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id;
What you’re looking for: Any replica not HEALTHY, or a growing log_send_queue_size / redo_queue_size, which means your secondary is falling behind.
The fix: Falling behind is usually network latency, a secondary under-resourced for the workload, or a large transaction still being applied. If synchronization health shows NOT_HEALTHY, check network connectivity between replicas first — that’s the most common cause.
15. Check Replication Health (if applicable)
What to check: Open Replication Monitor, or query:
EXEC sp_replmonitorsubscriptionpendingcmds
@publisher = N'YourPublisher',
@publisher_db = N'YourDB',
@publication = N'YourPub',
@subscriber = N'YourSubscriber',
@subscriber_db = N'YourSubDB',
@subscription_type = 0;
What you’re looking for: A large or growing number of pending commands, which means the subscriber is falling behind the publisher.
The fix: Check the distribution agent is actually running, check for blocking on the subscriber, and if the latency is real and growing, look at whether a recent large batch load at the publisher is the cause — that’s normal and will catch up. If it’s a steady daily creep, that’s a capacity issue.
16. Review TempDB Health
What to check:
SELECT
SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB
FROM sys.dm_db_file_space_usage;
SELECT
session_id, request_id,
(internal_objects_alloc_page_count + user_objects_alloc_page_count) * 8 AS TempDBUsageKB
FROM sys.dm_db_task_space_usage
ORDER BY TempDBUsageKB DESC;
What you’re looking for: TempDB filling up fast, or one specific session hogging it — often a big sort, hash join, or someone’s poorly written temp-table-heavy stored procedure.
The fix: Identify the query driving the usage and see if it can be rewritten to use less TempDB (fewer sorts, better indexing to avoid a sort/spill in the first place). Also confirm you have multiple TempDB data files configured (as many as up to 8, matched to core count for most workloads) — a single TempDB file on a busy server is a classic, easily fixed bottleneck.
17. Check Wait Statistics Since Last Restart
What to check:
SELECT TOP 10
wait_type,
wait_time_ms,
waiting_tasks_count,
wait_time_ms / NULLIF(waiting_tasks_count, 0) AS AvgWaitMs
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','SLEEP_TASK','BROKER_TASK_STOP')
ORDER BY wait_time_ms DESC;
What you’re looking for: The story your server is telling you about what it spends most of its time waiting on — PAGEIOLATCH waits point to disk I/O, CXPACKET/CXCONSUMER points to parallelism, LCK_M_* points to blocking.
The fix: This isn’t a single-fix check — it’s a diagnostic lens. Use it to decide where to spend your investigation time today rather than guessing. If I/O waits dominate week over week, that’s a hardware/storage conversation. If lock waits dominate, go back to your blocking and indexing work.
18. Check Index Fragmentation on Key Tables
Not strictly a daily fix, but worth a quick daily glance on your most critical, high-churn tables — with the real remediation scheduled weekly.
What to check:
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;
What you’re looking for: Fragmentation over 30% on pages that actually matter (small tables with high fragmentation percentages usually aren’t worth worrying about).
The fix: Reorganize between 10-30% fragmentation, rebuild above 30% — but this belongs in a scheduled maintenance job, not a live morning fix. Note it, add it to this week’s maintenance window if it’s not already covered.
19. Check for Missing Index Recommendations (With a Healthy Dose of Skepticism)
What to check:
SELECT
mid.statement AS TableName,
migs.avg_user_impact,
migs.user_seeks + migs.user_scans AS UsageCount,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
What you’re looking for: High-impact, high-usage suggestions — not every row on this list. SQL Server’s missing index DMVs are a hint, not a mandate, and they don’t know about indexes you already have that are close enough.
The fix: Don’t blindly create every suggested index — that’s how you end up with 15 overlapping indexes on one table, which hurts write performance. Cross-check against existing indexes, consolidate where you can, and only add what genuinely earns its keep.
20. Review Recent Schema or Permission Changes
What to check:
SELECT
creation_date,
modify_date,
name,
type_desc
FROM sys.objects
WHERE modify_date > DATEADD(DAY, -1, GETDATE())
AND is_ms_shipped = 0
ORDER BY modify_date DESC;
Also review your login/permission audit if you have one configured — you should.
What you’re looking for: Unexpected changes — someone modified a stored procedure or added a login outside of your normal change process.
The fix: If it’s unexpected, find out who and why before anything else touches it. This is less about “fixing” a technical issue and more about catching process gaps — undocumented emergency changes, a developer with more access than they should have, or a change that should have gone through review and didn’t.
21. Check Server and Database-Level Configuration Drift
What to check: Compare current sp_configure output and key database settings (auto-shrink, recovery model, compatibility level) against your documented baseline for that server.
EXEC sp_configure;
SELECT name, recovery_model_desc, is_auto_shrink_on, compatibility_level
FROM sys.databases;
What you’re looking for: Anything that drifted from baseline — auto-shrink accidentally turned on (it should almost never be on), compatibility level changed after a migration, max memory reset to default after a patch.
The fix: Correct the drift, and more importantly, figure out how it drifted. Patching activity and restores from another environment are common causes of settings silently reverting.
22. Verify SQL Server and Windows Services Are Running as Expected
What to check: Confirm SQL Server, SQL Agent, and any dependent services (Full-Text, Reporting Services, Analysis Services if used) are up and set to the correct startup accounts.
What you’re looking for: A service that restarted unexpectedly overnight — check the Windows Event Log alongside the SQL error log for the real reason (patching, OOM kill, a crash).
The fix: If it restarted due to an unhandled patch or reboot, that’s usually fine, just confirm nothing else broke on the way back up (jobs that should’ve run during the outage, connections that need to reconnect). If it crashed, that needs a root-cause dig, not just a restart-and-move-on.
23. Check Linked Server and External Connectivity
What to check:
EXEC sp_testlinkedserver 'YourLinkedServerName';
Loop through all linked servers you rely on for ETL, reporting, or cross-database queries.
What you’re looking for: Any linked server that fails to connect — often due to an expired credential, a firewall change, or the remote server being down.
The fix: Confirm whether the remote side changed anything (new firewall rule, rotated password, renamed instance), and update the linked server definition accordingly. If it’s used by an overnight job, check whether that job’s failure this morning (check #1) traces back to exactly this.
24. Review Query Store / Top Resource Consumers (Azure SQL and On-Prem with Query Store Enabled)
What to check:
SELECT TOP 10
qt.query_sql_text,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads,
rs.count_executions
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -12, GETDATE())
ORDER BY rs.avg_duration DESC;
What you’re looking for: Any query whose performance shifted overnight compared to its usual baseline — Query Store is exceptional at catching plan regressions, where a perfectly fine query suddenly gets a bad plan after a stats update or parameter sniffing issue.
The fix: If you spot a regression, Query Store lets you force the previous good plan immediately as a stopgap (sp_query_store_force_plan), buying you time to investigate the real fix — usually a stats or parameterization issue — without the pressure of an ongoing incident.
25. Check Your Monitoring/Alerting System Itself
The most overlooked check on this list: is your monitoring actually monitoring?
What to check: Confirm your monitoring tool (whatever you use — SQL Sentry, Redgate, Azure Monitor, a custom dashboard) checked in recently and isn’t silently stuck. Check that alert emails/pages are actually being delivered, not swallowed by a spam filter or a dead distribution list.
What you’re looking for: A gap — no alerts overnight from a system that should generate at least routine noise, a dashboard showing stale data, a “last updated” timestamp from yesterday.
The fix: A silent monitoring system is worse than no monitoring system, because it gives you false confidence. If something’s stuck, fix it before you trust any of the “all clear” signals from the rest of this list today.
Putting It All Together
That’s 25 checks, and yes, the first few times through this will take you a while. That’s fine — you’re building judgment, not just running a script. Over time:
- Automate what you can. Most of these checks should become alerts, not manual queries you run every morning. Your goal is for the morning check to become “review the exceptions dashboard,” not “run 25 scripts by hand.”
- Prioritize by blast radius. Corruption (#4), suspect databases (#12), and backup failures (#2) get looked at first, every time, no exceptions. Index fragmentation (#18) can wait until after your coffee.
- Keep a running log. Note what you found and what you did about it each day. Six months from now, that log tells you exactly which servers are chronically unhealthy and where your real capacity or design problems live — patterns you’d never spot from any single day’s check.
- Don’t skip steps because yesterday was clean. The morning it goes wrong is never the morning you expect.
Good DBA work is mostly invisible — nobody notices the outage that didn’t happen. This checklist is how you make sure it stays that way.
Quick-Reference Summary Table
Bookmark this. Once you’ve internalized the details above, this is the version you’ll actually glance at every morning.
| # | Check | What You’re Looking For | Quick Fix |
|---|---|---|---|
| 1 | SQL Agent job failures | Any status ≠ Succeeded overnight | Read the real error, re-run if safe; fix root cause if recurring |
| 2 | Backups ran and are restorable | Full backup older than RPO; missing log backups | Kick off manual backup; test-restore critical DBs monthly |
| 3 | SQL Server error log | Login spikes, I/O warnings, corruption mentions | Correlate timestamp with other activity; chase repeating errors |
| 4 | Database corruption (CHECKDB) | Stale last-good-check date; allocation/consistency errors | Isolate, confirm clean backup, don’t repair-run blindly |
| 5 | Disk space on all drives | Under 15–20% free anywhere | Clear space now; set 20% alerting and quarterly capacity planning |
| 6 | Auto-growth events | Frequent small growths, especially on log files | Resize files upfront; switch to fixed MB growth |
| 7 | Overnight blocking/deadlocks | Active blocking chains; repeating deadlock pairs | ID head blocker before killing; fix via indexing/transaction order |
| 8 | Long-running/executing queries | Queries running far longer than normal | Check plan and blocking; investigate later, don’t rewrite live |
| 9 | CPU utilization | Sustained >80–85% SQL Server CPU | Find top CPU queries; assess scaling if load genuinely grew |
| 10 | Memory pressure / PLE | Low or sharply dropping Page Life Expectancy | Find the scan-heavy query first; don’t just add RAM |
| 11 | Transaction log space | Log space used >70–80%, esp. in FULL recovery | Take a log backup now; shrink only after understanding cause |
| 12 | Suspect/offline databases | Any state other than ONLINE | Drop-everything check — read error log before acting |
| 13 | Failed login attempts | Spikes, especially against sa or unfamiliar IPs | Loop in security if attack; check for a stale app password otherwise |
| 14 | AlwaysOn AG health | Replica not HEALTHY; growing send/redo queue | Check network latency between replicas first |
| 15 | Replication health | Growing pending command count | Confirm distribution agent running; check subscriber blocking |
| 16 | TempDB health | Rapid fill-up or one session hogging it | Find the driving query; confirm multiple TempDB files configured |
| 17 | Wait statistics since restart | Top waits (PAGEIOLATCH, CXPACKET, LCK_M_*) | Use as a diagnostic lens to prioritize the day’s investigation |
| 18 | Index fragmentation | >30% fragmentation on tables that matter | Reorganize 10–30%, rebuild >30% — schedule, don’t fix live |
| 19 | Missing index recommendations | High-impact, high-usage suggestions | Cross-check existing indexes before adding anything |
| 20 | Recent schema/permission changes | Unexpected object or login changes | Find who and why before anything else touches it |
| 21 | Config drift (sp_configure, DB settings) | Deviation from documented baseline | Correct it, then trace how it drifted (patch/restore) |
| 22 | SQL/Windows services running | Unexpected restart overnight | Confirm nothing broke on the way back up; root-cause crashes |
| 23 | Linked server/external connectivity | Any linked server failing to connect | Check for expired creds, firewall changes; update definition |
| 24 | Query Store / top resource consumers | Plan regressions vs. usual baseline | Force last good plan as stopgap; fix stats/parameterization |
| 25 | Monitoring/alerting system itself | Stale dashboards, no overnight alerts at all | Fix monitoring first — you can’t trust an “all clear” without it |
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
