Home » Blog » Determine the Uptime of a SQL Server Instance

Determine the Uptime of a SQL Server Instance

2010-12-17 - General

Every once in a while it is helpful to know the time that has passed since a SQL Server instance was last restarted. This information is particularly helpful when dealing with accumulating dynamic management views like sys.dm_db_index_usage_stats.

If you do a search in your favorite search engine, you come across four different ways to get to that information:

SELECT DATEADD(ms,-sample_ms,GETDATE() )AS StartTime

FROM sys.dm_io_virtual_file_stats(1,1);

SELECT create_date AS StartTime

FROM sys.databases

WHERE name = 'tempdb';

SELECT login_time AS StartTime

FROM sys.dm_exec_sessions

WHERE session_id =1;

EXEC xp_readerrorlog;

So let us examine them a little closer:

According to BOL the column sample_ms in sys.dm_io_virtual_file_stats contains the “Number of milliseconds since the computer was started.” That means it does tell us how long ago the computer running SQL Server was restarted. It does however not tell us, if SQL Server was recycled since then.

That means the information returned by sys.dm_io_virtual_file_stats does not give us a reliable way to determine the SQL Server instance uptime.

BOL says: “tempdbis re-created each time the instance of SQL Server is started”. It also states that you can’t drop, restore, take offline or remove tempdb in any other way from a running system. So the create_date returned from sys.databases for tempdb should get us close to the time we are looking for. However, creating tempdb is not the first step SQL Server executes when starting up. Most other databases get initialized first. Depending on the amount of recovery work that needs to be done, there can be a significant difference between the instance start time and the creation time of tempdb.

Moving on: SQL Server has several system processes running. We all have heard of SPID < 50 means it is a system process. However, as many recently discovered, this undocumented feature you cannot rely on. SQL Server is free to use any SPID for system processes. While it is not likely to change, I recommend to never base production code on undocumented features.

Also, in my experience, the start time returned by sys.dm_exec_sessions is most of the time later than the start time returned by sys.databases. So, this one is out too.

That leaves us with xp_readerrorlog. BOL tells us that “A new error log is created each time an instance of SQL Server is started”. That is great news. If you look at the output of this procedure the first few lines look similar to this:

LogDate	ProcessInfo	Text
2010-12-17 14:46:01.570	spid53	Microsoft SQL Server 2008 (SP1) …
2010-12-17 14:46:01.570	spid53	(c) 2005 Microsoft Corporation.
2010-12-17 14:46:01.580	spid53	All rights reserved.
2010-12-17 14:46:01.580	spid53	Server process ID is 7260.

Those entries where created when the error log file was created. Creating this file is one of the first steps SQL Server executes, so this gives us the closest estimate of our server start time yet.

But the above quoted sentence about the error log in BOL continues on to tell us that “the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server”. After that procedure executes, a new log file is created and the information shown above is written into it, only with a new time stamp. There is no simple way to detect that this happened and then go and find the old log file that contains the real instance start time if it even still exists. That together with the fact that xp_readerrorlog is also an undocumented procedure makes it not very helpful for our purposes.

So in the end the closest we can get to the start time of the SQL Server instance is the creation date of the tempdb database. While it is not a 100% accurate reflection of the real instance start time, it is a reliable way to get fairly close to it. Considering that not really a lot happens on a SQL Server instance until all databases, including tempdb, are initialized, it is probably the better time to use anyway in most practical applications.

UPDATE:

After talking to my friend Kalen Delaney, I have to post an update to this blog post. Starting with SQL Server 2008, there actually is a reliable way to get the exact SQL Server instance start time. It is documented and the time returned is actually even a little earlier than the time you can find in the error log. That means it is the real start time of the SQL Server service. So I have to change my recommendation: if you are looking for the start time of your SQL Server instance use this query:

SELECT sqlserver_start_time

FROM sys.dm_os_sys_info;