SQL Server 2005/2008에서 트랜잭션 로그의 내용을 확인하는 방법

테이블 데이터의 예기치 않은 삭제와 같은 데이터베이스에서 발생한 이상 현상에 대한 감사 목적으로 SQL Server의 트랜잭션 로그를 분석할 필요가 있을 수 있습니다. 또한, 트랜잭션 로그를 통해 특정 명령 수행시 SQL Server 내부적으로 수행되는 실제 상세 작업에 대해 확인해 볼 필요가 있을 수도 있습니다. 트랜잭션 로그의 내용을 보여 주는 툴이 있다면 이를 사용하면 좋을텐데 이런 툴은 상용 제품이기 때문에 구입이 꺼려지는 것이 사실입니다.

 

SQL Server 2005/2008에서는 트랜잭션 로그의 내용을 볼 수 있는 방법을 제공합니다. 비록 가독성 있는 형태로 보여 주지 못하고 트랜잭션 로그의 구조에 대해서 상당한 지식이 있어야만 제대로 파악할 수 있는 수준의 내용들만을 보여 주지만 아쉬운 대로 대략적인 내용들을 파악해 보기 위해 사용할 수 있습니다.

 

SQL Server 2005/2008에서 트랜잭션 로그의 내용을 확인해 보는 방법은 3가지가 있으며 각각에 대해서 살펴보면 다음과 같습니다.

 

1. DBCC LOG 명령을 사용하는 방법

DBCC LOG 명령의 구문은 다음과 같습니다. 두 번째 파라미터 값은 생략 가능하며 생략시 최소 정보(type=0)만이 표시됩니다. 

DBCC LOG ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

파라미터 정보: 
      dbid 또는 dbname - 트랜잭션 로그를 확인해 보고자 하는 데이터베이스의 이름이나 ID

      type - 출력의 형태를 의미하며 다음 중의 하나를 지정할 수 있음(기본 값은 0)

      0 - minimum information (operation, context, transaction id)

      1 - more information (plus flags, tags, row length, description)

      2 - very detailed information (plus object name, index name, page id, slot id)

      3 - full information about each operation

      4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

     -1 - full information about each operation plus hexadecimal dump of the current transaction log's row,

           plus Checkpoint Begin, DB Version, Max XDESID

 

다음의 예와 같이 실행할 수 있습니다.

DBCC LOG(test)   -- DBCC LOG(test, 0)을 실행한 것과 동일한 효과

DBCC LOG(test, 2)

DBCC LOG(7, -1)   -- 데이터베이스 이름 대신에 데이터베이스 ID를 사용

 

DBCC LOG 명령은 데이터베이스의 전체 트랜잭션 로그가 출력되며 조건 등을 지정하여 출력되는 내용을 필터링할 수 있는 방법이 없으므로 트랜잭션 로그가 큰 경우에는 주의해서 사용해야 합니다.  

 

2. fn_dblog 시스템 함수를 사용하는 방법

fn_dblog 시스템 함수의 구문은 다음과 같습니다. 파라미터로 확인하고자 하는 트랜잭션 로그의 범위를 설정하는 LSN(Log Sequence Number)를 지정할 수 있습니다. LSN은 16진수 또는 10진수 형태의 문자열을 사용할 수 있는데 16진수일 경우에는 문자열의 맨 앞에 '0x'를 추가하여 16진수임을 나타내도록 합니다.

fn_dblog(@start, @end)

파라미터 정보:

     @start - 시작 LSN으로 기본 값인 NULL 지정시 트랜잭션 로그의 처음을 의미함 

     @end - 마지막 LSN으로 기본 값인 NULL 지정시 트랜잭션 로그의 끝을 의미함

 

fn_dblog 시스템 함수는 테이블 형태의 결과를 반환하는 테이블 반환 함수(Table Valued Function)이므로 select문의 from절에 사용합니다. fn_dblog 시스템 함수를 사용한 예는 다음과 같습니다. 

select * from fn_dblog(NULL, NULL)   -- 현재 데이터베이스의 모든 트랜잭션 로그를 출력 

