Toolbox - How Long Has My Server Been Up?

https://imgflip.com/i/2axp6y

No..not that Up (although it is awesome!)

--

As a remote service provider a common request is to follow up after a server restart (or investigate a possible server restart).  A question that inevitably comes up is "When did the server restart?"

If all you need to know is when the SQL Server service restarted, the easiest thing is the creation date of tempdb since it is recreated as part of SQL Server start-up:

--

SELECT name, create_date
FROM master.sys.databases
WHERE name = 'tempdb'

--

name	create_date
tempdb	05/14/2018 07:31:18

--

The catch is that the SQL service restart often isn't good enough.

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

--

Instance_Name	Current_Server_Time	OS_Start_Time	SQL_Server_Start_Time	SQL_Agent_Start_Time
INSTANCE999	05/24/2018 19:59:48	05/14/2018 07:30:31	05/14/2018 07:31:14	05/14/2018 07:31:24

OS_Uptime	SQL_Uptime	Agent_Uptime
10 days 12 hours, 29 minutes, 17 seconds	10 days 12 hours, 28 minutes, 34 seconds	10 days 12 hours, 28 minutes, 23 seconds

--


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:"

--

output

System Boot Time:          4/21/2018, 7:41:10 AM

--

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:

--

name	create_date
tempdb	04/21/2018 07:42:44

Instance_Name	OS_Start_Time	SQL_Server_Start_Time	SQL_Agent_Start_Time
INSTANCE999	04/21/2018 07:42:17	04/21/2018 07:42:31	04/21/2018 07:42:47

output

System Boot Time:          4/21/2018, 7:41:10 AM

--

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

Sometimes you only have access to one datetime or another - especially systeminfo - for example you may be a sysadmin in SQL but not have access to run the Windows command line function - but if you have access to all of these numbers, consider the order of items above.

--

Hope this helps!