When was server last rebooted?

Question

When was server last rebooted?

dan-572483

SSCoach

Points: 15870
More actions
November 13, 2012 at 1:56 pm

#284711

Is there a DMV or function that tells you when the physical server was last rebooted, as opposed to when SQL service was last restarted (which is in sys.dm_os_sys_info)?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

For the physical server you can run "net statistics server" and take a look at the time.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 2

edit; for reference for others , he mentioned he already knows this one, as opposed to when the OS restarted:

select

sqlserver_start_time

from sys.dm_os_sys_info

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This code gives the start time for the OS, SQL Server, and SQL Server Agent, and gives the uptime days for the OS, SQL Server, and SQL Server Agent.

select

[OS Start Time]= convert(varchar(23),b.OS_Start,121),

[SQL Server Start Time]= convert(varchar(23),a.SQL_Start,121),

[SQL Agent Start Time]= convert(varchar(23),a.Agent_Start,121),

[OS Uptime] =

convert(varchar(15),

right(10000000+datediff(dd,0,getdate()-b.OS_Start),4)+' '+

convert(varchar(20),getdate()-b.OS_Start,108)),

[SQL Uptime] =

convert(varchar(15),

right(10000000+datediff(dd,0,getdate()-a.SQL_Start),4)+' '+

convert(varchar(20),getdate()-a.SQL_Start,108)) ,

[Agent Uptime] =

convert(varchar(15),

right(10000000+datediff(dd,0,getdate()-a.Agent_Start),4)+' '+

convert(varchar(20),getdate()-a.Agent_Start,108))

from

(

Select

SQL_Start = min(aa.login_time),

Agent_Start =

nullif(min(case when aa.program_name like 'SQLAgent %' then aa.login_time else '99990101' end),

convert(datetime,'99990101'))

from

master.dbo.sysprocesses aa

where

aa.login_time > '20000101'

) a

cross join

(

select

OS_Start = dateadd(ss,bb.[ms_ticks]/-1000,getdate())

from

sys.[dm_os_sys_info] bb

) b

Results:

OS Start Time SQL Server Start Time SQL Agent Start Time OS Uptime SQL Uptime Agent Uptime

----------------------- ----------------------- ----------------------- --------------- --------------- ---------------

2012-08-04 22:19:26.317 2012-08-04 23:38:07.163 2012-10-31 11:49:49.063 0100 18:28:38 0100 17:09:57 0013 04:58:15

yuvipoy SSChampion Points: 10469 More actions · Answer 4

you can get the time by running this query also

Select name,crdate from sys.sysdatabases where name='tempdb'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

yuvipoy (11/14/2012)
you can get the time by running this query also
Select name,crdate from sys.sysdatabases where name='tempdb'

That gets you the time the SQL instance was last started, not the time the server was last rebooted.

sysdatabases is deprecated, should not be used, included only for backward compat with SQL 2000, will be removed in a future version. The replacement is sys.databases

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLCharger SSCommitted Points: 1928 More actions · Answer 6

Michael Valentine Jones (11/13/2012)
This code gives the start time for the OS, SQL Server, and SQL Server Agent, and gives the uptime days for the OS, SQL Server, and SQL Server Agent.
....

Michael,

Pretty neat query this one - hadn't thought to get OS start time from dm_os_sys_info ticks.:cool:

Since it returns just one row, it is also handy for registred-server batch queries. After a powercut, you can tell immediately which servers had Windows interrupted and which one just SQL services.

Cheers,

JohnA

MCM: SQL2008