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;

Categories: General

4 comments
@databoffin
@databoffin

I was looking for this for the very reason you describe above, to work out how relevant the index usage stats are. We can get the start time of SQL Server but databases can be dropped and created afterwards and so can tables. So we can get the start time from tempdb for the instance or sys.dm_os_sys_infobut we’ll also need to check if the creation date of the database is newer and then check if the creation date of the table in question is newer than that date.

I’m also interested in a solution for AzureDB but we in this instance, sys.dm_os_sys_info is unavailable and so is tempdb but maybe the database creation date will be enough?

--Get the most recent start time of the instance \ database

SELECTMAX(db.create_date)

FROMsys.databases db

WHEREdb.name = 'tempdb'

OR db.database_id = DB_ID();

--Check if the table was created after this date

SELECTt.create_date

FROMsys.tables t

WHEREt.name = 'TableName';

@sqlity
@sqlity moderator

@@databoffin  You are right, the database and or table could have been created after the instance was restarted. So, looking at their creation dates is a good idea. I am not sure what to do when you are on Azure. 

Nitin Sontakke
Nitin Sontakke

Hello,

I happen to come over here when I googled.

But I am finding something strange (to me, of course!) on my machine. SQL Server Express 2012 is installed on a local box and I almost every day shut-down the machine when going home.

However, the last query (SELECT sqlserver_start_time FROMsys.dm_os_sys_info;) showing the date and time from about a month ago!

What could be the reason?


@sqlity
@sqlity moderator

@Nitin Sontakke, that is odd. I would say, make sure you are actually connecting to the machine you think you are connecting to. if you are indeed sure, that might be a bog in the express edition. Make sure you have installed all updates, and then try again.