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 


Other Resources:

Windows server last reboot time
Find Last Restart of SQL Server
How to Identify Who or What is Restarting SQL services via Service Control?