Subscribe to this blog
Briefcase
Print
Over and over again we are told that the DMV’s only hold data since your last reboot. So, how do you know when your server was last rebooted? Well, every time your SQL Server service restarts tempdb is recreated every time. With a quick query to sys.databases we can get the creation date of tempdb! Armed with that little nugget you can then analyze what is in the DMV’s relevant to the last system restart.
SELECT
create_date AS last_restart_time
FROM
sys.databases
WHERE name = 'tempdb'
Posted by Jason Brimhall on 16 February 2010
nice tip.
Posted by Dugi on 17 February 2010
yep not bad...very very simple and useful tip!
Posted by Nicholas Cain on 18 February 2010
You can also query a DMV directly
select sqlserver_start_time from sys.dm_os_sys_info
Posted by Wesley Brown on 18 February 2010
Nicholas,
Doesn't work on 2005, that column is new to 2008. Should have mentioned that though!
Thanks!
Posted by Amol.Naik on 18 February 2010
xp_readerrorlog can also be used.
Posted by Spencer Evans on 19 February 2010
xp_readerrorlog won't give the correct result if you regularly cycle your error logs (EXEC sp_cycle_errorlog)
Posted by dcameron on 19 February 2010
The following script will work on 2000/2005:
SELECT crdate AS last_restart_time
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
Posted by al_kessler on 19 February 2010
This also works for 2005
select login_time
from master..sysprocesses
where lastwaittype = 'SQLTRACE_BUFFER_FLUSH'
The time result for this method is about 22 seconds later than the other examples on one server and about 3 seconds later on another. Could be the time to complete some tasks prior to buffer flush.
Posted by Wesley Brown on 19 February 2010
I love when everyone jumps in with other suggestions!
select login_time
from master..sysprocesses
where lastwaittype = 'SQLTRACE_BUFFER_FLUSH'
how about just picking spid 1?
Posted by cherish.j.wang on 21 February 2010
summarize:
1.
select create_date AS last_restart_time
from master.sys.databases
where name = 'tempdb'
2.
SELECT crdate AS last_restart_time
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
3.
select login_time AS last_restart_time
from master.sys.sysprocesses
where lastwaittype = 'SQLTRACE_BUFFER_FLUSH'
4.
--if the SQL server service start automatically
select DATEADD(ms,-ms_ticks,GETDATE()) AS last_restart_time
from sys.dm_os_sys_info
5.
--if errorlog did not truncate
declare @T TABLE
(
LogDate DATETIME
,ProcessInfo NVARCHAR(100)
,Text NVARCHAR(max)
)
INSERT INTO @T
EXEC sys.sp_readerrorlog 0
SELECT MIN(LogDate) AS last_restart_time
FROM @T
Posted by mtillman on 19 April 2011
Very good - thank you. 'Was looking for this.