SQL Server Error Log 관리

SQL Server Error Log

오류 로그는 서비스가 시작할 때부터 서비스가 중지될 때까지 계속 메시지를 기록한다. 로그는 심각도 수준이 19 ~ 25의 값을 가진 모든 오류를 기록하고, 모니터링 시 오류 로그에서 심각도 수준 19 ~ 25 사이의 값을 가진 오류는 반드시 체크해야 한다. 해당 심각도 수준의 오류는 트랜잭션을 실패하고, 응용 프로그램이 운영에 문제가 된다. 심각도 수준이 20 ~ 25 오류는 치명적이며, 클라이언트 연결은 오류 메시지를 받고 종료된다. 

SQL Server Error Log 포함된 사항

No	Contents
1	SQL Server 버전
2	운영체제 버전
3	서비스 시작 시간
4	서버 프로세스 ID
5	Error Log 파일 경로
6	서버 실행 모드 (Tread/Fiber)
7	수신 프로토콜 및 포트
8	시스템 및 사용자 데이터 베이스의 인스턴스 복구  프로세스 정보
9	서버 옵션 변경 정보
10	SQL Server에서 발생한 오류

SQL Server Error Log 읽기

sp_readerrorlog 실행 

Parameters	Type
에러 로그 파일 번호	0 = Current(현재), 1 =Archive #1, 2 = Archive #2, ...
로그 파일 종류	1 또는 null = Error Log, 2 = SQL Agent Log
검색인자 1	AND 연산자
검색인자 2	AND 연산자
검색 시작 시간	Date
검색 끝 시간	Date
결과 정렬	ascending(N'asc') , decending (N'desc')

--실행 예시  EXEC sp_readerrorlog 0, 1, '2017', 'backup', '2017-08-16', '2017-08-17', N'asc'

SQL Server Error Log 관리하기

테이블로 관리하기

A. SQL Server Error Log 및 Agent Log 관리용 테이블 생성

--SQL Server Error Log Table IF NOT EXISTS (SELECT * FROM sys.objects From object_id = OBJECT_ID(N'[dbo].[SQLERRORLOG]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[SQLERRORLOG] (        [mRegDate] [datetime] NULL,        [mProcessInfo] [varchar](10) NULL,        [mText] [varchar](max) NULL ) ON [PRIMARY] END GO --Agent Log Table IF NOT EXISTS (SELECT * FROM sys.objects From object_id = OBJECT_ID(N'[dbo].[SQLAGENTLOG]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[SQLAGENTLOG] (        [mRegDate] [datetime] NULL,        [mProcessInfo] [varchar](10) NULL,        [mText] [varchar](max) NULL ) ON [PRIMARY] END GO

B. 데이터 저장하기

--현재 시점까지 Error Log 저장 INSERT INTO [dbo].[SQLERRORLOG] EXEC ('sp_readerrorlog') GO EXEC msdb.dbo.sp_cycle_errorlog GO --현재 시점까지 Agent Log 저장 (Agent Service가 반드시 작동 중이어야 한다.) INSERT INTO [dbo].[SQLAGENTLOG] EXEC ('sp_readerrorlog -1, 2') GO EXEC msdb.dbo.sp_cycle_agent_errorlog GO

C. 데이터 확인 

테이블 생성 후 주기적으로 테이블에 데이터를 저장하여 관리하면, 로그를 빠르게 볼 수 있으며 오류 로그 크기가 커져서 디스크 공간을 크게 차지

하는 것을 예방 할 수 있다.

SELECT * FROM dbo.SQLERRORLOG WHERE mRegDate >= '2017-08-17'

참고: https://blogs.msdn.microsoft.com/repltalk/2011/01/05/how-to-archive-your-sql-errorlogs-into-a-table/

※데이터베이스 엔진 오류 심각도

Level of Severity	DESC
0~9	상태 정보를 반환하거나, 심각하지 않은 오류를 보고 데이터 베이스엔진에서 심각도 0~9인 경우 시스템 오류 발생시키지 않음
10	상태 정보를 반환하거나, 심각하지 않은 오류를 보고 호환성을 위해 오류 정보를 호출 응용 프로그램으로 반환하기 전에 심각도 10을 0으로 변환
11 ~ 16	사용자가 해결할 수 있는 오류
11	지정한 개체 또는 엔터티가 없음
12	특수한 쿼리 힌트로 인해 잠금을 사용하지 않는 쿼리에 대한 특수 심각도 일관성을 유지하는 기능인 잠금을 사용하지 않는 문으로 읽기 작업 수행 시 데이터 일관성 손상 가능
13	트랜잭션 교착 상태 오류 (DeadLock)
14	거부된 권한 등의 보안 관련 오류
15	T-SQL 명령의 구문 오류
16	사용자가 해결할 수 있는 일반 오류
17 ~ 19	사용자가 해결 할 수 없는 소프트웨어 오류. 시스템 관리자에게 알림
17	Statement가 실행되어 SQL Server에서 데이터베이스에 사용되는 디스크 공간, 메모리 또는 잠금등의 리소스 부족이거나, 시스템 관리자가 설정한 일정한 한계를 초과한 오류
18	데이터베이스 엔진 소프트웨어에 문제가 있지만, 문이 원료되고 데이터베이스 엔진 인스턴스에 대한 연결이 유지됨. 심각도가 18이면 시스템 관리자에게 알려야함
19	구성할 수 없는 데이터베이스 엔진 한계를 초과하여 현재 일괄처리가 종료 됨 심각도가 19 이산인 오류 메시지는 현재 일괄처리 실행이 중지된다. 거의 발생하지 않지만, 시스템 관리자 또는 주 지원 공급자를 통해 해결
19 ~ 25	오류 메시지를 오류 로그에 기록
20 ~ 24	시스템 문제 및 심각한 오류를 의미. 일괄 처리를 실행하는 데이터베이스 엔진 테스크가 더 이상 수행하지 않음. 태스크는 발생한 문제 정보를 기록하고 종료된다. 대부분의 경우 데이터베이스 엔진 인스턴스와 응용 프로그램과의 연결도 끊어지며, 다시 연결하지 못함 데이터 베이스 또는 개체 손상을 의미한다.
20	일괄처리에 문제가 발생함. 현재 테스크에만 영향을 주므로 데이터베이스 자체 손상은 되지 않는다.
21	현재 데이터베이스의 모든 태스크에 영향을 미치는 문제 발생. 데이터 베이스 자체 손상은 아니다.
22	메시지에서 지정된 테이블 또는 인덱스가 소프트웨어 또는 하드웨어 문제로 인해 손상됨 거의 발생하지 않으나, 오류 발생 시 DBCC CHECKDB 실행으로 데이터베이스 내 다른 개체 손상도 확인해야한다. 문제가 버퍼 캐시에 한정되고 디스크 자체에는 아무 이상이 없을 수도 있고 이 경우 데이터베이스 엔진 인스턴스 다시 시작하면 문제가 해결한다. 다시 시작해도 문제가 발생한다면 디스크 문제이다.  가끔 오류 메시지에 지정된 개체를 제거하여 문제를 해결 할 수 있다.
23	하드웨어 또는 소프트웨어 문제로 인해 전체 데이터베이스의 무결성이 의심됨 거의 발생하지 않으나. 문제가 캐시에 한정되고 디스크 자체 아무 이상 없을 수도 있다. 경우에 따라서는 데이터베이스 복원이 될 수도 있다.
24	미디어 오류. 시스템 관리자가 데이터베이스 복원 또는 하드웨어 공급업체에 문의해야 하는 경우