トランザクションログの切り捨てを行うためには、トランザクションログのバックアップまたは、復旧モードを [単純] に
設定する必要があります。
ただし例外が一つだけあり、初回のデータベースバックアップ時にはトランザクションログが切り捨てられるようです。
まずはデータを挿入して、トランザクションログが使用されている状態にしてみました。
– トランザクションログの使用状況の取得 –
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.
[…] 参考にしたサイト SQL Server の初回データベースバックアップとログの切り捨てについて […]
■■ 2010/01/09(土) ■■ | 無題
14 7月 14 at 11:45