I want to calculate the total downtime for a SQL service in a month.

  • I want to calculate the total downtime for a SQL service in a month . Anyways to find it?. 😎

    Regards,
    Saravanan

  • You could calculate the difference between the max and min times of all error logs in the past month.

    here is an example

    CREATE TABLE #current(logdate datetime,

    ProcessInfo varchar(10),

    text varchar(max));

    CREATE TABLE #previous(logdate datetime,

    ProcessInfo varchar(10),

    text varchar(max));

    INSERT INTO #current

    EXEC xp_readerrorlog 0;

    INSERT INTO #previous

    EXEC xp_readerrorlog 1;

    DECLARE

    @min-2 datetime,

    @max-2 datetime;

    SELECT @min-2 = MIN(logdate)

    FROM #current;

    SELECT @max-2 = MAX(logdate)

    FROM #previous;

    SELECT DATEDIFF(mi, @max-2, @min-2);

    DROP TABLE #current;

    DROP TABLE #previous;

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Thanks Bob.Appreciate your reply.

    I will recycle the errorlog every week and also I maintain only the last six error logs. It will not workout for me. :w00t:

    Any other way ?? :hehe:

    Regards,
    Saravanan

  • Why would you be able to used this? You have six weekly files which will encompass one months worth of logs. you just need to loop through your logs and then sum up the total.

    CREATE TABLE #err_log_tmp(ArchiveNo int,

    CreateDate nvarchar(24),

    Size int);

    INSERT INTO #err_log_tmp

    EXEC master.dbo.sp_enumerrorlogs;

    CREATE TABLE #time(downtime int);

    DECLARE

    @nologs int,

    @i int,

    @i_pre int,

    @min-2 datetime,

    @max-2 datetime;

    SET @i = 0;

    SELECT @nologs = MAX(ArchiveNo)

    FROM #err_log_tmp

    WHERE createdate > DATEADD(mm, -1, GETDATE());

    WHILE @i < @nologs

    BEGIN

    CREATE TABLE #current(logdate datetime,

    ProcessInfo varchar(10),

    text varchar(max));

    CREATE TABLE #previous(logdate datetime,

    ProcessInfo varchar(10),

    text varchar(max));

    SET @i_pre = @i + 1;

    INSERT INTO #current

    EXEC xp_readerrorlog @i;

    INSERT INTO #previous

    EXEC xp_readerrorlog @i_pre;

    SELECT @min-2 = MIN(logdate)

    FROM #current;

    SELECT @max-2 = MAX(logdate)

    FROM #previous;

    INSERT INTO #time

    SELECT DATEDIFF(mi, @max-2, @min-2);

    DROP TABLE #current;

    DROP TABLE #previous;

    SET @i = @i + 1;

    END;

    SELECT SUM(downtime)

    FROM #time;

    DROP TABLE #err_log_tmp;

    DROP TABLE #time;

    the only other option I can think of the figure out downtime, outside of third party monitoring would be to parse the windows system log for service stopped and running state and calculate the difference in time.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

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

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