SQL Clone
SQLServerCentral is supported by Redgate
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

    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
    DAY(GETDATE()) - DAY(i.sqlserver_start_time) DaysUpTime
from sys.dm_os_sys_info i;


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.