locked
How To Check SQL SERVER Uptime Through T-SQL RRS feed

  • Question

  • logon to SQL Server management studio and connect to the master database instance

     

    The after that you run this script against the master database instance only...

     

    Highlighted Below:

     

    SQLScript:

    SET NOCOUNT ON
    DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
    SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
    SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
    IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
    ELSE
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
    IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
    PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
    END
    ELSE BEGIN
    PRINT 'SQL Server and SQL Server Agent both are running'
    END

     

     

     

     


     


     

     

     

    • Changed type Phil Brammer Monday, January 11, 2010 5:52 PM
    • Changed type Phil Brammer Monday, January 11, 2010 5:52 PM
    • Changed type Tom Phillips Tuesday, January 12, 2010 2:16 AM
    • Moved by Tom Phillips Monday, August 16, 2010 7:29 PM TSQL Question (From:SQL Server Database Engine)
    Wednesday, August 6, 2008 10:14 AM

Answers

  • Nice script!

    I tested it. I added USE master to be complete:

    Code Snippet

    USE

    master;

    SET

    NOCOUNT ON

    DECLARE

    @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

    SELECT

    @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

    SELECT

    @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

    IF

    ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

    SELECT

    @min=(DATEDIFF ( mi, @crdate,GETDATE()))

    ELSE

    SELECT

    @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

    PRINT

    'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

    IF

    NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

    BEGIN

    PRINT

    'SQL Server is running but SQL Server Agent <<NOT>> running'

    END

    ELSE

    BEGIN

    PRINT

    'SQL Server and SQL Server Agent both are running'

    END

    Kalman Toth SQL SERVER  & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016




    • Proposed as answer by Naomi N Monday, September 13, 2010 8:02 PM
    • Marked as answer by Kalman Toth Thursday, December 29, 2011 10:54 PM
    • Edited by Kalman Toth Friday, November 10, 2017 3:36 PM
    Wednesday, August 6, 2008 4:24 PM
  • Here is what it takes to run on SQL Server 2008 R2:

    SET NOCOUNT ON
    DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
    SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
    SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
    IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
    ELSE
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
    IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
    PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
    END
    ELSE BEGIN
    PRINT 'SQL Server and SQL Server Agent both are running'
    END
    
    • Proposed as answer by Kalman Toth Monday, August 16, 2010 7:40 AM
    • Marked as answer by Kalman Toth Friday, October 22, 2010 2:51 PM
    Monday, August 16, 2010 5:52 AM
  • This script runs as well on SQL Server 2005 :-)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Kalman Toth Thursday, December 29, 2011 10:54 PM
    Monday, August 16, 2010 6:56 AM
    Answerer

