Server Up-Time Information

  • Comments posted to this topic are about the item Server Up-Time Information

  • 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;

  • All my installations seems to have negative values in the sample_ms column. Seems to be a bug or an overflow .

  • 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

    *****************/

  • The script calculates the uptime for the server, not the database service.


    N 56°04'39.16"
    E 12°55'05.25"

  • It aslo calculates a start time in the future:

    server_up_time_min server_up_time_hr server_up_time_day approx_server_start_utc_datetime

    -26332.06 -438.87 -18.29 2012-01-13 08:47:00

    HTH -- Mark D Powell --

  • Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply