Post reply

Checking SQL Server UPTIME

  • March 2, 2005 at 7:00 am

    #68378

    does any one have sql code to check the sql server uptime that returns a string with yrs, months, days, hrs, mi the server has been up ?  i have a script that works but it only gives me minutes..

    SELECT tim=datediff(mi, login_time, getdate())FROM master..sysprocesses WHERE spid = 1

    thanks

     

    kim

     

  • March 2, 2005 at 7:34 am

    #544872

    You might be able to write some logic using "DATEPART" to give you the individual components...

     

     

  • March 2, 2005 at 7:34 am

    #544871

    -- how to determin SQLServer Uptime

    --  Tracking Uptime by Brian Moran http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html

    --

    SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes

    FROM master..sysprocesses WHERE spid = 1

    go

    -- my own tests

    SELECT @@servername as ServerName,  Dateadd(ss, SQLServer_UpTime_Seconds * (-1), getdate() ) as StartUp_DateTime

    from (

    SELECT datediff(ss, login_time, getdate()) as SQLServer_UpTime_Seconds

    FROM master..sysprocesses

    WHERE spid = 1

    ) a

    go

     

    SELECT @@servername as ServerName,   getdate() - login_time as SQLServer_UpDateTime_1900_01_01

    FROM master..sysprocesses

    WHERE spid = 1

    go

    SELECT @@servername as ServerName,  Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years

    , month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end  as Months

    , day( SQLServer_UpTime) - 1 as Days

    , substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart

    from (

    SELECT  getdate() - login_time as SQLServer_UpTime  -- opgepast start vanaf 1900-01-01

    FROM master..sysprocesses

    WHERE spid = 1

    ) a

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 2, 2005 at 9:09 am

    #544919

    thanks all, thats what i needed ...

  • March 3, 2005 at 11:21 am

    #545299

    Very useful queries ... Thanx alzdba !!!

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 5 posts - 1 through 4 (of 4 total)

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