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

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

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/

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» SQL Server 트랜잭션 로그 읽기 jevida(강성욱) 2016.10.12 2115
1892 MaxBCPThreads에 따른 BCP 병렬출력 jevida(강성욱) 2016.10.11 1090
1891 확장이벤트와 dm_os_wait_stats 대기유형 매핑 jevida(강성욱) 2016.10.11 1894
1890 장기 트랜잭션 확인 및 경고 설정 jevida(강성욱) 2016.10.11 1428
1889 SQL 데이터 수집툴 DiagManager 사용법 jevida(강성욱) 2016.10.11 1324
1888 Windows Server 2012 R2 그룹 관리 서비스 계정 및 SQL 서버 jevida(강성욱) 2016.10.11 1834
1887 Kerbros 구성 관리자 툴 jevida(강성욱) 2016.10.11 1164
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2425
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1364
1884 TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3369
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2751
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 879
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1168
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 882
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 998
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1363
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 1833
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 959
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1046
1874 AppDomain unloading 오류 로그 – CLR 오류 jevida(강성욱) 2016.10.11 1188





XE Login