select * from fn_dblog(DEFAULT,DEFAULT)   -- fn_dblog(NULL, NULL)을 호출한 것과 동일한 효과

 

-- 시작 LSN과 마지막 LSN을 16진수 형태로 지정 

select * from fn_dblog('0x00000015:000001ba:0001', '0x00000015:000001ba:0014')

-- 시작 LSN과 마지막 LSN을 10진수 형태로 지정 

select * from fn_dblog('21:442:1', '21:442:20')

 

-- 행을 INSERT하는 로그 레코드만을 추출 

select * from fn_dblog('0x00000015:000001ba:0001', '0x00000015:000001ba:0014')
where Operation = 'LOP_INSERT_ROWS'

-- 특정 테이블에 대한 로그 레코드만을 추출 

select * from fn_dblog('0x00000015:000001ba:0001', '0x00000015:000001ba:0014')
where AllocUnitName = 'dbo.testtable'

  

상기의 예에서 볼 수 있는 바와 같이 fn_dblog 시스템 함수는 일반 테이블에 대한 쿼리문을 실행하듯이 사용 할 수 있으므로 DBCC LOG 명령에 비해서 훨씬 융통성이 큼을 알 수 있습니다. 하지만, fn_dblog 시스템 함수는 현재 사용중인 트랜잭션 로그의 내용만을 확인할 수 있을 뿐 트랜잭션 로그 백업 파일의 내용을 확인할 수 있는 방법은 제공해 주지 못하는 한계가 있습니다.

 

3. fn_dump_dblog 시스템 함수를 사용하는 방법

fn_dump_dblog 시스템 함수는 fn_dblog 시스템 함수가 현재 사용중인 트랜잭션 로그의 내용만을 확인할 수 있다는 한계를 보완해 주는 시스템 함수로 트랜잭션 로그 백업 파일의 내용을 확인할 수 있도록 합니다. fn_dump_dblog 시스템 함수의 구문은 다음과 같습니다. 

fn_dump_dblog
 (@start, @end, @devtype, @seqnum,

  @fname1, @fname2, @fname3, @fname4, @fname5, @fname6, @fname7, @fname8,

  @fname9, @fname10, @fname11, @fname12, @fname13, @fname14, @fname15, @fname16,
  @fname17, @fname18, @fname19, @fname20, @fname21, @fname22, @fname23, @fname24,
  @fname25, @fname26, @fname27, @fname28, @fname29, @fname30, @fname31, @fname32,
  @fname33, @fname34, @fname35, @fname36, @fname37, @fname38, @fname39, @fname40,
  @fname41, @fname42, @fname43, @fname44, @fname45, @fname46, @fname47, @fname48,
  @fname49, @fname50, @fname51, @fname52, @fname53, @fname54, @fname55, @fname56,
  @fname57, @fname58, @fname59, @fname60, @fname61, @fname62, @fname63, @fname64)
파라미터 정보:

     @start - 시작 LSN으로 기본 값인 NULL 지정시 트랜잭션 로그의 처음을 의미함

     @end - 마지막 LSN으로 기본 값인 NULL 지정시 트랜잭션 로그의 끝을 의미함

     @devtype - 백업 파일의 형태를 의미하며 NULL | DISK | TAPE | VIRTUAL_DEVICE 중에 하나의

                      값을 지정할 수 있음. 기본 값은 NULL로 DISK와 동일한 의미임.

     @seqnum - 백업 파일 내에서의 일련 번호를 지정함. 하나의 백업 파일에 여러 개의 백업을 받았을

                      경우 순서 대로 일련 번호가 붙으며 첫 번째 백업의 일련 번호는 1임. 기본 값은 1임.

     @fname1~@fname64 - 백업 미디어 세트(하나의 백업이 여러 개의 백업 파일에 분산되어 저장되도록

                      하는 것)를 구성하는 파일들을 지정하는 데 사용됨(백업 미디어 세트는 최대 64개의

                      파일로 구성될 수 있음). 백업이 하나의 백업 파일에 저장되는 일반적인 경우에는

                      @fname1만을 지정하고 나머지는 기본 값인 NULL을 지정함.

 

