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.
It filters the system event logs and provides the required information.
For demonstration, let’s restart the Windows server and choose the reason -Application: Maintenance (Planned).
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
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-GridViewIt opens another grid window with the results. You can filter records in the GRID view as well.
