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

Last restart of SQL Server

Occasionally I need to see what the date of last restart of SQL Server. The old method isto check the creation date of tempdb database. However, in SQL 2008 we have a DMV that can be used for this purpose: sys.dm_os_sys_info. Here's how:

 

1) Searching creation of tempdb

SELECT
    d.create_date,
    DAY(GETDATE()) - DAY(d.create_date) DaysUpTime
FROM sys.databases d
WHERE d.name like 'tempdb'

2) Using the DMV sys.dm_os_sys_info
select
    i.sqlserver_start_time,
    DAY(GETDATE()) - DAY(i.sqlserver_start_time) DaysUpTime
from sys.dm_os_sys_info i;

Comments

Posted by Greg J on 18 November 2013

I had never seen/used the sys.[dm_os_sys_info] before, so thanks for that Adeilson.

I have one issue with your script though... My last SQL Server restart was on Oct 4.  Today is Nov 18.  Your script tells me that SQL Server has been up for 14 days (DaysUpTime), when it's actually been up for 45 days.

By using the DAY() function, your DaysUpTime column will only show the correct value if the last SQL Server restart was in the month you're running the command.  

Here's the code I'll end up going live with for DaysUpTime.  Again, thank you for pointing me in the right direction Adeilson!

DATEDIFF(dd, i.sqlserver_start_time, GETDATE()) DaysUpTime

Leave a Comment

Please register or log in to leave a comment.