Restart History: Preserve Startup Evidence Before Logs Cycle

Restart history needs startup evidence saved before retained logs disappear. A newly opened error log does not necessarily mean the database engine restarted. Keep the source, instance and timestamp attached to every observation.

Current startup time is one observation

On SQL Server, sys.dm_os_sys_info.sqlserver_start_time reports the current engine’s local startup time. It does not return a list of previous starts. Permissions depend on the version. SQL Server 2019 and earlier require VIEW SERVER STATE.

SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for this DMV. These are SQL Server permission rules. Do not assume that every hosted database has the same access model. Record the engine version and collector identity when designing a real collector.

Archive numbers identify files, rather than events

sp_readerrorlog reads retained logs. Archive zero selects the current log, and product one selects the SQL Server engine log. Product two selects the Agent log. The reader’s permission requirements also change by SQL Server version.

sp_cycle_errorlog can open a new error log while the engine continues running. Existing archive numbers then shift. The same startup marker can appear under a different archive number during a later collection. Therefore, counting files or archive numbers overcounts startup evidence.

Practice the collection rule with sample history

This SQL Server 2012 or later example uses made-up instances, timestamps and text. It reads no real error log. It shows archive renumbering, two instances, repeat collection and source separation. All the data stays in temporary tables. First, create the sample log rows and an empty history table.

DROP TABLE IF EXISTS #History;
DROP TABLE IF EXISTS #Log;
CREATE TABLE #Log(InstanceKey nvarchar(20) NOT NULL,CollectionId int NOT NULL,
 ArchiveNo int NOT NULL,Product int NOT NULL,LogLocalTime datetime2(3) NOT NULL,
 LogText nvarchar(200) NOT NULL);
INSERT #Log VALUES
 (N'Demo-A',1,2,1,'2026-09-01T08:00:00.000',N'Server process ID is 111.'),
 (N'Demo-A',1,1,1,'2026-09-01T09:00:00.000',N'The error log has been reinitialized.'),
 (N'Demo-A',1,0,1,'2026-09-01T10:00:00.000',N'Server process ID is 222.'),
 (N'Demo-A',2,1,1,'2026-09-01T10:00:00.000',N'Server process ID is 222.'),
 (N'Demo-A',2,0,1,'2026-09-01T11:00:00.000',N'The error log has been reinitialized.'),
 (N'Demo-A',2,0,1,'2026-09-01T11:05:00.000',N'DBCC trace note: Server process ID is 222.'),
 (N'Demo-A',2,0,2,'2026-09-01T11:10:00.000',N'Server process ID is 333.'),
 (N'Demo-B',1,0,1,'2026-09-01T10:00:00.000',N'Server process ID is 444.');
CREATE TABLE #History(InstanceKey nvarchar(20) NOT NULL,
 EvidenceLocalTime datetime2(3) NOT NULL,EvidenceSource varchar(20) NOT NULL,
 PRIMARY KEY(InstanceKey,EvidenceLocalTime,EvidenceSource));

Notice that the same startup line appears in collection 1 as archive 0 and in collection 2 as archive 1. Here is the collection step.

INSERT #History(InstanceKey,EvidenceLocalTime,EvidenceSource)
  SELECT DISTINCT l.InstanceKey,l.LogLocalTime,'Error log marker'
  FROM #Log AS l
  WHERE l.Product=1 AND LTRIM(l.LogText) LIKE N'Server process ID is %'
   AND NOT EXISTS(SELECT 1 FROM #History AS h WHERE h.InstanceKey=l.InstanceKey
    AND h.EvidenceLocalTime=l.LogLocalTime AND h.EvidenceSource='Error log marker');

This step reads #Log and fills #History. It filters a made-up English startup-prefix pattern for the engine product. A trace note mentioning that phrase later in its text does not qualify. Verify the actual message pattern for your engine version and log language.

Keep four evidence records distinct from four restarts

The result contains three error-log-marker records across two instances. A fourth row records the current DMV observation for Demo-A. Its timestamp differs from one marker by twenty milliseconds. That is separate source evidence, rather than proof of another restart.

Add the current DMV observation, then run the whole collection a second time. The history still holds four rows. Its instance, timestamp and source key prevent duplicate loads in this example. It does not prove concurrent production collectors are safe. Production retention and transaction design need their own review.

DECLARE @Pass int=1;
WHILE @Pass<=2
BEGIN
 INSERT #History(InstanceKey,EvidenceLocalTime,EvidenceSource)
 SELECT DISTINCT l.InstanceKey,l.LogLocalTime,'Error log marker'
 FROM #Log AS l
 WHERE l.Product=1 AND LTRIM(l.LogText) LIKE N'Server process ID is %'
  AND NOT EXISTS(SELECT 1 FROM #History AS h WHERE h.InstanceKey=l.InstanceKey
   AND h.EvidenceLocalTime=l.LogLocalTime AND h.EvidenceSource='Error log marker');
 INSERT #History(InstanceKey,EvidenceLocalTime,EvidenceSource)
 SELECT N'Demo-A',CONVERT(datetime2(3),'2026-09-01T10:00:00.020'),'Current DMV'
 WHERE NOT EXISTS(SELECT 1 FROM #History WHERE InstanceKey=N'Demo-A'
  AND EvidenceLocalTime='2026-09-01T10:00:00.020' AND EvidenceSource='Current DMV');
 SELECT @Pass AS Pass,COUNT(*) AS HistoryRows FROM #History;
 SET @Pass+=1;
END;
SELECT InstanceKey,EvidenceLocalTime,EvidenceSource
FROM #History ORDER BY InstanceKey,EvidenceLocalTime,EvidenceSource;

Timestamp gaps do not establish downtime or cause

The gap query uses LAG on error-log markers within each instance. It reports differences between local wall-clock timestamps. Clock adjustments and time-zone changes can affect that comparison. Do not label those differences as guaranteed physical elapsed seconds.

;WITH Markers AS
 (SELECT InstanceKey,EvidenceLocalTime,
  LAG(EvidenceLocalTime) OVER(PARTITION BY InstanceKey ORDER BY EvidenceLocalTime) AS PreviousMarker
  FROM #History WHERE EvidenceSource='Error log marker')
 SELECT InstanceKey,EvidenceLocalTime,PreviousMarker,
  DATEDIFF(second,PreviousMarker,EvidenceLocalTime) AS WallClockGapSeconds
 FROM Markers ORDER BY InstanceKey,EvidenceLocalTime;

DROP TABLE #History;
DROP TABLE #Log;

A startup observation also does not identify why the engine stopped. Retain surrounding operational evidence before diagnosing a crash, maintenance restart or service action. Missing old logs limit the history you can establish. Preserve evidence before treating the remaining files as a complete record.

A new error log is not a restart, it is only a new file.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.