SQL Server Production Outage: A Calm Runbook for the First 30 Minutes

The phone rings: “The system is down.” Orders aren’t saving, the ERP screen is frozen, and five people are already logged in to the database server. What happens in the next 30 minutes usually decides whether this is a 15-minute incident or a four-hour outage.

After years of answering these calls, we’ve learned that most long outages aren’t caused by the original problem. They’re caused by what people do in the first minutes: a restart at the wrong moment, a KILL on the wrong session, or five people changing five things at once. This runbook is the calm, ordered approach our DBA team follows, and that we recommend to every team that runs SQL Server in production.

What Not to Do: The Panic Moves That Turn Minutes into Hours

These are the mistakes we see most often in the field, and each one has made an outage longer than it needed to be:

• Restarting the server or the SQL Server service immediately. If long transactions were running, every database has to go through recovery after the restart and roll them back before it comes online. Without Accelerated Database Recovery, recovery time is roughly proportional to the longest active transaction. A system that was slow becomes a system that’s completely offline.
• Stopping SQL Server Agent jobs, related or not. Stopping a job that’s in the middle of a large data load or index rebuild doesn’t make it disappear; it starts a rollback that can take as long as the work already done, and can push a database into recovery.
• Killing long-running transactions in Activity Monitor. The session doesn’t go away; it shows up as KILLED/ROLLBACK and keeps holding its locks until the rollback completes. Killing the wrong session can make the blocking worse, not better.
• Everyone intervening at the same time. One person changes the virtual machine’s resources, another kills processes, a third runs DBCC FREEPROCCACHE and forces every query to recompile, a fourth changes MAXDOP. Afterwards nobody knows which change did what, and some of them have made things worse.
• Restarting after all of that. This is the worst combination: the rollbacks started by the kills and stopped jobs now have to finish inside crash recovery, and the database stays closed for a long time while everyone waits.

Minutes 0–5: Stop, Organize, Write Things Down

1. Name one incident lead. One person decides what changes on the server. Others can investigate, but nobody changes anything without the lead knowing.
2. Freeze changes. No restarts, no kills, no configuration changes, no VM changes until the first diagnosis is done.
3. Start an incident log. A shared document or chat thread with timestamps: what was observed, what was changed, by whom. It feels slow; it saves hours.
4. Ask “what changed?” A deployment, a new report, a patch, a configuration change, a storage or network change in the last hours or days is often the fastest path to the cause.
5. Define the symptom precisely. Is everything down, or one application? Can you connect at all? Slow, blocked, or returning errors? Each points to a different cause.

Minutes 5–15: Diagnose Before You Act

When our team picks up an incident, these are the first places we look, in roughly this order.

1. Monitoring alerts and the error log

If you have monitoring, start with what it reported and when. Then read the SQL Server error log: errors 823, 824 or 825 point to storage; 9002 means a transaction log is full; 17883 or similar scheduler warnings point to CPU or worker problems; a database in recovery shows its progress there. See our SQL Server Error Log guide and SQL Server Alerts.

-- Most recent error log entries containing "error" or recovery progress
EXEC sys.sp_readerrorlog 0, 1, N'error';
EXEC sys.sp_readerrorlog 0, 1, N'Recovery of database';

2. What is running and who is blocking whom

Look at active requests, waits and the blocking chain. Many teams use the free community procedure sp_WhoIsActive for this; our team also uses its own in-house scripts. Built-in DMVs work everywhere:

SELECT r.session_id, r.blocking_session_id, r.status, r.command,
       r.wait_type, r.wait_time / 1000 AS wait_sec,
       r.total_elapsed_time / 1000 AS elapsed_sec,
       DB_NAME(r.database_id) AS database_name,
       s.login_name, s.host_name, s.program_name,
       t.text AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Find the head blocker: the session that blocks others but isn’t blocked itself. It isn’t always an active query. A common pattern is an application that opened a transaction and never closed it; the session sits sleeping with an open transaction, holding locks:

-- Sleeping sessions that still hold an open transaction
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.open_transaction_count, s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.status = N'sleeping' AND s.open_transaction_count > 0
ORDER BY s.last_request_end_time;

