데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

No. Subject Author Date Views
1690 SQL Server 성능 대시보드 jevida(강성욱) 2016.09.13 2211
1689 SSMS를 이용한 데이터베이스 복사 하기 jevida(강성욱) 2016.09.13 11392
1688 SQL Server에서 DB2 링크드 서버 연결 방법 jevida(강성욱) 2016.09.13 3273
1687 SQL Server 인덱스 활성 / 비활성 하기 jevida(강성욱) 2016.09.13 2341
1686 파티션 오프셋과 SQL Server에 대한 디스크 할당 단위 크기 jevida(강성욱) 2016.09.13 1679
1685 Read the End of a Large Error Log (에러로그 끝 부분 읽기) jevida(강성욱) 2016.09.13 1299
1684 데이터베이스 여유공간 확인 jevida(강성욱) 2016.09.13 1445
1683 FILE GROUP 간 데이터 이동 jevida(강성욱) 2016.09.13 1601
1682 문서화 되지 않은 sys.fn_PhysLocFormatter 함수 jevida(강성욱) 2016.09.13 946
1681 SQL Server 관리자 전용 연결(DAC) jevida(강성욱) 2016.09.13 3363
1680 파일 접근 권한으로 인한 DB복원 실패 작업 해결하기 jevida(강성욱) 2016.09.13 1920
» DBCC CHECKDB 실행과 히스토리 관리 jevida(강성욱) 2016.09.13 1840
1678 MDF를 이용한 데이터베이스 복원 jevida(강성욱) 2016.09.13 1174
1677 EventID 3041 Error jevida(강성욱) 2016.09.13 1410
1676 데이터베이스 미러 백업 jevida(강성욱) 2016.09.13 1313
1675 SNAPSHOT을 이용한 SQL Server 복원 jevida(강성욱) 2016.09.13 2291
1674 복사 전용 백업(COPY ONLY BACKUP) jevida(강성욱) 2016.09.13 3341
1673 SQL Server 기본 백업 폴더 변경하기 jevida(강성욱) 2016.09.13 4043
1672 sp_helpindex 의 향상된 버전 minsouk 2016.05.17 4561
1671 Spatial Index (공간 인덱스) 생성, 수정, 삭제 jevida(강성욱) 2016.01.11 9236





XE Login