Server Up-Time Information

  • Michael L. Smith

    SSC Enthusiast

    Points: 102

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

  • rbandyo

    Old Hand

    Points: 372

    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;

  • Selva Jayakar

    Valued Member

    Points: 52

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

  • DCPeterson

    SSCoach

    Points: 15150

    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

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

  • SwePeso

    SSC-Dedicated

    Points: 39693

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


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

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4450

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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