3. Agent jobs and resource usage

Check which SQL Server Agent jobs are running right now and since when: a maintenance job, a large import or a report scheduled at the wrong time is a frequent cause. Then check CPU usage across logical processors, memory pressure, and whether a single query or session dominates.

4. Disk latency and free space

Slow storage makes everything slow, and a full disk stops everything. Check both:

-- Free space on every volume that holds database files
SELECT DISTINCT vs.volume_mount_point,
       vs.total_bytes / 1073741824 AS total_gb,
       vs.available_bytes / 1073741824 AS free_gb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

-- Why can't the log be reused? (LOG_BACKUP, ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA ...)
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE log_reuse_wait_desc <> N'NOTHING';

-- Average I/O latency per file since startup (look for files far above the rest)
SELECT DB_NAME(vfs.database_id) AS database_name, mf.physical_name,
       vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_ms,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_write_ms DESC;

Minutes 15–30: The Smallest Safe Fix, Then Communicate

Once you know the cause, choose the intervention with the least risk and the least work to undo:

• Blocking: kill only the head blocker, and only after checking what it is doing and how much work it has done. A session that has been updating rows for two hours will take a long time to roll back. After the kill, watch the rollback instead of guessing: KILL 87 WITH STATUSONLY; reports the percentage complete and the estimated time left.
• Full transaction log: find the reason in log_reuse_wait_desc and fix that reason: take a log backup, deal with the open transaction, or fix the stuck replication or availability replica. Add log space on another volume if needed. Don’t delete or shrink log files in a panic. See SQL Server Transaction Log Too Big?
• Full data disk: free or add space, or add a file on another volume. Then fix autogrowth so files don’t try to grow in tiny steps at the busiest moment of the day.
• Storage or corruption errors: stop and follow a recovery plan instead of improvising. See SQL Server Database Corruption and, if a database won’t come online, RECOVERY PENDING or SUSPECT?
• A database in recovery: wait, and watch the progress messages in the error log. Restarting doesn’t make it faster.

Then communicate: what happened, what you did, what users should expect, and when you’ll update them next. A short, honest message every 15–30 minutes prevents a flood of calls that slows the people doing the work.

A Field Example: The Month-End Report

At a large manufacturer, the month-end report locked the whole system. Our team found the blocking chain, identified the report’s session as the head blocker and stopped that one operation. Everything else was left alone. The system was back to normal in 12 minutes. Afterwards we tuned the report itself: a run that had taken four hours now finishes in 19 minutes, so the same outage doesn’t come back next month.

The Root Causes We See Most Often

Most outages we’re called for trace back to a short list of causes. Nearly all of them can be prevented:

• Poorly written third-party application queries, and applications that don’t manage connections or transactions well: connections opened and never closed, sessions left sleeping with open transactions, transaction handling that leads to deadlocks. See SQL Deadlocks.
• No real backup policy, leading to full transaction logs, or replication that can’t keep up. See How to Build a SQL Server Backup Plan.
• Replication and Always On performance not watched, so the log can’t be truncated while a replica or log reader falls behind.
• Storage not following best practices, and disk latency that only shows up under load.
• Disks running out of space at peak hours, often combined with wrong autogrowth settings: the database tries to grow at the busiest moment and every session waits for it. See Database Autogrowth Best Practices.
• Missing index maintenance and integrity checks, so problems such as corruption are found during an outage instead of before it. See SQL Server Maintenance Plans.

After the Incident

1. Write the root cause down while it’s fresh, using the incident log.
2. Fix the cause, not just the symptom: tune the query, fix the application’s connection handling, add the alert that would have warned you, correct the autogrowth setting.
3. Check the databases: run DBCC CHECKDB if there was any storage error or unexpected shutdown, and confirm backups are running again.
4. Update this runbook with what you learned.

Questions We’re Asked During Every Outage

Aryasoft’s managed DBA customers have committed response times for exactly these moments: SLA customers get a response within 30 minutes during business hours and 2 hours outside them; maintenance-support customers within 2 hours and 4 hours.