背景

完全復旧モデルで稼働しているプロダクション環境のDBのメンテ作業（インデックスのオフライン再構築）について高速に完了させるために、一時的に一括ログ復旧モデルに変更したいことがありました。
その際、両者の挙動の違いなどについて検証したので、その時の結果をご紹介します。

検証準備

１．テスト用の完全復旧モデルのDBを作成
２．テストテーブル作成、テスト用にレコードINSERT

create table [dbo].[Test] (
	[PK] [int] IDENTITY(1, 1) not null
	,[val1] [int] null
	,[val2] [nvarchar](10) null
	,constraint [PK_Test] primary key clustered ([PK] asc)
	) on [PRIMARY]
go

insert into test (val1, val2) values (1, 'a'), (2,'b')

３．トランザクションログの中身を見る

select *From sys.fn_dblog(null, null)

→トランザクションログの中身がSELECTされることを確認

４．チェックポイントうって再実行

checkpoint

select * from sys.fn_dblog(null, null)

→なくなった。本来、完全復旧モデルの場合はトランザクションログのバックアップとらないと消えないはずなのに、チェックポイントうって消える挙動になってる。

これは、初回の完全バックアップ終了後にはじめて設定した復旧モデルの動作となるため。それまでは単純復旧モデル相当の挙動になる。

５．完全バックアップとる

backup database test to disk='nul'

※nullだと、ファイルとしては出力しない。

６．再度テーブルを更新

insert into test (val1, val2) values (1, 'a'), (2,'b')
checkpoint
select * from sys.fn_dblog(null, null)

→今度は切り捨てられてない
この時点で、完全復旧モデルにおける挙動の確認の準備が整ったことになる。

ここで以下のクエリを実行

-- トランザクションログの論理的なファイル構成を確認できる
dbcc loginfo('test')

status <> 0 のものが使用中。
領域拡張してみる

ALTER DATABASE [test] MODIFY FILE ( NAME = N'test_log', SIZE = 3 MB )
dbcc loginfo('test')

→1MB拡張で、論理的にVLF（Virtual Log File）が4分割されている。
一度の拡張につき、サイズに応じて４～16分割される。そのため、頻繁に拡張が発生していると、内部的にVLFがかなり細かく分割される。そうすると、トランザクションログのバックアップおよびリストアの効率が低下するので注意。

７．100行INSERT

insert into test (val1, val2) values (1, 'a'), (2,'b')
go 100

→ここを起点にしたいので、DB完全バックアップを取得

--C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup っていうのがデフォルトのバックアップディレクトリ。ディレクトリ省略すると自動でここに保存される
backup database test to disk='test_bk_20190704.bak'

再度100行ISNERT

insert into test (val1, val2) values (1, 'a'), (2,'b')
go 100

今度はログバックアップ

--既存のファイルがある場合は、追記される
backup log test to disk='test_bk_20190704.bak'

ここで一度一括ログ復旧モデルに変更

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

インデックスREBUILD

use test
alter index all on Test Rebuild

→これで、一括ログ復旧モデルの状態で、インデックスのメンテを実施したことになった。

この状態で再度データをいれる。

insert into test (val1, val2) values (1, 'a'), (2,'b')
go 100

ここでトランザクションログのバックアップ取得

--既存のファイルがある場合は、追記される
backup log test to disk='test_bk_20190704.bak'

→ここで復旧モデルをFULLに変更

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT
GO

この状態で、一括ログ復旧モデルで行った、インデックス再構築＋100行INSERT＋トランザクションログのバックアップを実施

インデックス再構築

use test
alter index all on Test Rebuild

→これで、完全復旧モデルの状態で、インデックスのメンテを実施したことになった。
この状態で再度データをいれる

insert into test (val1, val2) values (1, 'a'), (2,'b')
go 100

ここでトランザクションログのバックアップ取得

--既存のファイルがある場合は、追記される
backup log test to disk='test_bk_20190704.bak'

以上で検証準備は終了です。

完全復旧モデルと一括ログ復旧モデルにおける違い