fn_dump_dblog 시스템 함수 역시 테이블 형태의 결과를 반환하는 테이블 반환 함수(Table Valued Function)이므로 select문의 from절에 사용합니다. fn_dump_dblog 시스템 함수를 사용한 예는 다음과 같습니다. 

-- 트랜잭션 로그 백업(Transaction Log Backup) 파일로부터 전체 트랜잭션 로그를 추출 

select * from fn_dump_dblog(default, default, default, default, 'c:\backup\test.trn',
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default)

-- DEFAULT 키워드 대신에 명시적으로 파라미터 값을 지정하여 상기와 동일한 결과를 가져옴 

select * from fn_dump_dblog(null, null, 'disk', 1, 'c:\backup\test.trn',
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null, null, null, null, null, null, null, null,
                            null, null, null)

 

-- 전체 백업(Full Backup) 파일에 포함되어 있는 트랜잭션 로그의 전체를 추출 

select * from fn_dump_dblog(null, null, 'disk', 1, 'c:\backup\test.bak',
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default)  

 

-- 시작 LSN과 마지막 LSN을 16진수 형태로 지정하여 트랜잭션 로그 백업 파일의 일부를 추출

 select * from fn_dump_dblog('0x00000015:00000115:0010', '0x00000015:00000115:0015', 'disk', 1,

                            'c:\backup\test.trn',
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default)   

 

-- 트랜잭션 로그 백업 파일의 LSN 범위 내에서 행을 INSERT하는 로그 레코드만을 추출 

select * from fn_dump_dblog('0x00000015:00000115:0010', '0x00000015:00000115:0015', 'disk', 1,

                            'c:\backup\test.trn',
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default, default, default, default,
                            default, default, default)
where Operation = 'LOP_INSERT_ROWS'

 

 

지금까지 SQL Server 2005/2008에서 트랜잭션 로그의 내용을 확인해 보는 방법에 대해서 살펴보았습니다. 상기의 설명 중에 언급된 LSN(Log Sequence Number)와 Log Operation에 대해 간단하게 살펴보면 다음과 같습니다.

 

LSN(Log Sequence Number)는 로그 레코드마다 유일하게 부여되는 일련 번호로 10바이트로 구성됩니다. LSN은 세부적으로 다음과 같이 구분됩니다. VLF(Virtual Log File)은 truncation의 단위가 되는 논리적인 구분으로 하나의 물리적인 트랜잭션 로그 파일은 여러 개의 VLF로 구성됩니다. 각 VLF는 또 다시 여러 개의 로그 블록(Log Block)으로 구성됩니다. 하나의 로그 블록에는 여러 개의 로그 레코드가 저장되며 각 로그 레코드가 저장된 위치를 가리키는 Slot Array가 로그 블록에 존재합니다. Slot ID는 로그 블록에 위치하는 Slot Array의 Index 값으로 이를 통해 로그 레코드의 시작 Offset을 찾아낼 수 있습니다. 따라서, VLF 번호와 Log Block의 Offset 값, Slot ID를 조합하면 특정 로그 레코드의 위치를 찾을 수 있으며 그런 의미에서 LSN은 로그 레코드에 대한 포인터 기능을 수행하는 값임을 알 수 있습니다.

Field	Size	Offset	Description
m_fSeqNo	4	0x00	VLF's Sequence Number
m_blockOffset	4	0x04	Offset of log block DIV 512
m_slotId	2	0x08	Slot ID

 

Log Operation은 로그 레코드(Log Record)의 유형을 의미합니다. 로그 레코드는 다양한 Log Operation 값을 가질 수 있는데 Log Operation에는 다음과 같은 값들이 있습니다.

