본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

데이터베이스 복구 모델별 로그 증가 테스트

by ㏈ª ☞ β┖υΕJini.κR 2011. 4. 26.

 

USE MASTER

GO

 

--CHECKPOINT 발생하지않도록 INTERVAL 를최대치로변경

EXEC SP_CONFIGURE ' INTERVAL (MIN)', 32767

GO

RECONFIGURE WITH OVERRIDE

GO


--
데이터베이스생성

CREATE DATABASE FULLDB ON  PRIMARY

( NAME = N'FULLDB', FILENAME = N'D:\FULLDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'FULLDB_LOG', FILENAME = N'D:\FULLDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

CREATE DATABASE SIMPLEDB ON  PRIMARY

( NAME = N'SIMPLEDB', FILENAME = N'D:\SIMPLEDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'SIMPLEDB_LOG', FILENAME = N'D:\SIMPLEDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

CREATE DATABASE BULKLOGGEDDB ON  PRIMARY

( NAME = N'BULKLOGGEDDB', FILENAME = N'D:\BULKLOGGEDDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'BULKLOGGEDDB_LOG', FILENAME = N'D:\BULKLOGGEDDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

--전체복구모델변경

ALTER DATABASE FULLDB SET RECOVERY   FULL

GO

 

--단순복구모델변경

ALTER DATABASE SIMPLEDB SET RECOVERY   SIMPLE

GO

 

--BULK LOGGED 모델데이터베이스변경

ALTER DATABASE BULKLOGGEDDB SET RECOVERY  BULK_LOGGED

GO

 

DBCC SQLPERF(LOGSPACE)

GO

 

/*

DATABASE NAME          LOG SIZE (MB) LOG SPACE USED (%) STATUS

---------------------- ------------- ------------------ -----------

FULLDB                           9.992188            2.946638     0

SIMPLEDB                         9.992188            2.961298     0

BULKLOGGEDDB               9.992188            2.961298     0

*/

 

--전체복구모델데이터입력

CREATE TABLE FULLDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO FULLDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

 

--단순복구모델데이터입력

CREATE TABLE SIMPLEDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO SIMPLEDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

--BULK LOGGED 모델데이터입력

CREATE TABLE BULKLOGGEDDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))

GO

 

INSERT INTO BULKLOGGEDDB.DBO.SQLER VALUES(REPLICATE('A', 100))

GO 10000

 

DBCC SQLPERF(LOGSPACE)

GO

/*

DATABASE NAME          LOG SIZE (MB) LOG SPACE USED (%) STATUS

---------------------- ------------- ------------------ -----------

FULLDB                           9.992188            52.92709     0

SIMPLEDB                         9.992188            52.94175     0

BULKLOGGEDDB               9.992188            52.94175     0

*/