Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

When was SQL Server last restarted?

How do you tell when your SQL instance was lasted restarted?

I was reading a post by Joe Webb (Blog|Twitter) about a query he uses to to gather information about his clients SQL Server instance including how he find out when an instance of SQL Server was last restarted.

You can find Joe’s script and his latest post here.

He basically queries the sys.dm_exec_sessions to find the login time of the first session.

As Joe mentions in his post there a number of ways to get this information. I use a slightly different method where I query sys.databases to get the create time of tempdb…Tempdb db is recreated each time SQL Server is restarted so the created date time of TEMPDB will also give you the date and time of the last restart of the instance. My query looks like this:

SELECT  create_date
FROM sys.databases
WHERE database_id = 2 --TEMPDB's database_id


which produces the following result:



LastRestart

Comments

Posted by Dukagjin Maloku on 29 April 2010

Nice tip'n'trick!

Posted by Anonymous on 29 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, When was SQL Server last restarted? - The SQL DBA in the UK         [sqlservercentral.com]        on Topsy.com

Posted by vj.sqldba on 30 April 2010

Good work man.

Posted by Steve Jones on 30 April 2010

That is a nice trick. I've typically grabbed the first entry in the error log, but this is easier to query.

DECLARE  @logger TABLE (dt datetime, Process varchar(50), txt varchar(MAX))

INSERT @logger exec xp_readerrorlog

SELECT TOP 1 dt FROM @logger

works. Sounds like I need a blog post.

Posted by Gethyn Ellis on 30 April 2010

Steve, I like your solution but what happens if you cycle the error log periodontally with

EXEC sp_cycle_errorlog ;

GO

I cycle mine once a month via and agent job, to make them more readable and not let me get to large...There are arguments for not doing this but I like the fact the current error log doens't get too big and opens pretty quickly. anyway if I do this then the first error in the current log doesn't necessarily return the start/restart time of the instance.

Posted by Anonymous on 1 May 2010

Pingback from  Download Software  » Blog Archive  » Download Software | Unicenter SQL-Station 6.0.0 for MS SQL-Server

Posted by david.howell on 2 May 2010

Thanks for the laugh:

"cycle the error log periodontally"

I'm picturing you pulling the power cord out with your teeth.

Posted by ankur.hooda on 3 May 2010

try sp_helpdb and see creation date of tempdb .

Posted by Anonymous on 7 May 2010

Pingback from  Weekly Movie News Round-Up: May 3-7 – ChicagoNow (blog)

Posted by Anonymous on 7 May 2010

Pingback from  Stargazing | ‘Prince of Persia’ pleases; why Heidi wants more surgery – Kansas City Star

Posted by Justin Hostettler-Davies on 10 May 2010

Great little tip - cheers !

Leave a Comment

Please register or log in to leave a comment.