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

SQL Server Startup

From time to time I see a question asking how to determine the last time a SQL Server was either restarted or when the last time was that the database was used.  For the first question there is a pretty common answer that works for SQL 2000 and up.  In answering both questions though, I am going to show you three quick similar scripts that can be used to find this information.  The catch is that these queries will not work on SQL 2000.  On the flipside, it should give you an idea of what can be done to find out the same info for SQL 2000.

TempDB

WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.create_date AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT create_date FROM sys.databases
					WHERE name = 'tempdb') CDate
	ORDER BY DatabaseName

A common answer to the question of when was SQL Server last started is to check the Create_Date of the tempdb database.  Since tempdb is recreated every time SQL Server is restarted, it is a pretty accurate timestamp of the server start.  This query was written to work for SQL 2005 and above, but you can still find the create_date of the tempdb database in SQL 2000 by using a different query.

sys.dm_exec_requests

WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.start_time AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT start_time FROM sys.dm_exec_requests
					WHERE session_id = 1) CDate
	ORDER BY DatabaseName

In this query we access one of those Dynamic Management views available in SQL 2005 and up.  In this case, we look at the first process id similar to what one may have done with sysprocesses in sql 2000.  Now is probably a good time to also note something that is happening inside the CTE.  Notice the COALESCE that is being used?  I am taking the Max from those fields that are associated with read type of activities in a database.  This information is being pulled from a DMV called sys.dm_index_usage_stats.  This query will get me the most recent activity for reads and writes in each database (notice the group by).

Default Trace

DECLARE @TraceFile VARCHAR(256)
 
SELECT @TraceFile = CONVERT(VARCHAR(256),VALUE) FROM [FN_TRACE_GETINFO](NULL)
WHERE [property] = 2;
 
;
WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.StartTime AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT StartTime
					FROM [FN_TRACE_GETTABLE](@TraceFile, DEFAULT)
					WHERE EventSequence = 1
						And IsSystem = 1) CDate
	ORDER BY DatabaseName

The last one is to make use of the default trace in SQL 2005, 2008 and 2008 R2.  The default trace records the time of certain events that occur in the database and that includes when the database is starting up.  To find the server startup time in this case, I am checking the EventSequence and IsSystem fields for a value of 1.  As you can see, I also dumped the filepath for the default trace file into a variable and use that in the function to get the data.

When comparing performance of these three options, they pretty much came out even.  Each took its turn performing faster – with regards to time.  However, the default trace method did return a more expensive execution plan every single time.  With how fast these queries run, I’m not real sure that that is very conclusive nor that it would be a heavy concern.  These queries are designed more for the occasional run by the DBA rather than to be executed millions of times a day.  You decide which will be best for you.  I think with the use of the function to get the default trace info, I was mildly surprised that the query performed that well.

Another thing to note is that each of these methods will return a slightly different timestamp.  For instance, the tempdb timestamp gives me 3:21:28 and the sys.dm_exec_requests produces a timestamp of 3:21:36 and the tracefile shows a timestamp of 3:21:24.  I don’t have any hard foundation for why that is – just speculation that seems to make sense.  I will just leave it at that though.

In addition to these methods there is also the option of checking the system event logs to determine the SQL Service startup time.  This information should help you when investigating your databases and server and would be something handy to keep in your toolbox.

Comments

Posted by Dukagjin Maloku on 23 March 2011

Jason, this is really a good post, including all SQL Server versions (2000/2005/2008/2008 R2)! I don't know if there is something more @ Denali!

Once again good job, m8!

Posted by mail.pnreddy on 25 March 2011

Good one!

Posted by Jason Brimhall on 25 March 2011

Thanks Dukagjin and Reddy

Posted by Justin Hostettler-Davies on 26 March 2011

Awesome - cheers Jason ! Very useful.

Posted by Jason Brimhall on 27 March 2011

Thanks Justin

Leave a Comment

Please register or log in to leave a comment.