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 ...
Best Answer
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.....
@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.
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.
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.
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.
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.
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/
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 :)
you can get instance startime by simply quering tempdb creation time.
