Subscribe to this blog
Briefcase
Print
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:
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 !