SQL Server 오류 로그 한번에 확인하기

SQL Server Error LOG는 인스턴스 Root 폴더의 LOG폴더에 텍스트 파일 형태로 기록되고
내용을 확인하려면 SSMS를 사용하게 됩니다.

내용을 검색할때는 SSMS에서 제공하는 로그 파일 뷰어를 사용해서 볼 수 있습니다.

만약 에러로그 파일을 분석해서 관리자에게 경고를 보내는 스크립트를 만들려면
로그 파일 뷰어 보다는 쿼리를 이용하는게 좋겠죠.
그래서 SQL Server에서는 sp_readerrorlog라는 저장프로시저를 제공합니다.
sp_readerror의 내용을 보면 다음과 같습니다.
create proc sys.sp_readerrorlog(
 @p1  int = 0,
 @p2  int = NULL,
 @p3  varchar(255) = NULL,
 @p4  varchar(255) = NULL)
as
begin

  if (not is_srvrolemember(N'securityadmin') = 1)
 begin
    raiserror(15003,-1,-1, N'securityadmin')
    return (1)
 end
 
 if (@p2 is NULL)
  exec sys.xp_readerrorlog @p1
 else
  exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end
총 4개의 파라메타가 있는데 각 파라메타의 의미는 다음과 같습니다.
@p1 : 0 = 현재 로그, 1 = 보관된 첫번째 로그, 2 = 보관된 두번째 로그...
@p2 : 1 or Null = Error Log, 2 = SQL Agent Log
@p3 and @p4 : Search 키워드, @p3과 @p4가 and 조건
입니다.

예를 들어보면
sp_readerrorlog 0, 1, 'login', 'failed'
이렇게 호출하면 현재 Error Log에서 'login' & 'failed'를 만족하는 로그를 검색해줍니다.
하지만 Error Log는 하나가 아니고 여러개입니다.
그래서 전체 Error를 검색하려면 @p1 파라메타에 숫자를 바꿔가면서 여러번 쿼리해야 합니다.
그래서 전체 Error Log를 검색하기는 불편합니다.
전체 Error Log의 갯수는  xp_enumerrorlogs 프로시져를 통해 알 수 있습니다.

다음 쿼리는 전체 Error Log를 대상으로 검색하는 쿼리입니다.
SET NOCOUNT ON

DECLARE @Type CHAR(1), @Keyword1 VARCHAR(1000), @Keyword2 VARCHAR(1000)

DECLARE @NumErrorLogs INT,
        @LogCnt INT

DECLARE @ErrorLogs TABLE (NO INT, DATE DATETIME, SIZE INT)
DECLARE @Errors TABLE (LogDate DATETIME, ProcessInfo VARCHAR(1000), Text VARCHAR(MAX))

INSERT @ErrorLogs EXEC xp_enumerrorlogs

SET @NumErrorLogs = (SELECT MAX(NO) FROM @ErrorLogs)

SET @LogCnt = 0

WHILE @NumErrorLogs > @LogCnt
BEGIN
    INSERT @Errors EXEC sp_readerrorlog @LogCnt, @Type, @Keyword1, @Keyword2
    SET @LogCnt = @LogCnt + 1
End

SELECT LogDate, ProcessInfo, Text = REPLACE(TEXT, '. ', '.' + CHAR(10))
FROM @Errors
ORDER BY LogDate DESC
sp_readerrorlog에서 @p1이 빠진 형태입니다.
Log File 번호를 지정하는 대신 xp_enumerrorlogs 를 사용하여 Log File의 갯수를 확인후
전체 파일에 대하여 sp_readerrorlog를 실행합니다.
파일이 클 경우에는 실행 시간이 길어질 수 있습니다.
적절히 수정해서 사용하세요.