DBCC CHECKDB 실행과 히스토리 관리

DBCC CHECKDB 실행과 히스토리 관리

 

• Version : SQL Server 2000, 2005, 2008, 2008R2, 2012

 

우리는 데이터베이스 무결성 검사시 DBCC CHECKDB를 사용한다.

개인적으로는 주기적으로 CHECKDB를 통하여 데이터베이스의 무결성 검사를 할 것을 권장한다. 참고로 CHECKDB를 실행하는 동안 성능 문제가 발생 할 수 있으므로 DBA가 잘 판단하여 진행 하도록 하자.(나의 경우에는 매주 진행되는 정기점검에 검사한다.)

 

• CHECKDB를 실행하는 것 까지는 좋았는데 이 결과를 히스토리로 관리할 방법이 없을까?
• 원하는정보만 쿼리를 이용하여 확인 할 수는 없을까?

오늘 실습을 통하여 CHECKDB의 결과를 저장하고 검색하는 방법을 알아 보자.

 

 

SSMS에서 CHECKDB를 실행하여 보자. 다음과 같이 텍스트로 결과가 출력되는 것을 확인 할 수 있다.

DBCC CHECKDB(SW_TEST)

 

 

 

CHECKDB의 실행 기록 유무는 에러로그에서 확인 할 수 있다. 에러로그에는 요약 정보만 나타난다. 그리고 여러가지 로그가 함께 보여 내가 원하는 정보를 쉽게 보기 힘들다.

SP_READERRORLOG

 

 

 

CHECKDB의 결과를 히스토리로 저장하고 더 많은 정보를 쉽게 보기 위하여 테이블에 저장하는 구조를 만들어 보자. 다음 스크립트를 이용하여 결과를 저장할 테이블을 생성한다. 데이터베이스 하나의 CHECKDB 결과가 약 60건이 저장되는 것을 확인 할 수 있다.

BEGIN TRY     DROP TABLE DBCC_CHECKDB_HISTORY END TRY BEGIN CATCH END CATCH GO   CREATE TABLE DBCC_CHECKDB_HISTORY( [REGDATE] DATETIME DEFAULT GETDATE(), [ERROR] INT, [LEVEL]INT, [STATE] INT, [MESSAGETEXT] NVARCHAR(500), [REPAIRLEVEL] INT, [STATUS] INT, [DBID] INT, [OBJECTID] INT, [INDEXID] INT, [PARTITIONID] INT, [ALLOCUNITID] INT, [REFFILE] INT, [PAGE] INT, [SLOT] INT, [FEFILE] INT, [REFPAGE] INT, [REFSLOT] INT, [ALLOCATION] INT )   INSERT INTO DBCC_CHECKDB_HISTORY(ERROR, [LEVEL], [STATE], MESSAGETEXT, REPAIRLEVEL, [STATUS], [DBID], OBJECTID, INDEXID, PARTITIONID, ALLOCUNITID, [FEFILE], PAGE, SLOT, REFFILE, REFPAGE, REFSLOT, ALLOCATION) EXEC ('DBCC CHECKDB(SW_TEST) WITH TABLERESULTS')

 

 

 

DBCC_CHECKDB_HISTORY를 조회하여 보자. 다음과 같이 저장된 정보가 나타난다.

 

 

데이터베이스 서버에는 여러 개의 사용자 데이터베이스 및 시스템데이터베이스가 운영되고 있다. 커서를 통하여 자동으로 사용자 데이터베이스를 검사하는 스크립트를 생성하도록 한다. 프로시저로 만들어 놓으면 Job Agent 등에 등록하여 사용할 때 유용하다.

CREATE PROC [DBO].[USP_CHECKDB_JEVIDA]   AS   DECLARE @DATABASE_NAME NVARCHAR(100)   DECLARE DATABASE_LIST CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN ( 'MASTER' , 'MODEL' , 'MSDB' , 'TEMPDB' )     AND STATE_DESC = 'ONLINE'     AND SOURCE_DATABASE_ID IS NULL -- REAL DBS ONLY (NOT SNAPSHOTS)     AND IS_READ_ONLY = 0   OPEN DATABASE_LIST FETCH NEXT FROM DATABASE_LIST INTO @DATABASE_NAME WHILE @@FETCH_STATUS = 0 BEGIN   INSERT INTO DBCC_CHECKDB_HISTORY (ERROR, [LEVEL], [STATE], MESSAGETEXT, REPAIRLEVEL, [STATUS], [DBID], OBJECTID, INDEXID, PARTITIONID, ALLOCUNITID, [FEFILE], PAGE, SLOT, REFFILE, REFPAGE, REFSLOT, ALLOCATION) EXEC ( 'DBCC CHECKDB(''' + @DATABASE_NAME + ''') WITH TABLERESULTS' )   FETCH NEXT FROM DATABASE_LIST INTO @DATABASE_NAME END   CLOSE DATABASE_LIST DEALLOCATE DATABASE_LIST

 

 

 

프로시저 생성 후 프로시저를 실행 하여 보자. 나의 경우에는 예제 데이터베이스 등이 설치되어있다.

EXEC USP_CHECKDB_JEVIDA

 

 

 

CHECKDB가 완료되고나면 히스토리 테이블에서 필요한 정보만 볼 수 있도록 쿼리하여 보자.

SELECT * FROM     (     SELECT         REGDATE, ERROR, LEVEL, DB_NAME(DBID) AS DATABASENAME, MESSAGETEXT     FROM DBCC_CHECKDB_HISTORY     ) AS x WHERE DATABASENAME = 'SW_TEST'

 

 

 

CHECK DB 실행 프로시저를 생성하여 정기적으로 진행되는 메인터넌스 작업 때 실행 되도록 Job Agent에 등록하여 사용한다면 정기검사 및 효율적으로 관리가 가능할 듯 하다. 또한 DBA는 문제가 되는 부분만 쿼리 하여 email또는 SMS 등의 보고를 받는다면 문제 발생을 좀더 쉽고 빠르게 파악할 수 있을 듯 하다.

 

2012-11-16 / 강성욱 / http://sqlmvp.kr