When did Windows reboot?

When did the Agent service start?

Why did SQL start before the tempdb create date?

...and more.

I went digging and found an answer I liked online - as usual I modified it for my own wishes while keeping the core of the code:

--

/*
Item Uptime Query
Author Andy Galbraith
Created 2018/01/03
Updated 2018/03/05
Decription Returns uptime for Windows, SQL Server, and SQL Agent
Versions SQL 2005+
Notes In some cases the SQL Server start time may show earlier than the OS start time - this is due
to the particular flags used to show that the system is "up" and is expected.
*/

/*
Queries modified from query in comment at:
https://www.sqlservercentral.com/Forums/Topic1384287-391-1.aspx
*/

SELECT
@@SERVERNAME as Instance_Name
, GETDATE() as Current_Server_Time
, CONVERT(VARCHAR(23),b.OS_Start,121) as OS_Start_Time
, z.SQL_Start as SQL_Server_Start_Time
, CONVERT(VARCHAR(23),a.Agent_Start,121) as SQL_Agent_Start_Time
, CONVERT(VARCHAR(50),
CAST(CAST(right(10000000+datediff(dd,0,GETDATE()-b.OS_Start),4) as INT) as VARCHAR(4))+' days '+
CAST(DATEPART(hh,GETDATE()-b.OS_Start) as VARCHAR) + ' hours, ' +
CAST(DATEPART(mi,GETDATE()-b.OS_Start) as VARCHAR) + ' minutes, ' +
CAST(DATEPART(ss,GETDATE()-b.OS_Start) as VARCHAR) + ' seconds') as OS_Uptime
, CONVERT(VARCHAR(50),
CAST(CAST(right(10000000+datediff(dd,0,GETDATE()-z.SQL_Start),4) as INT) as VARCHAR(4))+' days '+
CAST(DATEPART(hh,GETDATE()-z.SQL_Start) as VARCHAR) + ' hours, ' +
CAST(DATEPART(mi,GETDATE()-z.SQL_Start) as VARCHAR) + ' minutes, ' +
CAST(DATEPART(ss,GETDATE()-z.SQL_Start) as VARCHAR) + ' seconds') as SQL_Uptime
, CONVERT(VARCHAR(50),
CAST(CAST(right(10000000+datediff(dd,0,GETDATE()-a.Agent_Start),4) as INT) as VARCHAR(4))+' days '+
CAST(DATEPART(hh,GETDATE()-a.Agent_Start) as VARCHAR) + ' hours, ' +
CAST(DATEPART(mi,GETDATE()-a.Agent_Start) as VARCHAR) + ' minutes, ' +
CAST(DATEPART(ss,GETDATE()-a.Agent_Start) as VARCHAR) + ' seconds') as Agent_Uptime
FROM
(
SELECT login_time as SQL_Start
FROM sys.dm_exec_sessions WHERE session_id = 1
) z
CROSS JOIN
(
SELECT
NULLIF(min(
case
when aa.program_name like 'SQLAgent %'
then aa.login_time
ELSE '99990101'
end),
CONVERT(datetime,'99990101')) as Agent_Start
FROM master.dbo.sysprocesses aa
WHERE aa.login_time > '20000101'
) a
CROSS JOIN
(
SELECT
DATEADD(ss,bb.[ms_ticks]/-1000,GETDATE()) as OS_Start
FROM sys.[dm_os_sys_info] bb
) b

--

As you can see, it leverages sys.dm_exec_sessions and sysprocesses to pull the start times for the SQL services and sys.dm_os_sys_info to pull the OS info.

--

For another option, we can leverage an operating system function, the systeminfo command line tool,

You can use a pipe | to pass a find command into systeminfo, like this:

--

systeminfo|find "Time:"

--

--

To run it, you can run an elevated CMD prompt from windows (right-click and Run as Administrator), or if xp_cmdshell is enabled you can run it from SQL:

--

EXEC xp_cmdshell 'systeminfo|find "Time:"'

--

So we have seen three ways to pull the info - but which one is the most "right"?

Let's compare the results for concurrent runs of the three queries:

--

There are times when you need to know pretty specifically what happened and when - so let's review:

1. systeminfo time - 7:41:10AM
2. sys.dm_os_sys_info ms_ticks - 7:42:17AM - more than a minute later!
3. sys.dm_exec_sessions SQL start - 7:41:32AM
4. tempdb create date - 7:42:44AM
5. sysprocesses Agent start - 7:42:47AM

--

Hope this helps!