SQL Server Restart Date and Time

Problem:

I received a request from one of the developer saying that, the Staging DB was down yesterday evening, between 6:00 PM to 8:00 PM.

Due to this, he was not able to meet his deadline.

Solution:

Since it was staging server, we do not configure automate mail alert for service restart.

So, to answer such question, I have checked below things.

1.      Last SQL Server instance restart.

2.      Last DB server (windows services) restart.

HOW TO CHECK ?

 SQL Server Restart:

We can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

SELECT 'Statistics since: ' + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info

OR

EXEC SP_HELPDB 'Tempdb'

 DB Server (Windows Services) Restart:

Option 1-

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.

  

Option(CMD) 2-

Run the following from a command prompt:

 systeminfo | find “Time:”

OR

Run the following from a command prompt:

net statistics server