Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

Quick Tip Of The Day

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'

Comments

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.

Leave a Comment

Please register or log in to leave a comment.