-- 글제목 : 트랜잭션 열린넘 있으면 로그백업할때 사이즈가 커진다

-- 작성자 : 차주언(narsas@naver.com)
-- 작성일 : 2009.12.02

위의 이슈가 왜 생겼냐면 몇몇 APP에서 BEGIN TRAN걸고 도망가서 그 피해가 DB에 있기 때문입니다.

일단 석이님 글 좀 보고.. ㅋㅋ BEGIN TRAN 걸고 도망간넘은 잘 찾아서 해결해야합니다

예제는 송혁님의 데이터 생성쿼리 걍 펌해서 썻습니다.

drop database [LogTest1]

go

drop database [LogTest2]

go

/****** Object:  Database [LogTest]    Script Date: 12/02/2009 17:49:22 ******/

CREATE DATABASE [LogTest1] ON  PRIMARY

( NAME = N'LogTest1', FILENAME = N'D:\MSSQL\DATA\LogTest1.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

 LOG ON

( NAME = N'LogTest_log1', FILENAME = N'D:\MSSQL\DATA\LogTest_log1.LDF' , SIZE = 1000MB , MAXSIZE = 2048GB , FILEGROWTH = 100MB)

GO

/****** Object:  Database [LogTest]    Script Date: 12/02/2009 17:49:22 ******/

CREATE DATABASE [LogTest2] ON  PRIMARY

( NAME = N'LogTest2', FILENAME = N'D:\MSSQL\DATA\LogTest2.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

 LOG ON

( NAME = N'LogTest_log2', FILENAME = N'D:\MSSQL\DATA\LogTest_log2.LDF' , SIZE = 1000MB , MAXSIZE = 2048GB , FILEGROWTH = 100MB)

GO

dbcc loginfo

-- 각각VLF 8개씩

USE LogTest1

go

CREATE TABLE tblx (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)

GO

INSERT INTO tblx

SELECT TOP 2000000

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1))

FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3

dbcc loginfo

backup database LogTest1 to disk = 'd:\backup\logTest1.bak' with init

backup log LogTest1 to disk = 'd:\backup\logTest1.trn' with init

--------

USE LogTest2

go

CREATE TABLE tblx (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)

GO

begin tran

INSERT INTO tblx

SELECT TOP 2000000

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1))

FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3

dbcc loginfo

-- 다른세션에서

backup database LogTest2 to disk = 'd:\backup\logTest2.bak' with init

backup log LogTest2 to disk = 'd:\backup\logTest2.trn' with init

-- commit tran 하고다시백업실험

commit tran

backup database LogTest2 to disk = 'd:\backup\logTest3.bak' with init

backup log LogTest2 to disk = 'd:\backup\logTest3.trn' with init