x

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

EDIT:

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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

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

I have compiled what is mentioned in this question and [in this blog][1] 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]

and

NET STATISTICS SERVER

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

[1]: http://www.sqldbadiaries.com/2011/03/07/when-was-sql-server-restarted/
more ▼

answered Dec 10, 2011 at 11:47 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(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

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(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

salum gravatar image

salum
213 1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x344
x126

asked: Nov 23, 2011 at 05:56 AM

Seen: 1110 times

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