January 10, 2014 at 4:46 am
I want to calculate the total downtime for a SQL service in a month . Anyways to find it?. 😎
Regards,
Saravanan
January 10, 2014 at 11:09 am
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;
January 15, 2014 at 8:52 am
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
January 15, 2014 at 9:36 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply