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 등의 보고를 받는다면 문제 발생을 좀더 쉽고 빠르게 파악할 수 있을 듯 하다.
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
