SQL Server 트랜잭션 로그 읽기

SQL Server 트랜잭션 로그 읽기

 

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

 

데이터베이스에서 발생하는 행위는 트랜잭션 로그 라는 곳에 기록된다. 문서화되지 않은 기능 fn_dblog 함수를 사용하여 트랜잭션 로그의 정보를 읽어보자.

 

Fn_dblog는 트랜잭션의 시작 LSN과 종료 LSN을 필요로 한다. NULL은 기본값으로 트랜잭션 로그 파일의 모든 로그 레코드를 반환한다.

 

실습을 위해 데이터베이스를 생성한다.

--Create DB. USE [master]; GO CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] ( [Sr.No] INT IDENTITY, [Date] DATETIME DEFAULT GETDATE (), [City] CHAR (25) DEFAULT 'Seoul');

 

다음 스크립트를 실행하여 데이터베이스 및 테이블을 작성하는데 걸린 어떤 과정과 단계를 확인할 수 있다.

USE ReadingDBLog; GO select COUNT(*) from fn_dblog(null,null)

 

 

데이터베이스 생성과 테이블을 생성하는데 총 176행의 정보가 생성된 것을 확인 할 수 있다. 다음 스크립트를 통해 데이터베이스 생성, 테이블 생성에 기록된 트랜잭션 로그 파일 데이터를 확인하여 보자.

USE ReadingDBLog; GO select [Current LSN], [Operation], [Transaction Name], [Transaction ID], [Transaction SID], [SPID], [Begin Time] FROM fn_dblog(null,null)

 

 

LOP_BEGIN_XACT는 트랜잭션의 시작을 의미한다. 작업 열은 우리가 삽입, 업데이트, 삭제, 축소, 잠금, 페이지 할당 등과 같이 수행되는 작업을 알려준다.

 

데이터의 삽입, 업데이트, 삭제 등의 DML 스크립트를 사용하여 트랜잭션 로그 파일에 기록하는 방법을 확인하여 보자. 이 작업을 수행하는 동안 페이지가 할당 또는 해제되는 방법을 추적할 수 있다.

USE ReadingDBLog go INSERT INTO Location DEFAULT VALUES ; GO 100 GO UPDATE Location SET City='New Delhi' WHERE [Sr.No]<5 GO DELETE Location WHERE [Sr.No]>90 Go

 

데이터 입력 수정, 삭제가 완료되었으면 트랜잭션 로그 파일을 확인해 보자.

USE ReadingDBLog go SELECT [Current LSN], [Transaction ID], [Operation], [Transaction Name], [CONTEXT], [AllocUnitName], [Page ID], [Slot ID], [Begin Time], [End Time], [Number of Locks], [Lock Information] FROM sys.fn_dblog(NULL,NULL) WHERE Operation IN ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

 

 

트랜잭션을 시작하고 힙테이블 dbo.location 에 데이터를 입력하고 트랜잭션을 완료하는 것을 확인 할 수 있다. UPDATE문과 DELETE 구문도 확인 할 수 있다.

 

트랜잭션 로그에서 SQL Server의 페이지 분할이나 페이지 분할 횟수 등 내부 동작도 확인할 수 있다.

 

다음 스크립트를 실행하여 스플릿 정보를 확인한다.

USE ReadingDBLog go --Get how many times page split occurs. SELECT [Current LSN], [Transaction ID], [Operation], [Transaction Name], [CONTEXT], [AllocUnitName], [Page ID], [Slot ID], [Begin Time], [End Time], [Number of Locks], [Lock Information] FROM sys.fn_dblog(NULL,NULL) WHERE [Transaction Name]='SplitPage' GO

 

 

위에서 확인된 스플릿의 트랜잭션ID를 사용하여 추적할 수 있다.

--Get what all steps SQL Server performs during a single Page Split occurrence. SELECT [Current LSN], [Transaction ID], [Operation], [Transaction Name], [CONTEXT], [AllocUnitName], [Page ID], [Slot ID], [Begin Time], [End Time], [Number of Locks], [Lock Information] FROM sys.fn_dblog(NULL,NULL) WHERE [Transaction ID]='0000:000002dd'

 

 

 

트랜잭션 로그는 백업의 상호 관계에서는 백업을 진행 할 경우 트랜잭션 로그가 잘리게되어 로그 파일을 축소 할 수 있다.

다음 스크립트를 통하여 백업 후 트랜잭션 로그의 행 숫자가 줄어든 것을 확인 할 수 있다.

SELECT COUNT(*) FROM fn_dblog(null,null) GO   BACKUP DATABASE ReadingDBLog TO DISK = 'c:\SQL_DATA\ReadingDBLog_Full.bak' GO   SELECT COUNT(*) FROM fn_dblog(null,null) GO

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/

 

 

2013-12-04 / 강성욱 / http://sqlmvp.kr