September 24, 2007 at 6:22 pm
Comments posted to this topic are about the item Server Up-Time Information
November 5, 2007 at 3:11 pm
Nice query!! But the server start time is not coming out correct as you are subtracting all the days, and then all the hours and all the minutes. Just subtracting the total minutes of uptime from the current time should show the right date and time the server was last started.
WITH ServerUpTimeInfo AS (
SELECT (dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00
AS server_up_time_min,
((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00
AS server_up_time_hr,
(((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00) / 24.00
AS server_up_time_day
FROM sys.dm_io_virtual_file_stats(1,1) AS dm_io_virtual_file_stats )
SELECT CAST(server_up_time_min AS decimal(12,2)) AS server_up_time_min,
CAST(server_up_time_hr AS decimal(12,2)) AS server_up_time_hr,
CAST(server_up_time_day AS decimal(12,2)) AS server_up_time_day,
CAST(DATEADD(mi,
-ROUND(server_up_time_min, -1), getutcdate()) as smalldatetime
) as approx_server_start_utc_datetime
FROM ServerUpTimeInfo;
November 15, 2007 at 8:02 am
All my installations seems to have negative values in the sample_ms column. Seems to be a bug or an overflow .
April 11, 2008 at 4:02 pm
There's a much simpler way to do this:
select ServerUptimeInHrs = datediff(hh, Login_time, current_timestamp)
from sys.dm_exec_sessions
where session_id = 1
That gives you the hours, obviously days, minutes or whatever are just as easy to calculate...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
December 7, 2009 at 3:27 am
The script calculates the uptime for the server, not the database service.
N 56°04'39.16"
E 12°55'05.25"
November 17, 2011 at 10:58 am
It aslo calculates a start time in the future:
server_up_time_minserver_up_time_hrserver_up_time_dayapprox_server_start_utc_datetime
-26332.06-438.87-18.292012-01-13 08:47:00
HTH -- Mark D Powell --
May 12, 2016 at 6:35 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply