SQL Server: Find Windows and SQL Server Last Restart Time

Article
1/17/2024

Introduction

Being a DBA can be really a thankless job, when everything works fine no one bothers about DBA's but when things go wrong every one tries to pass the blame on DBA. One of the ideal case is when SQL Server restarts, sysadmin & DBA starts blaming each other for the restart.
Below are few ways you can check when was SQL Server restarted last time and why it was restarted.

How to Identify Who or What is Restarting SQL services via Service Control?

If you have the default trace running,The default trace is lightweight and among other things does track Server Stop and Server Start.

Different ways to find SQL Server Last Restart Time

Method 1--> errorlog

sp_readerrorlog 0,1,'Copyright (c)'

Method 2--> tempdb creation

SELECT create_date FROM sys.databases WHERE name  = 'tempdb'

create_date
———————–
2013-11-26 01:16:13.023
 
(1 row(s) affected)

Method 3--> sqlserver start time in sys.dm_os_sys_info

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

sqlserver_start_time
———————–
2013-11-26 01:16:10.757
 
(1 row(s) affected)

Method 4--> session_id from sys.dm_exec_sessions

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

login_time
———————–
2013-11-26 01:16:24.127
 
(1 row(s) affected)

Method 5--> Dashbord

Right click on the server name in SSMS and select Reports > Standard Reports > Server Dashboard and you will get a report similar to below report.

Method 6--> Event viewer

start -> run -> eventvwr
Chose Windows log -> Application
Open filter and enter Event Sources (MSSQLSERVER) and** Event Id** (17163)

Here is the output of above filter.

Different ways to find Windows last rebooted time.

Method 1--> Task Manager

Start Task Manager->Performance
Screen below gives you total up time since last restart.

Method 2 -->Eventviewer

This method helps you to figure out root cause for the restart.
start -> run -> eventvwr
Choose: Windows Log – > System
Open filter and following** Event Id’s**
6005 to see when the Event Log service was started. It gives the message "The Event log service was started".
6006 to see when there was a clean shutdown. It gives the message "The Event log service was stopped".
6008 to see when there was a dirty shutdown. It gives the message "The previous system shutdown at time on date was unexpected".
6009 is logged during every boot.

Here is the output of above filter.

you can check using powershell query

get-eventlog System | where-object {$_.EventID -eq "6005"} | sort -desc TimeGenerated

Method 3--> powershell

Method 4--> WMI client:

Using a wmi client.

C:\>wmic OS GET CSName,LastBootUpTime
CSName    LastBootUpTime

Share via