Checking when the SQL Instance started

There are several ways to find out, or some make an estimate at, when a SQL Instance started. Can anyone say how accurate this query would be?

 SELECT  MIN(start_time) as [Instance Started]
 FROM    [sys].[dm_exec_requests]

On all my instances it seem to be pretty accurate but I'm wondering if there is any reason not to use this? It seems a lot simpler than checking the create date of TempDB and various other ways I know of ...

more ▼

asked Nov 23, 2011 at 05:56 AM in Default

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Only for SQL 2008 an above, but this would be better:

 SELECT  sqlserver_start_time
 FROM    sys.dm_os_sys_info


I'm not sure how accurate the sys.dm_exec_requests version would be, what happens to that DMV if you drop clean buffers?

EDIT 2: Drop clean buffers doesn't change the entries of sys.dm_exec_requests , so that is my own myth busted! :)

p.s. don't test this assumption on a prod box. I didn't..... honest.....

more ▼

answered Nov 23, 2011 at 06:03 AM

avatar image

26.2k 18 38 48

ooh, new things :) +1

Nov 23, 2011 at 06:04 AM Fatherjack ♦♦

@Fatherjack seems to have found a possible bug (if you can class it as that). His query / the infromation provided by sys.dm_exec_requests doesn't account for daylight savings changes.

If your SQL server was running before the time change and you run his query and compare it to my solution, the times are off by 60 minutes. The sys.dm_os_sys_info DMV displays the information correctly though.

Nov 24, 2011 at 01:50 AM WilliamD

I'm not so sure that dm_os_sys_info is right. When compared with my server system event log the time that the dmv reports is an hour BEFORE the server actually started.

Nov 24, 2011 at 02:36 AM Fatherjack ♦♦

well, they agree in pairs - dm_exec_requests and the system event log and then dm_os_sys_info and the command prompt query NET STATISTICS WORKSTATION. Connect item opened https://connect.microsoft.com/SQLServer/feedback/details/708164/dm-exec-requests-data-conflicts-with-dm-os-sys-info , also blogging on it.

Nov 24, 2011 at 04:34 AM Fatherjack ♦♦

Just to update everyone: The Connect item has been noted by Microsoft and will be resolved in a future update/version. Thanks for your input.

Dec 10, 2011 at 03:28 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I think it'll probably work. I'd call it an undocumented artifact and I wouldn't want to base major assumptions on it that affect my pay check or something like that, but yeah, I think it'll work.

more ▼

answered Nov 23, 2011 at 10:43 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left

I have compiled what is mentioned in this question and in this blog and run it on my machine:

 SELECT create_date FROM sys.databases WHERE name = 'tempdb'
 select start_time from sys.traces
 where is_default = 1
 SELECT login_time FROM sys.dm_exec_sessions
 WHERE session_id = 1;
 SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
 EXEC sp_readerrorlog 0,1,'Copyright (c)'
 SELECT  MIN(start_time) as [Instance Started]
 FROM    [sys].[dm_exec_requests]



All give a slightly different result. I usually use the first and the last one.

more ▼

answered Dec 10, 2011 at 11:47 AM

avatar image

4.9k 33 39 43

(comments are locked)
10|1200 characters needed characters left

Another way... and it won't work for everyone as some people use daily error logs etc.

 EXEC master.dbo.xp_readerrorlog 0, 1, 'Recovery is complete.', NULL, NULL, NULL, N'asc'

This basically returns when the system was ready.

Not a solution I would go with to base everything on but it does show that there are always alternatives :)

more ▼

answered Nov 24, 2011 at 07:20 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left

you can get instance startime by simply quering tempdb creation time.

more ▼

answered Dec 09, 2011 at 08:44 PM

avatar image

213 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 23, 2011 at 05:56 AM

Seen: 1331 times

Last Updated: Nov 23, 2011 at 05:56 AM

Copyright 2018 Redgate Software. Privacy Policy