MSSQL Server’da Error Log Dosyasını Bir Tabloya Kaydetmek

Bu makalede mssql server üzerinde bulunan error log’ları belirli bir tablo altına kaydetmiş olacağız. Aynı zamanda filtreleme işlemi sonucu log dosyasında bulunan gereksiz mesajlardan kaçınmış olacağız. sp_readerrorlog procedure‘ün kullanıldığı bu yapıda toplanan logların belirli bir filtrelemeden sonra son kullanıcıya tablo aracılığı ile gösterilmesi amaçlanmaktadır.

İlk olarak logları kaydedeceğimiz tablomuzu oluşturuyoruz.

• CaptureDate: Procedure yapısının o an çalıştığı tarih değerini almaktadır.
• LogDate: Error log’da bulunan log’un tarihini kaydetmektedir.
• LogType: Error log veya Agent log olduğunu buradan bulabiliriz. Aynı zamanda job’ımız oluşturulduğunda ikisini de aynı tabloya kaydetme işlemi yapmaktadır.

USE [DBAMON]
GO

CREATE TABLE [dbo].[ErrorAgentLog](
	[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CaptureDate] [datetime] NULL,
	[LogDate] [datetime] NULL,
	[ProcInfo] [varchar](64) NULL,
	[LogText] [varchar](max) NULL,
	[LogType] [varchar](10) NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (ONLINE=ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Agent ve Error log’ları toplayacağımız procedure yapımızı oluşturuyoruz.

USE [DBAMON]
GO

CREATE procedure [dbo].[CaptureErrorAgentLog]
@LogType smallint = 1
AS
BEGIN

DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
Declare @maxLogDate datetime;
DECLARE @cnt int = 0;
DECLARE @logcnt int = 0;
Declare @recordDate datetime;
declare @InstanceId numeric(18,0);
DECLARE @CaptureLogs TABLE
(LogDate Datetime,ProsessInfo sysname,Text varchar(max) )
DECLARE @logFiles TABLE (
LogID INT,
LogDate DATETIME,
LogSize BIGINT );

--begin try

INSERT INTO @logFiles
EXEC sys.sp_enumerrorlogs @LogType;

select @logcnt = (count(*)-1) from @logFiles;

select @maxLogDate = max(LogDate) from [DBAMON].[dbo].[ErrorAgentLog]
where LogType = @LogType ;

IF (@maxLogDate IS NULL)
begin
SELECT @StartDate = getdate()-7;
end
else
begin
SELECT @StartDate = @maxLogDate;
end
SELECT @EndDate = dateadd(ss,-1,getdate());

INSERT @CaptureLogs
EXEC master.dbo.xp_readerrorlog 0, @LogType, null, null, @StartDate, @EndDate, 'asc';


while @cnt < @logcnt
begin
set @cnt = @cnt + 1;
INSERT @CaptureLogs
EXEC master.dbo.xp_readerrorlog @cnt, @LogType, null, null, @StartDate, @EndDate, 'asc';
end;

select @recordDate = getdate();
IF (@maxLogDate IS NULL)
begin
insert into [DBAMON].[dbo].[ErrorAgentLog]
 (
[CaptureDate],
[LogDate],
[ProcInfo],
[LogText],
[LogType])
select @recordDate , LogDate, ProsessInfo , Text , @LogType from @CaptureLogs
where (text not like '%informational message%' and text not like '%BACKUP DATABASE successfully%' and text not like '%BACKUP DATABASE WITH DIFFERENTIAL successfully%' and text not like '%Buffer Pool scan took%' and text not like '%was killed by hostname%') 
order by LogDate asc;
end
else
begin
insert into [DBAMON].[dbo].[ErrorAgentLog]
 (
[CaptureDate],
[LogDate],
[ProcInfo],
[LogText],
[LogType])
select @recordDate , LogDate, ProsessInfo , Text , @LogType from @CaptureLogs
where (text not like '%informational message%' and text not like '%BACKUP DATABASE successfully%' and text not like '%BACKUP DATABASE WITH DIFFERENTIAL successfully%' and text not like '%Buffer Pool scan took%' and text not like '%was killed by hostname%') 
and LogDate > @maxLogDate
order by LogDate asc;
end


/*end try
begin catch
declare
@error_message nvarchar(4000),
@error_number int,
@error_state int,
@error_severity int,
@error_procedure nvarchar(126),
@error_line int;

select @errornumber = errornumber()
,@errorstate = errorstate()
,@errorseverity = errorseverity()
,@errorprocedure = errorprocedure()
,@errorline = errorline()
set @errormessage = left('CaptureErrorAgentLog Error Message: ' + errormessage(), 4000)
raiserror(700016, @errorseverity, @errorstate, @error_message)
exec [DBAMON].[dbo].[CaptureError] @errormessage,
@error_number,
@error_state,
@error_severity,
@error_procedure,
@error_line
end catch*/
END
GO

Yukarıda procedure yapımızı oluşturduktan sonra Sql server agent altında belirli aralıklarla çalışması için aşağıdaki stepler oluşturulmaktadır.

Step 1’de sadece sql server Error loglarından alınan log kayıtlarını göstermektedir.

EXECUTE [DBAMON].[dbo].[CaptureErrorAgentLog]  @LogType = 1

Step 2’de sadece sql agent error loglarından alınan log kayıtlarını göstermektedir.

EXECUTE [DBAMON].[dbo].[CaptureErrorAgentLog] @LogType = 2

Mevcutta oluşan tablomuza select çektiğimizde sql server error loglarda bulunan kayıtların tablomuza kaydedildiğini görmüş oluruz.

Select sorgumuzda logType kısmında 2 belirtilmesi sql agent error loglarının kaydediğini göstermektedir.

İlgili job’ın günün herhangi bir anından bir kere çalıştırılması yeterlidir. Başka makalede görüşmek dileğiyle..

“Yeryüzündeki bütün ağaçlar kalem olsaydı, deniz de -ardından ona yedisi daha eklenmek üzere- mürekkep olsaydı yine de Allah’ın sözleri tükenmezdi; Allah azîzdir, hakîmdir.”Lokman-27