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

Code block

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

Code block

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

Code block

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.