Who, When, and Why restarted my SQL Server?

Updated: Dec 31, 2021

Sometimes, a database administrator needs to investigate the SQL Server outages. Suppose the outage is caused by a server restart. You need the following information for reporting purposes.

  • WHEN – When was my SQL Server restarted?

  • WHO – Who restarted the Server?

  • WHICH – Which process initiated the SQL Server restart?

  • WHY: Why the SQL Server was restarted?

Windows server event ID 1074 stores information restart of Server and can be viewed under ‘System’ tab of Windows logs.

Type eventvwr in the Start-> Run or search Event Viewer from the server start menu. Expand Windows Logs, click on the filter, and specify the event ID 1074.

Windows Event Viewer

Filter Event IDs

It filters the system event logs and provides the required information.

Filtered Windows Logs for SQL Server restart

For demonstration, let’s restart the Windows server and choose the reason -Application: Maintenance (Planned).

Computer restart Reason

Once the Server is up, filter the Windows logs for event ID 1074, and it gives the following results.

  • Shutdown type: Restart

  • When the Server was restarted: 2/30/2021 8:16:46 PM

  • What is the Reason for restart- Application: Maintenance (Planned)

  • Who initiated the restart: WindowsSQL\Administrator

  • Which process initiated the restart: Explorer.exe

View gathered data

We can use PowerShell script to filter event id 1074 as well. Launch PowerShell ISE from the start menu and type the following command.

gwmi win32_ntlogevent -filter "LogFile='System' and EventCode='1074'" |
select User,@{n="Time";e={$_.ConvertToDateTime($_.TimeGenerated)}},message |  Out-GridView

It opens another grid window with the results. You can filter records in the GRID view as well.

PowerShell script to find SQL Server restart

PowerShell output Grid View

33 views0 comments