Pages

Tuesday, February 22, 2011

SQL Server Start Time

Have you tried finding out the time, your SQL Server instance has been started? There are some sophisticated solutions, like the one from Tracy Hamlin (twitter), which takes advantage of the fact that tempdb is re-created every time, SQL Server starts. Her solution goes like this:

select create_date
 
from sys.databases where database_id=2

Another answer to the question, I’ve seen sometimes on the internet queries the login time for any of the system processes:

select login_time
 
from sys.dm_exec_sessions where session_id=1

This was my preferred way – until yesterday, when I discovered the following simple method:

select sqlserver_start_time
 
from sys.
dm_os_sys_info

Easy, isn’t it? Interestingly though, every of the above three queries yields a different result. Here’s a query with a sample output:

select (select sqlserver_start_time
         
from sys.dm_os_sys_info) as
sql_server_start_time
     
,(select
create_date
         
from sys.databases where database_id=2) as
tempdb_creation_time
     
,(select
login_time
         
from sys.dm_exec_sessions where session_id=1) as sysprocess_login_time

Result:

image

It seems the SQL Server service must be started first. Only after the service is running, tempdb is created followed by a subsequent start of all sysprocesses. I can’t imagine that the diverse three times make any difference in practice, e.g. if you try finding out for how many hours your SQL Server instance is running. But out there may be existing applications that have to be aware of the difference.

2 comments:

  1. The column "sqlserver_start_time" did not exist in sys.dm_os_sys_info until SQL Server 2008 and requires VIEW SERVER STATE permission on the server.
    If you are using SQL Server 2005 and have all the needed permissions, you can always calculate the machine start time (before the SQL Server service was started):

    SELECT DATEADD(ms,ms_ticks*-1,GETDATE())
    from sys.dm_os_sys_info

    obviously, if the service have been restarted since the machine was started this will be of no use... :)

    ReplyDelete
  2. Thanks Justicator. Good point regarding the VIEW SERVER STATE permission! I totally overlooked this.
    As for the ms_ticks column, BOL indeed says: Number of milliseconds since the *computer* was started. So it's not the time SQL Server wa started, obviously.
    Thanks.

    ReplyDelete

Followers