All replies

  • Nice script!

    I tested it. I added USE master to be complete:

    Code Snippet

    USE

    master;

    SET

    NOCOUNT ON

    DECLARE

    @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

    SELECT

    @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

    SELECT

    @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

    IF

    ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

    SELECT

    @min=(DATEDIFF ( mi, @crdate,GETDATE()))

    ELSE

    SELECT

    @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

    PRINT

    'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

    IF

    NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

    BEGIN

    PRINT

    'SQL Server is running but SQL Server Agent <<NOT>> running'

    END

    ELSE

    BEGIN

    PRINT

    'SQL Server and SQL Server Agent both are running'

    END

    Kalman Toth SQL SERVER  & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016




    • Proposed as answer by Naomi N Monday, September 13, 2010 8:02 PM
    • Marked as answer by Kalman Toth Thursday, December 29, 2011 10:54 PM
    • Edited by Kalman Toth Friday, November 10, 2017 3:36 PM
    Wednesday, August 6, 2008 4:24 PM
  • Adeoye

     

    I notice that you are posting seemingly random bits of information.

     

    It would be far more useful to the Forums if you were to offer your assistance to Users by posting suggestions to their problems.

     

    These 'random' thoughts aren't really helping anyone...

     

    Tuesday, October 7, 2008 3:25 PM
  • Great job.  very nice code.
    Too Soon Old, Too Late Smart
    Sunday, August 2, 2009 8:24 AM
  • Gave an Error Invalid Colmn  Line 3 cloumn NAME. Change it to lower case to reolve.
    Monday, January 11, 2010 5:29 PM
  • Here is what it takes to run on SQL Server 2008 R2:

    SET NOCOUNT ON
    DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
    SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
    SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
    IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
    ELSE
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
    IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
    PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
    END
    ELSE BEGIN
    PRINT 'SQL Server and SQL Server Agent both are running'
    END
    
    • Proposed as answer by Kalman Toth Monday, August 16, 2010 7:40 AM
    • Marked as answer by Kalman Toth Friday, October 22, 2010 2:51 PM
    Monday, August 16, 2010 5:52 AM
  • This script runs as well on SQL Server 2005 :-)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Kalman Toth Thursday, December 29, 2011 10:54 PM
    Monday, August 16, 2010 6:56 AM
    Answerer
  • The following script attempts to get the uptime too, but why is there a difference? Which of these should be considered more accurate?

     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(n,
         -ROUND(server_up_time_min, -1),
         DATEADD(hh, -ROUND(server_up_time_hr, -1), DATEADD(d, -ROUND(server_up_time_day, -1), GETUTCDATE()))
         ) AS smalldatetime)
          AS approx_server_start_utc_datetime
     FROM ServerUpTimeInfo;
     GO
    

     

    Monday, September 13, 2010 7:59 PM
  • Aalam,

    sys.dm_io_virtual_file_stats returns the start time of the computer, not the start time of the SQL Server instance. See http://sqlity.net/en/89/determine-the-uptime-of-a-sql-server-instance/ for more details.


    • Edited by sqlity.net Tuesday, April 22, 2014 1:41 AM
    Friday, December 17, 2010 8:20 PM
  • Aalam, you redundantly added hours, minutes and days, which are the same information in different forms, so the final date was way off.

    Try this for server uptime (4 minutes more than SQL uptime, on the server I'm looking at.)

     

     

     

    WITH ServerUpTimeInfo AS (

       SELECT (dm_io_virtual_file_stats.sample_ms / 1e3 ) 

          AS server_up_time_sec,

          (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(second,-server_up_time_sec,GETUTCDATE()) AS smalldatetime)

          AS approx_server_start_utc_datetime,

       CAST(DATEADD(second,-server_up_time_sec,GETDATE()) AS smalldatetime)

          AS approx_server_start_localtime

     FROM ServerUpTimeInfo;

     GO

     

     

     

    Monday, January 30, 2012 3:12 PM
  • Hi All,

    Here is the same script, also via UI u can get it right click the tempdb and created date :)

    =============================

    SET NOCOUNT ON
    DECLARE @crdate DATETIME,
    @days varchar(3),
    @hr VARCHAR(50),
    @min VARCHAR(5),
    @today DATETIME

    SET @today = GETDATE()

    SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

    SET @min = DATEDIFF (mi,@crdate,@today)
    SET @days= @min/1440
    SET @hr = (@min/60) - (@days * 24)
    SET @min= @min - ( (@hr + (@days*24)) * 60)

    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
    +@days + ' days & '
    +@hr+' hours & '
    +@min+' minutes'
    IF NOT EXISTS (SELECT 1 FROM master.sys.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
    PRINT 'SQL Server is running but SQL Server Agent running'
    END
    ELSE
    BEGIN
    PRINT 'SQL Server and SQL Server Agent both are running'
    END

    ============================

    Regards

    RajU R

    • Proposed as answer by Artem Ervits Tuesday, May 1, 2012 8:29 PM
    • Unproposed as answer by Artem Ervits Tuesday, May 1, 2012 8:29 PM
    Thursday, April 26, 2012 3:53 AM
  • I usually check the create date database property on tempdb.

    select create_date from sys.databases where name = 'tempdb'

    Tuesday, May 1, 2012 8:30 PM
  • in 2008r2 additional field was added to sys.dm_os_sys_info called sqlserver_start_time

    select sqlserver_start_time from sys.dm_os_sys_info
    • Edited by Artem Ervits Tuesday, May 1, 2012 8:33 PM
    • Proposed as answer by Microslave2k Thursday, September 13, 2012 9:34 AM
    Tuesday, May 1, 2012 8:33 PM
  • Hi,

    The script works fine.  However, I want to direct the output into a table.  Can it be done ?

    Thanks

    =====

    Wednesday, July 4, 2012 4:46 PM
  • Yes, you can create a table ServerState (IdleMessage varchar(max))

    and instead of the PRINT statements use

    insert into ServerState (IdleMessage)

    select -- here follows what is currently printed


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, July 4, 2012 4:49 PM
  • Thanks.
    Thursday, July 5, 2012 4:06 PM