0

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.

3 Answers 3

2

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
3
  • @Sabotassi,Thanks a lot.Exactly what I was looking for. Sep 26, 2016 at 7:28
  • @Sabotassi,what does 0 means.What if date diff is greater than 24 hours will I get 1 as value ? Sep 26, 2016 at 7:42
  • 1
    @SQLBoy it means that the server has not restarted within 24 hours. You can test different results by hard-coding the login_time: SELECT login_time as start_time ,IIF( DATEDIFF(hh, '2016-09-26 07:00:00.000', GETDATE() ) < 24, 1, 0) as has_started_within_24_hours ,DATEDIFF(hh, '2016-09-26 07:00:00.000', GETDATE()) as online_hours FROM sys.sysprocesses WHERE spid = 1
    – Sabotaasi
    Sep 26, 2016 at 7:48
3

It's very simple, tempdb database re-creates every time when SQL Server re-started. So, create_date of tempdb database is the date & time when SQL Server restarted/started.

SELECT  case when datediff(mi,create_date,getdate())<1440
        then 'SQL Server got restarted last '+cast(datediff(hh,create_date,getdate()) as varchar)+' hours '+cast(datediff(mi,create_date,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(create_date as varchar)
        end
FROM sys.databases where database_id=DB_ID('tempdb')

NOTE: Above query would not return correct result if TF 3609 is enabled in SQL Server

So, in addition to, below query will also help you to find out SQL Serve Start time.

SELECT case when datediff(mi,sqlserver_start_time,getdate())<1440
then 'SQL Server got restarted last '+cast(datediff(hh,sqlserver_start_time,getdate()) as varchar)+' hours '+cast(datediff(mi,sqlserver_start_time,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(sqlserver_start_time as varchar)
end
FROM sys.dm_os_sys_info

Thanks

5
  • Hi Rajesh Ranjan,yes I agree with you and I am aware.I just want to see if any possibility to know if the restart is within last 24 hours as I am working on a Powershell Automation.Thanks a lot @Rajesh Sep 26, 2016 at 7:26
  • I update my answer according to your requirement to get hours and minutes before SQL Server restarted. Sep 26, 2016 at 7:34
  • @RajeshRanjan If you have enabled trace flag 3609 tempdb is not recreated on SQL Server startup.
    – Shanky
    Sep 26, 2016 at 11:24
  • @Shanky Can you please tell me why user would avoid to use and in hat situation we would not use it (tempdb)?? Sep 26, 2016 at 11:30
  • 1
    The question is not why and when but should we ALWAYS depend on tempdb creation to gauge SQL Server start time. The point is the query you have posted would give incorrect result if TF is enabled. Yes you would hardly find instance with this TF enabled but we cannot blindly believe on this query.
    – Shanky
    Sep 26, 2016 at 11:56
1

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.

There is no need for doing it, unless you are keeping track and history of all sql server restarts.

You can easily find sql server start date using sys.dm_server_services DMV (provided you are running sql server 2008 R2 SP1 and up)

if (select DATEDIFF(hh, last_startup_time, GETDATE() ) from sys.dm_server_services where filename like '%sqlservr.exe%') <= 24
begin
--insert into dbo.YourAuditTable
select DATEDIFF(hh, last_startup_time, GETDATE() ) as HoursSinceSQLServerRestart
        , *  --- choose your columns !
from sys.dm_server_services
where filename like '%sqlservr.exe%'
end
else 
select 'SQL Server restart time is more than 24 hrs'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.