Quick Scan Report – SQL Server Recent Restart

What this check looks for

The creation date of tempdb, from sys.sysdatabases, within the last 24 hours. Tempdb is recreated every time SQL Server starts, so its creation date is the instance start time.

This is informational rather than a fault. The scan reports it at severity Info, and its value is context: several other findings read differently on an instance that restarted last night.

Why it matters

Two reasons, and the second is the one people miss.

First, if the restart was not planned, something caused it. SQL Server does not restart itself for no reason. The candidates are all worth knowing about:

• A Windows update and reboot, which is usually the answer and is usually fine.
• A failover, on a cluster or an Availability Group.
• A crash. A severity 25 error, an access violation, or a non-yielding scheduler that ended in the service terminating. Those leave a memory dump, which has its own check.
• The service account, a resource limit, or somebody restarting it to “fix” something.

Second, a restart resets nearly every performance measurement on the instance, and several other checks read them. After a restart:

• sys.dm_os_wait_stats is empty and starts accumulating from zero. Wait analysis on an instance up for two hours tells you about those two hours and nothing else.
• sys.dm_io_virtual_file_stats resets, so the I/O latency checks are averaging a very small sample.
• sys.dm_db_index_usage_stats resets, which is the important one: the unused index and index usage reports rely on it, and after a restart every index looks unused. Acting on that is how a needed index gets dropped.
• The plan cache is empty, so everything recompiles and the first run of everything is slower.
• The buffer pool is cold, so page life expectancy starts low and climbs.

So this finding is partly a warning label on the rest of the scan. A report full of “unused” indexes and unremarkable wait statistics, on an instance that restarted six hours ago, is not telling you what it appears to.

How to confirm it yourself

SELECT [sqlserver_start_time]                                        AS [started],
       DATEDIFF(HOUR, [sqlserver_start_time], GETDATE())             AS [hours_up],
       DATEDIFF(DAY,  [sqlserver_start_time], GETDATE())             AS [days_up]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

Why it restarted, which is in the previous error log, because the restart cycled the current one:

EXEC sp_enumerrorlogs;
EXEC sp_readerrorlog 1, 1, N'shutdown';
EXEC sp_readerrorlog 1, 1, N'terminating';
EXEC sp_readerrorlog 1, 1, N'Severity: 2';

And the start of the current log, which records how it came up:

EXEC sp_readerrorlog 0, 1, N'SQL Server is starting';
EXEC sp_readerrorlog 0, 1, N'Recovery is complete';

A clean shutdown logs it. An absence of any shutdown message before the startup entries means it was not a clean stop, which points at a crash, a host level reset or a failover.

Whether anything crashed:

SELECT [creation_time], [filename], [size_in_bytes] / 1048576 AS [size_mb]
  FROM sys.dm_server_memory_dumps WITH (NOLOCK)
 ORDER BY [creation_time] DESC;

How to fix it

There is nothing to fix if it was planned. The work is to find out whether it was.

1. Check with whoever patches the servers. A Windows update cycle explains most of these and needs no further action.
2. Read the previous error log for a clean shutdown message. Present means deliberate; absent means it stopped without being asked.
3. Check for a memory dump at the same time. One means a crash, and the memory dumps check covers what to do next.
4. If it is clustered or in an Availability Group, check whether it was a failover rather than a restart, and why. A failover caused by a heartbeat timeout points at something specific, and priority boost is one known cause with its own check.
5. Check the Windows system event log for an unexpected shutdown event, which distinguishes an operating system crash from a SQL Server one.

Then adjust how you read the rest of the scan. On a recently restarted instance:

• Treat the unused index findings as unreliable until the instance has been up through a full business cycle, including month end.
• Treat the wait statistics and I/O latency findings as a small sample.
• Come back to the scan in a week.

If restarts are frequent, that is the finding rather than any individual one. An instance restarting weekly without a maintenance schedule to explain it needs the error log read properly rather than the restart noted.

How long it takes

About an hour to establish why. Nothing to fix if it was planned.

Related reports

Related checks

Frequently asked questions

We patch monthly. Can we ignore this? Yes, once you have confirmed the timing matches. The second half of the page still applies: the performance findings in this scan are based on a short sample.

How long until the statistics are meaningful again? For wait statistics and I/O, a few days of representative load. For index usage, a full business cycle, which usually means a month.

tempdb creation date seems an odd way to measure uptime. It is reliable and it works on every version. sys.dm_os_sys_info.sqlserver_start_time says the same thing more directly on modern versions.

The instance restarts every night. Then something is restarting it deliberately, or it is crashing nightly. Both need the error log read. Neither is normal.