Toolbox – How Long Has My Server Been Up?

Andy Galbraith, 2018-05-24

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:

@@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
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
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
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
SELECT login_time as SQL_Start
FROM sys.dm_exec_sessions WHERE session_id = 1
) z
when aa.program_name like ‘SQLAgent %’
then aa.login_time
ELSE ‘99990101’
CONVERT(datetime,’99990101′)) as Agent_Start
FROM master.dbo.sysprocesses aa
WHERE aa.login_time > ‘20000101’
) a
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:”

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
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!





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads