[MsSQL] sp_readerrorlog, xp_readerrorlog 를 사용하여 구동 시점에서부터 오류만 뽑아오기

SQL Server 는 sp_readerrorlog 라는 시스템 프로시저를 지원합니다.

이 프로시저는 SQL Server 로그를 읽어오는 역할을 수행합니다.

xp_readerrorlog 역시 지원하는데, 이는 sp_readerrorlog 보다 좀더 많은 기능을 지원합니다.

단지 기능만을 지원하는 것 뿐이지 출력 형식이 달라진다거나 그러지는 않습니다.

xp_readerrorlog 와 sp_readerrorlog 의 차이점은 아래 포스팅을 참고해 주세요.

https://mozi.tistory.com/296

구동 시점 이후의 오류만 출력하는 쿼리

그럼 먼저 구동 시점 이후의 오류만 뽑아오는 쿼리를 보여드리고 아래서 설명을 진행하겠습니다.

CREATE TABLE dbo.TEMP_logcnt (LogNumber INT, StartDate DATETIME, SizeINBytes INT)

CREATE TABLE dbo.TEMP_log (ID INT IDENTITY, LogDate DATETIME, ProcessInfo NVARCHAR(100), Text NVARCHAR(1000))

INSERT INTO dbo.TEMP_logcnt EXEC sp_enumerrorlogs 1

DECLARE @count INT

SET @count = 0

WHILE @count <= (SELECT MAX(LogNumber) FROM dbo.TEMP_logcnt)

BEGIN

  INSERT INTO dbo.TEMP_log EXEC sp_readerrorlog @count, 1, "Server Process ID is"

  IF ( 1 <= (SELECT COUNT(*) FROM dbo.TEMP_log WHERE ProcessInfo = 'Server') )

  BEGIN

    BREAK

  END

  SET @count = @count + 1  

END

DECLARE @date DATETIME

SELECT TOP 1 @date = LogDate FROM dbo.TEMP_log WHERE ProcessInfo = 'Server'

WHILE 0 <= @count

BEGIN

  DELETE FROM dbo.TEMP_log

  INSERT INTO dbo.TEMP_log (LogDate, ProcessInfo, Text) EXEC xp_readerrorlog @count, 1, NULL, NULL, @date, NULL

  SET @count = @count - 1

END;

WITH CTE AS

(

  SELECT *, LEAD(Text) OVER (ORDER BY ID) AS TXT1 FROM dbo.TEMP_log

)

SELECT * FROM CTE WHERE TEXT LIKE '오류:%';

DROP TABLE dbo.TEMP_logcnt

DROP TABLE dbo.TEMP_log

쿼리 설명

1. TEMP_LOG 와 TEMP_LOGCNT 테이블을 생성합니다.

 TEMP_LOG 는 오류 로그를 담을 테이블 이고, TEMP_LOGCNT 는 SQL Server 로그의 갯수를 저장합니다.

2. sp_enumerrorlogs 를 사용하여 SQL Server 로그의 파일 개수를 TEMP_LOGCNT 에 저장합니다.

3. SQL Server 최신 로그 파일부터 가장 마지막의 파일까지 반복적으로 읽기가 수행됩니다.

   이 때, Server 프로세스가 기록한 내용이 Server Process ID is 라면 반복문을 종료합니다. 이는 서버가 구동될 때 기록되는 로그이기 때문입니다.

   그리고 몇 번째 파일까지를 반복하여 읽었는지를 count 변수에 저장합니다.

4. xp_readerrorlog 프로시저를 사용하여, Server Process ID is 로그가 기록된 시간부터 현재까지의 로그를 dbo.TEMP_Log 에 저장합니다.

5. 해당하는 오류 코드와 관련된 메시지를 한줄로 정리하여 임시 CTE 테이블에 저장합니다.

6. CTE 테이블의 레코드를 출력합니다.