Thursday, 29 April 2010

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