|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Friday, June 14, 2013 3:11 PM
Points: 338,
Visits: 993
|
|
| 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)?
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Thursday, June 06, 2013 3:00 PM
Points: 939,
Visits: 1,713
|
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 2,969,
Visits: 10,615
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, May 24, 2013 2:53 AM
Points: 111,
Visits: 517
|
|
you can get the time by running this query also
Select name,crdate from sys.sysdatabases where name='tempdb'
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 38,071,
Visits: 30,365
|
|
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 2008, MVP 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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:09 AM
Points: 155,
Visits: 1,301
|
|
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. 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
|
|
|
|