Field	Code	Description
LOP_FORMAT_PAGE	1	Format of a page. Context tells page type.
LOP_INSERT_ROWS	2	Insertion of 1 or more rows on a page.
LOP_DELETE_ROWS	3	Deletion of 1 or more rows on a page.
LOP_MODIFY_ROW	4	Modification of 1 row on a page.
LOP_MODIFY_HEADER	5	Modification of page header.
LOP_MODIFY_COLUMNS	6	Modification of disjoint regions in a row.
LOP_SET_BITS	7	Sets range of bits to 1 or 0.
LOP_DELTA_SYSIND	9	Delta to sysindexes page/row counts.
LOP_SET_FREE_SPACE	10	Change free space value for a page.
LOP_DELETE_SPLIT	11	Deletion of rows on a page being split.
LOP_UNDO_DELETE_SPLIT	12	Compensation for LOP_DELETE_SPLIT.
LOP_EXPUNGE_ROWS	13	Redo only removal of ghost rows.
LOP_FILE_HDR_MODIFY	16	Change size in file header. Shrink file.
LOP_SET_GAM_BITS	17	Clear several bits in a GAM, SGAM, IAM, DCM, or BCM bitmap.
LOP_BEGIN_XACT	128	First record generated by xact.
LOP_COMMIT_XACT	129	Commit. Transaction ended.
LOP_ABORT_XACT	130	End of rollback. Transaction ended
LOP_PREP_XACT	131	Transaction prepared.
LOP_MARK_SAVEPOINT	132	Marks beginning of a named savepoint.
LOP_FORGET_XACT	133	Notes that outcome of cross-db xact can be forgotten.
LOP_CREATE_FILE	134	Insert into sysfiles1, and create new OS file.
LOP_DROP_FILE	135	Mark deferred drop in sysfiles1, and delete file.
LOP_MARK_DDL	136	Marks begin/end of DDL operation on object.
LOP_BEGIN_CKPT	150	Beginning of checkpoint.
LOP_XACT_CKPT	152	Transaction list record for checkpoint.
LOP_END_CKPT	153	Checkpoint complete.
LOP_BUF_WRITE	154	Logs flush of multiple data pages.
LOP_IDENTITY_TYPE	155	Column info for an identity column.
LOP_BEGIN_RECOVERY	160	Marks start of undo phase of recovery.
LOP_END_RECOVERY	161	Marks end of recovery for LOP_BEGIN_RECOVERY.
LOP_NONLOGGED_OP	162	Records that a non-logged operation was performed.
LOP_SORT_BEGIN	170	Marks start of sort for index creation.
LOP_SORT_END	171	Marks the end of a sort for index creation.
LOP_SORT_EXTENT	172	Records sort extent allocation.
LOP_CREATE_INDEX	173	Marks end of allocation of 1st extent.
LOP_DROP_INDEX	174	CLR for first extent allocation.
LOP_SORT_MEMORY	175	Holds memory grant information for sort.
LOP_REPL_COMMAND	200	Special replication command.
LOP_BEGIN_UPDATE	201	Marks beginning of update sequence.
LOP_END_UPDATE	202	Marks end of update sequence.
LOP_TEXT_POINTER	203	Logs text pointer and corresponding row.
LOP_TEXT_INFO_BEGIN	204	Marks start of text op for replication.
LOP_TEXT_INFO_END	205	Marks end of text op for replication.
LOP_REPL_NOOP	206	Used to increment the end-of-log LSN.
LOP_TEXT_VALUE	207	Contains value of text column.
LOP_SHRINK_NOOP	211	Consume log space during a shrink operation.

 

 

과거에는 SQL Server 트랜잭션 로그의 내용을 보여 주는 상용 프로그램으로 Lumigent사의 Log Explorer가 많이 사용되었습니다. 하지만, Log Explorer는 현재 더 이상 판매되고 있지 않기 때문에 사용 가능한 옵션이 아닙니다. 현재는 ApexSQL사의 ApexSQL Log(http://www.apexsql.com/sql_tools_log.aspx)가 많이 사용되고 있으며 ApexSQL Log는 SQL Server 2008까지 모든 버전의 SQL Server를 지원합니다.

 

고객의 입장에서는 Microsoft사에서 트랜잭션 로그의 내용을 확인해 볼 수 있는 툴을 함께 제공해 준다면 좋을 듯 한데 현재로써는 언제 그것이 현실화될지 기약할 수 없는 것이 아쉽습니다. 다음 버전의 SQL Server가 출시될 때에는 이런 툴이 함께 제공되기를 기대해 보면서 글을 맺을까 합니다.