SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Archive for 11月 24th, 2009

SQL Server の初回データベースバックアップとログの切り捨てについて

with one comment

SQL Server のデータベースバックアップですが、完全バックアップではトランザクションログの切り捨てはされません。
トランザクションログの切り捨てを行うためには、トランザクションログのバックアップまたは、復旧モードを [単純] に
設定する必要があります。
ただし例外が一つだけあり、初回のデータベースバックアップ時にはトランザクションログが切り捨てられるようです。
まずはデータを挿入して、トランザクションログが使用されている状態にしてみました。
– トランザクションログの使用状況の取得 –

DECLARE @logspace TABLE(
??? DatabaseName sysname,
??? LogSize int,
??? LogSpaceUsed int,
??? Status int
)
INSERT INTO @logspace EXEC (‘DBCC SQLPERF(”LOGSPACE”)’)SELECT * FROM @logspace WHERE DatabaseName = ‘WORK’

?
– 実行結果? –

DatabaseName LogSize LogSpaceUsed Status
WORK 110 77 0

?
現在は 110 MB のトランザクションログに対して 77 % が使用されている状況です。
完全バックアップはまだ取得していませんので、差分バックアップのベース LSN も設定はされていません。
– 差分バックアップのベース LSN の取得 –

SELECT
??? [file_id],
??? [name],
??? [differential_base_lsn]
FROM
??? [sys].[master_files]
WHERE
??? [database_id] = DB_ID(N’WORK’)

?
– 実行結果? –

file_id name differential_base_lsn
1 WORK NULL
2 WORK_log NULL

?
それでは初回の完全バックアップを取得してみます。
– 完全バックアップの取得 –

BACKUP DATABASE [WORK] TO?
DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBackupWORK.bak’
WITH FORMAT, INIT,? NAME = N’WORK-完全 データベース バックアップ’,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

?
バックアップ取得後に DBCC SQLPERF(‘LOGSPACE’) を実行して再度、トランザクションログの使用状況を取得します。
– 実行結果 –

DatabaseName LogSize LogSpaceUsed Status
WORK 110 3 0

?
初回のバックアップではログが切り捨てられているのが確認できます。
# 77 % の利用から 3 % の利用となっています。
完全バックアップを取得したので、差分バックアップのベース LSN も設定がされています。
– 実行結果? –

file_id name differential_base_lsn
1 WORK 62000000412300000
2 WORK_log NULL

?
この状態で再度データを挿入して、データベースのバックアップを取得し、ログの使用状況を確認してみます。
– 実行結果 (バックアップの取得前) –

DatabaseName LogSize LogSpaceUsed Status
WORK 110 79 0

?
– 実行結果 (バックアップの取得後) –

DatabaseName LogSize LogSpaceUsed Status
WORK 110 79 0

?
– 実行結果 (バックアップ取得後の差分バックアップのベース LSN) –

file_id name differential_base_lsn
1 WORK 100000000353400043
2 WORK_log NULL

?
2 回目以降の完全バックアップではトランザクションログの切り捨てはされていないことが確認できます。
差分バックアップのベース LSN は完全バックアップを取得したので更新されています。
# バックアップを取得したことをあらわそうと思い情報を取得しています。
トランザクションログのバックアップは完全バックアップが存在していない状況では取得することができません。
初回の完全バックアップではそれまでのトランザクションログのバックアップが存在しておらず、トランザクション
ログの切り捨てが行われてもログチェーンとしては問題がないためこのような動作になっているのかと。
SQL Server に慣れていない人が完全バックアップでトランザクションログが切り捨てられると考えてしまう理由が、
この動きにあるのかな~と電車の中でふと思ったので投稿してみました。
2010/12/9 追加
SQL CAT のブログで本件について記載されていました。
Transaction Log size does not match the size of the data being loaded.

Written by Masayuki.Ozawa

11月 24th, 2009 at 1:48 pm

Posted in SQL Server