SQL Server Restart Time – How to Check

sql servert-sql

I am using below command to get SQL Server last restart time :

Select min(Login_time) from master.sys.sysprocesses

select getdate()

Can anyone help me with a query if possible : If the SQL Server last start time is within last 24 Hours from current select getdate() it should be written to another column that SQL Server got restarted last XX hours back along with SQL Server Last restart time.

Best Answer

It seems like you are looking for the DATEDIFF function.

An example would look something like this:

SELECT
    login_time as start_time
    ,IIF( DATEDIFF(hh, login_time, GETDATE() ) < 24, 1, 0) as has_started_within_24_hours
    ,DATEDIFF(hh, login_time, GETDATE()) as online_hours
FROM sys.sysprocesses
WHERE spid = 1

If you would like to see the other columns only if the difference is smaller than 24 hours, then I suggest using IF..ELSE. You don't have to use the variable, but it makes testing easier:

DECLARE @is_recent int
SELECT @is_recent = IIF( DATEDIFF(hh, login_time, GETDATE() ) < 24, 1, 0) FROM sys.sysprocesses WHERE spid = 1

IF @is_recent = 1
    SELECT
        login_time as start_time
        ,IIF( DATEDIFF(hh, login_time, GETDATE() ) < 24, 1, 0) as has_started_within_24_hours
        ,DATEDIFF(hh, login_time, GETDATE()) as online_hours
    FROM sys.sysprocesses
    WHERE spid = 1
ELSE
    SELECT
        login_time as start_time
    FROM sys.sysprocesses
    WHERE spid = 1