As a DBA seldom we see cases where a User reports “Hey Mr DBA, my application went down for a minute or even few minutes, Now everything appears to be Okay. Can you pls take a look what happened?” Well, our very First place should be SQL Server Error Logs( My Personal favorite to start troubleshooting any issue) and let’s assume in this case that our Instance has been restarted and we see it in our Error Logs. So this is the traditional way to find out regarding SQL Server restarts – The SQL Server Error Logs.
Let’s jump into another scenario where you want to know when was the SQL Server restarted. It would be little annoying to cycle through all the error logs in SSMS if you are recycling your Logs on a daily basis and maintaining multiple Error Logs(PS we can have upto 99 Archives of Error Logs listed in our SSMS). So what would be our easiest bet to figure out when was the Instance restarted??
Method 1:
TempDB Creation date:
Since we all know Tempdb will be created each time you restart SQL Server, Tempdb creation date will give us the answer.
Alternate Methods:
1st Session(Session_id = 1) creation time and by starting SQL Server 2008, we can query DMV sys.dm_os_sys_info.
FYI Please see the below Screenshot
Note: Notice a slight difference in Milli seconds( could be seconds) in tempdb creation time and the time when SQL created it’s first session. But that’s Okay! Choose your Best method. I use tempdb creation date(since I’m used to it since SQL 2005). May be I’ve to start using DMV on new boxes!!
Hope this helps!
MSSQLTREK 