How To Check SQL SERVER Uptime Through T-SQL

  • Comments posted to this topic are about the item How To Check SQL SERVER Uptime Through T-SQL

  • I added separation of the hours into days and hours.

    SET NOCOUNT ON

    DECLARE

    @crdate DATETIME

    , @days VARCHAR(3)

    , @hr VARCHAR(50)

    , @min-2 VARCHAR(5)

    SELECT @crdate=crdate FROM master.dbo.sysdatabases WHERE NAME='tempdb'

    SELECT @days=((DATEDIFF ( mi, @crdate,GETDATE()))/60)/24

    SELECT @hr=((DATEDIFF ( mi, @crdate,GETDATE()))/60)- (@days * 24)

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

    SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))

    ELSE

    SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*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.dbo.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

  • Nice script. Note that to get it to perform correctly on SQL Server 2005 you must change "sysdatabases" to "sys.sysdatabases".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hello,

    Just one minor amendment so that the script runs Okay on a case sensitive Server:

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

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Only a minor thought but if you replace the formulas with variables, I think it makes the code easier to read.

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days varchar(3),

    @hr VARCHAR(50),

    @min-2 VARCHAR(5),

    @today DATETIME

    SET @today = GETDATE()

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

    SET @min-2 = DATEDIFF (mi,@crdate,@today)

    SET @days= @min-2/1440

    SET @hr = (@min/60) - (@days * 24)

    SET @min-2= @min-2 - ( (@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

    David

  • After reworking it the @today is only use once so don't even need that variable.

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days varchar(3),

    @hr VARCHAR(50),

    @min-2 VARCHAR(5)

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    SET @days= @min-2/1440

    SET @hr = (@min/60) - (@days * 24)

    SET @min-2= @min-2 - ( (@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

  • got some cast errors with David's script.

    try--

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days INT ,

    @hr INT ,

    @min-2 INT

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    SET @days= @min-2/1440

    SET @hr = (@min/60) - (@days * 24)

    SET @min-2= @min-2 - ( (@hr + (@days*24)) * 60)

    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '

    + CAST(@days AS VARCHAR(30))+ ' days & '

    + CAST(@hr AS VARCHAR(30))+' hours & '

    + CAST(@min AS VARCHAR(30))+' 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 NOT running'

    END

    ELSE

    BEGIN

    PRINT 'SQL Server and SQL Server Agent both are running'

    END

    thanks,

    bc

    [font="Arial Narrow"]bc[/font]

  • got some cast errors with David's script.

    I didn't get any casting errors when I ran it but I agree your version is better with having the variables as INT and casting for printing purposes.

    David

  • SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days INT ,

    @hr INT ,

    @min-2 INT

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    PRINT @min-2

    gives me: 144069

    ---------------------------------

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days varchar(3),

    @hr VARCHAR(50),

    @min-2 VARCHAR(5)

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    PRINT @min-2

    gives me: *

    and then i got this when running the entire script with the varchars--

    Msg 245, Level 16, State 1, Line 10

    Conversion failed when converting the varchar value '*' to data type int.

    not sure whats going on there??

    bc

    [font="Arial Narrow"]bc[/font]

  • and then i got this when running the entire script with the varchars--

    Msg 245, Level 16, State 1, Line 10

    Conversion failed when converting the varchar value '*' to data type int.

    I'm a bit confused because the script does not contain a "*". Can you post the entire script that gives you the error.

    Thanks

    David

  • this script:

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days varchar(3),

    @hr VARCHAR(50),

    @min-2 VARCHAR(5)

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    PRINT @min-2

    PRINT '------------------------'

    SET @days= @min-2/1440

    gives this result:

    *

    ------------------------

    Msg 245, Level 16, State 1, Line 12

    Conversion failed when converting the varchar value '*' to data type int.

    So for whatever reason, @min-2 (varchar) is getting set with an asterisk value and then the script breaks when trying to set the @days variable because is can't do math on *.

    [font="Arial Narrow"]bc[/font]

  • i didn't get any error.

    SET NOCOUNT ON

    DECLARE @crdate DATETIME,

    @days varchar(3),

    @hr VARCHAR(50),

    @min-2 VARCHAR(5)

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

    SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())

    PRINT @min-2

    PRINT '-------------'

    SET @days= @min-2/1440

    print @days

    PRINT '-------------'

    Result:

    24293

    ------------------------

    16

    ------------------------

    SQL DBA.

  • I don't know why DATEDIFF would return a "*" rather than the number of minutes. At least the INT version works but still very odd.

    I can run the query with VARCHAR or INT and both work fine.

    David

  • it's because the varchar(5) is not big enough to hold mine which is 6 characters. try setting yours to varchar(3) and you should get the same error.

    bc

    [font="Arial Narrow"]bc[/font]

  • Good to know, thanks

    David

Viewing 15 posts - 1 through 15 (of 18 total)

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