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?
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 ...
(comments are locked)
|
Only for SQL 2008 an above, but this would be better:
EDIT: I'm not sure how accurate the EDIT 2: Drop clean buffers doesn't change the entries of p.s. don't test this assumption on a prod box. I didn't..... honest..... 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 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
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)
|
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.
(comments are locked)
|
I have compiled what is mentioned in this question and in this blog and run it on my machine:
and
All give a slightly different result. I usually use the first and the last one.
(comments are locked)
|
Another way... and it won't work for everyone as some people use daily error logs etc.
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 :)
(comments are locked)
|
you can get instance startime by simply quering tempdb creation time.
(comments are locked)
|