Charger les journaux d’erreur SQL Server dans une table

Ce script vous permet de charger les journaux d’erreur (errorlogs) de SQL Server dans une table, ce qui va permettre de gérer les journaux volumineux et d’y faire des requêtes pour y trouver les informations recherchées.

Il utilise la procédure stockée système sp_enumerrorlogs pour connaître la liste des fichiers, puis charge chacun via la procédure étendue xp_readerrorlog et les ajoute à la table finale.

/*------------------------------------------------------------------- 
[SCRIPT] Load SQL Error logs 
[DATABASE] master 
[DESCRIPTION] Load SQL Server Errorlogs in a table for analysis 
[MAJ PAR] DATAFLY - Arian Papillon 
[DATE] 20181218 
-------------------------------------------------------------------*/
CREATE TABLE dbo.Errorlogs
	(
		EventTime datetime,
		MetaText varchar(128),
		Message nvarchar(max),
		ID int identity(1,1) PRIMARY KEY CLUSTERED
	) 
CREATE TABLE dbo.#LogRecords
	(
		EventTime datetime,
		MetaText varchar(128),
		Message nvarchar(max),
		ID int identity(1,1) PRIMARY KEY CLUSTERED
	) 
DECLARE        @CurrentLog SMALLINT;
IF OBJECT_ID('tempdb..#err_log_tmp') IS NOT NULL DROP TABLE #err_log_tmp
CREATE TABLE #err_log_tmp (ArchiveNo int, CreateDate nvarchar(128), Size int)
INSERT #err_log_tmp exec master.dbo.sp_enumerrorlogs
DECLARE elc CURSOR FOR SELECT ArchiveNo FROM #err_log_tmp ELT
OPEN elc
FETCH NEXT FROM elc INTO @CurrentLog
WHILE @@FETCH_STATUS = 0
BEGIN
    	TRUNCATE TABLE #LogRecords
		INSERT #LogRecords (EventTime, MetaText, Message)
        EXEC xp_readerrorlog @CurrentLog, 1;
		INSERT dbo.ErrorLogs (EventTime,MetaText, Message) SELECT (SELECT MAX(EventTime) FROM #LogRecords),'File:','Errorlog.'+CASE WHEN @CurrentLog = 0 THEN '' ELSE CAST(@CurrentLog AS varchar(3)) END
		INSERT dbo.ErrorLogs SELECT EventTime,Metatext,Message FROM #LogRecords ORDER BY ID DESC
    FETCH NEXT FROM elc INTO @CurrentLog
END;
CLOSE elc;
DEALLOCATE elc;

SELECT EventTime,MetaText,Message FROM dbo.Errorlogs

/*-------------------------------------------------------------------

[SCRIPT] Load SQL Error logs

[DATABASE] master

[DESCRIPTION] Load SQL Server Errorlogs in a table for analysis

[MAJ PAR] DATAFLY - Arian Papillon

[DATE] 20181218

-------------------------------------------------------------------*/

CREATE TABLE dbo.Errorlogs

(

EventTime datetime,

MetaText varchar(128),

Message nvarchar(max),

ID int identity(1,1) PRIMARY KEY CLUSTERED

)

CREATE TABLE dbo.#LogRecords

(

EventTime datetime,

MetaText varchar(128),

Message nvarchar(max),

ID int identity(1,1) PRIMARY KEY CLUSTERED

)

DECLARE @CurrentLog SMALLINT;

IF OBJECT_ID('tempdb..#err_log_tmp') IS NOT NULL DROP TABLE #err_log_tmp

CREATE TABLE #err_log_tmp (ArchiveNo int, CreateDate nvarchar(128), Size int)

INSERT #err_log_tmp exec master.dbo.sp_enumerrorlogs

DECLARE elc CURSOR FOR SELECT ArchiveNo FROM #err_log_tmp ELT

OPEN elc

FETCH NEXT FROM elc INTO @CurrentLog

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #LogRecords

INSERT #LogRecords (EventTime, MetaText, Message)

EXEC xp_readerrorlog @CurrentLog, 1;

INSERT dbo.ErrorLogs (EventTime,MetaText, Message) SELECT (SELECT MAX(EventTime) FROM #LogRecords),'File:','Errorlog.'+CASE WHEN @CurrentLog = 0 THEN '' ELSE CAST(@CurrentLog AS varchar(3)) END

INSERT dbo.ErrorLogs SELECT EventTime,Metatext,Message FROM #LogRecords ORDER BY ID DESC

FETCH NEXT FROM elc INTO @CurrentLog

END;

CLOSE elc;

DEALLOCATE elc;

SELECT EventTime,MetaText,Message FROM dbo.Errorlogs