次に、完全復旧モデルと一括ログ復旧モデルにおける違いを見ていきます。

--リストアの実行はしないけど、どういったバックアップが含まれているか調べる
restore headeronly from disk='test_bk_20190704.bak'

→Backup Type
1:完全
2:トランザクションログ
5:差分

→HasBulkLoggedData
0:最小ログ記録の操作が入っていない
1:最小ログ記録の操作が入っている（一括ログ復旧モデルでインデックス再構築後に取得したログがこれに該当する）

SSMSから復元の画面にいく

→「タイムライン」を押すと、ポイントインタイムリカバリできる画面になる。一括ログ復旧モデル時のバックアップの時間帯だけ選択できない。

原因は、HasBulkLoggedDataが1になっている期間のトランザクションログバックアップは、ポイントインタイムリストアができない。という仕様。

↑でいうと、2行目と3行目をみて、13:39:15～13:43:12までの間はポイントインタイムリストアができない。

HasBulkLoggedData=1から0になるタイミング

最小ログ記録操作を行った後の初回のトランザクションログバックアップ取得により、HasBulkLoggedData=0になる。

そのため、最小ログ記録操作後の初回トランザクションログバックアップを取得するまではポイントインタイムリストア不可能なので、可能な限りはやくトランザクションログバックアップを取得したほうが良い。

注意点

最小ログ記録操作を行った後の初回のトランザクションログバックアップ取得は、通常のトランザクションログバックアップよりファイルサイズが大きくなる。

実際に比較してみる
■ 完全

use test
alter index all on Test Rebuild
--既存のファイルがある場合は、追記される
backup log test to disk='test_bk_20190704.bak'

ここで一度一括ログ復旧モデルに変更

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

■ 一括ログ復旧

use test
alter index all on Test Rebuild
--既存のファイルがある場合は、追記される
backup log test to disk='test_bk_20190704.bak'

一括ログ復旧モデルだと、ログファイルに加えて、データファイルの一部（変更があったページだけ）のバックアップも実施されている。
最小ログ記録により、トランザクションログのサイズ自体は6ページから4ページへと減少している。

SQL Serverは、最小ログ記録でデータファイルが更新された場合は、該当のデータページをBCM(Bulk Changed Map)で管理しており、そこに保存される。

そこで、一括操作で変更があったページは追えるようになっている。BCMで最小ログ記録モードによって変更されたページを取得し、それをバックアップに含めている。

最小ログ記録だと、トランザクションログには、どういった変更が行われたかという情報が書き込まれないが、バックアップ時に変更されたデータを保存しておく必要はあるため、BCMから変更されたデータページを取得し、それを含めてあげる。

それにより、処理が終了したタイミングのデータベースを復元できることを保障する。

ちなみに、一括ログ復旧モデルでも、最小ログ記録となる操作が行われていなければ、バックアップ時にログだけがバックアップされる。また、HasBulkLoggedDataも0になる。

insert into test (val1, val2) values (1, 'a'), (2,'b')
go 100

backup log test to disk='test_bk_20190704.bak'

restore headeronly from disk='test_bk_20190704.bak'

→リカバリモデルも分かる。

復旧モデル変更時の挙動

最後に、復旧モデル変更時の挙動を確認してみました。

backup log test to disk='test_bk_20190704.bak'

USE [master]
GO
ALTER DATABASE [test] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

select * from sys.fn_dblog(null, null)

↓DBの内部情報を書き換えてる。

「完全→一括ログ」および「一括ログ→完全」の切り替えは一般的にオンラインでやるもの。
ユーザーのトランザクションへの影響は無い。

また、最小ログ記録が行われない限り、両者の挙動の違いはない。

まとめ

完全復旧モデルと一括ログ復旧モデルの挙動の違いについて、検証した結果をご紹介しました。
検証の結果を踏まえて、プロダクション環境で無事「完全復旧モデル→一括ログ復旧モデル→メンテ作業→完全復旧モデル」という流れを実施することができました。