第１回 「データ／ログの格納方法とデータベース復旧モデルについて」～ システム管理 ～

NEC

Eラーニング事業部

鈴木 智行

2002 年 4 月 1 日

概要 : SQL Server 2000 は 最新のデータベーステクノロジーを搭載しつつ、"Easy to Use" を実現したデータベース管理システムです。細かいことがわからなくてもある程度は運用できますが、最低限わからなければいけないポイントはたくさんあります。今回のコラムでは、その中でも特にデータ／ログ領域およびバックアップ／復元機能に焦点を当てて解説します。

目次

1.1. 複数のデータベースファイルへのデータの格納
1.2. ファイルグループ
1.3. トランザクションログファイルへのログの格納
1.4. データベース復旧モデル

1.1. 複数のデータベースファイルへのデータの格納

SQL Server 2000 のデータベースにはデータベースファイルとトランザクションログファイルが少なくとも各 1 つ以上必要です。データベースファイルはデータやインデックスデータはもちろん、テーブル、ストアドプロシージャ、ユーザー情報などのデータベース構成要素を含んでいます。それに対し、トランザクションログファイルはデータベース内で行われたあらゆる変更と、それぞれの変更を実行したトランザクションを記録しています。

以上のようにデータベースファイルとトランザクションログファイルには格納される情報が異なり、同じファイルに混在することは決してありません。

また、それぞれのファイルは複数実装した場合に格納の仕方が異なります。

例えば表 1-1-1 のようにデータベースとテーブルを作成します。

データベース:testdb
	ファイル名	サイズ (MB)
データベースファイル	testdb_Data1 testdb_Data2	2 2
トランザクションログファイル	testdb_Log1 testdb_Log2	2 2
テーブル:testtable
列名	データ型	長さ
Column1	int	4
Column2	char	10
Column3	int	4

表 1-1-1 **　データベースとテーブルの構成**

作成されたテーブル (testtable) に対し、以下の Transact-SQL を実行します。

use testdb
declare @a int ,@sum int
set @a = 1
set @sum = 0
while @a < 101
begin
 set @sum = @sum + @a
 insert testtable values(@a,'テスト',@sum)
 set @a = @a + 1
end

実行終了後、SQL Enterprise Manager を使用し、データベースファイルの 2 個目 (testdb_Data2) を削除してみます。

図 1-1-2 **　データベースファイルの** 2 個目 (testdb_Data2) を削除

上記の SQL では 100 件のデータが格納され、合計サイズは単純に計算して、(4 + 10 + 4 ) × 100 = 1800 (B) = 1.8 (kB) になります。管理情報などの領域を考慮にいれても 2 MB を超えることはありません。

データベースファイルは 1 個のファイル（testdb_Data1） だけで十分に格納できる容量のはずですが、図 1-1-2 のように testdb_Data2 は削除できませんでした。

以上からデータベースファイルにはシーケンシャルにデータを格納しないことがわかります。

1.2. ファイルグループ

データベースファイルを複数実装した場合、データベースファイル名を指定してデータを格納することはできません。極端に言えば複数のデータベースファイルがあったとしても、よく使うデータが 1 つのデータベースファイルに格納されてしまい、アクセスが集中してしまうことも考えられます。

これを回避するために、SQL Server 2000 ではファイルグループを使用するとデータの格納場所を制御することができます。ファイルグループは SQL Server 6.5 以前のセグメントと同じような機能を提供します。

ファイルグループは表 1-2-1 のように 3 つのファイルグループがあります。

種類	名前	機能
プライマリファイルグループ	PRIMARY(固定)	プライマリデータベースファイル (.MDB) が含まれる
ユーザー定義ファイルグループ	任意	複数のデータベースファイルを含むことができる
デフォルトファイルグループ	－	ファイルグループを指定せずにデータベースオブジェクトを作成した場合の既定のファイルグループ (インストール直後は PRIMARY)

表 1-2-1 **　ファイルグループの種類**

例えば SQL Enterprise Manager を使用して、新しいファイルグループ (UserFileGroup1) を作成し、そのファイルグループに属するように testtable テーブルを作成します。

図 1-2-2 **　新しいファイルグループ** (UserFileGroup1) を作成

図 1-2-3 **　新しいファイルグループ** (UserFileGroup1) にデータベースファイルの 2 個目 (testdb_Data2) を追加する

図 1-2-4 **　新しいファイルグループ** (UserFileGroup1) にテーブル (testtable) を追加する

以上の指定を行えば、testtable のデータは必ずデータベースファイルの 2 個目 (testdb_Data2) に格納されます。ただし、同一ファイルグループに複数のデータベースファイルが存在した場合には、それぞれのデータベースファイルに対し、proportional **に格納されます。**1-1 は testdb_Data2 が testdb_Data1 と同じ PRIMARY ファイルグループに属していたことが理由で、testdb_Data2 にデータが存在したため削除できなくなるというケースでした。このケースの場合、データベースファイルを削除するためには、図 1-2-5 のようにデータベースファイルの圧縮 (DBCC shrinkfile) 機能を用いて、ファイルを空にすること (empty オプション) でデータベースファイルは削除可能となります。

図 1-2-5 **　データを同一ファイルグループの他のファイルに移動**

1.3. トランザクションログファイルへのログの格納

1-1 1 の SQL 文を実行した状態で、SQL Enterprise Manager を使用してトランザクションログファイルの 2 個目 (testdb_Log2) を削除してみます。

図 1-3-1 **　トランザクションログファイルの** 2 個目を削除

トランザクションログは SQL Server 6.5 以前では syslogs システムテーブルに格納されており、データベースファイルと同じ格納方法でしたが、SQL Server 2000 ではシリアルレコードとしてシーケンシャルに格納され、データベースファイルと異なる格納方法がとられるようになりました。したがって図 1-3-1 の場合、1 個目だけでログ容量は十分なのでトランザクションログファイルの 2 個目は削除可能となります。

トランザクション ログには、マーク名、説明、データベース、ユーザー、日時情報、および実行された論理操作または修正されたデータの前後のイメージのいずれかが記録され、それらは LSN (Log Sequence Number) によって識別されます。

1.4. データベース復旧モデル

トランザクションログは特に何もしなければ増加の一途をたどります。SQL Server 2000ではトランザクションログファイルは自動拡張の設定をしている限り、実際のログ容量が初期設定サイズより多くなったとしても、エラーは発生せずにデータベースを運用することが可能です。管理者にとってエラーが発生しないので、とてもありがたいのですが自動拡張にも限界があります。例えば最大サイズを無制限にしていたとしても、トランザクションログファイルが置かれているドライブの空き容量より大きくなることはありません。したがってデータベースを運用する場合はログサイズを適切な量に保つことが重要ポイントになります。ここではその解決策の1つとしてデータベース復旧モデルを説明します。

データベース復旧モデルはデータベースオプションで設定します(図1-4-1)。

図 1-4-1 **　データベース復旧モデルの設定**

SQL Server 2000 からは Transact-SQL を使用する場合、Alter Database ステートメントの SET オプションで設定を行い、DATABASEPROPERTYEX 関数で現在の設定情報を取得します。

このデータベース復旧モデルは SQL Server 2000 からの新しい設定ですが、新しい考え方ではありません。SQL Server 7.0 以前でも同様な設定があり、SQL Server 2000 でも下位互換性のためにサポートされています。

	select into/ bulkcopy	trunc. log on chkpt.
フル (完全) 復旧モデル	FALSE	FALSE
一括ログ記録復旧モデル	TRUE	FALSE
シンプル(単純)復旧モデル	－	TRUE

表 1-4-2 **　データベース復旧モデルとデータベースオプション**

フル復旧モデルは SELECT INTO、CREATE INDEX、およびデータの一括ロードなどの一括操作をはじめ、すべての操作をログに完全に記録します。したがってどの復旧モデルよりもトランザクションログファイルの領域が多く必要です。

一括ログ記録復旧モデルはフル復旧モデルと似ていますが、SELECT INTO、CREATE INDEX、およびデータの一括ロードなどの一括操作に対して、最小のログ領域を使用します。その他の操作はログに完全に記録します。

シンプル復旧モデルは、一括ログ記録復旧モデルと同等のログを記録しますが、すぐに切り捨てられ、その領域は再利用されます。したがってどの復旧モデルよりもトランザクションログファイルの領域が最も少なくて済み、ログが無尽蔵に増加することはありません。

例えば表1-4-3 のようにデータベースとテーブルを作成します。

データベース: testdbFull (フル復旧モデル)
	ファイル名	サイズ (MB)
データベースファイル	testdbFull_Data	10
トランザクションログファイル	testdbFull_Log	1
データベース: testdbSimple (シンプル復旧モデル)
	ファイル名	サイズ (MB)
データベースファイル	testdbSimple_Data	10
トランザクションログファイル	testdbSimple_Log	1
テーブル: testtable
列名	データ型	長さ
Column1	bigint	8
Column1	char	10
Column3	bigint	8

表 1-4-3 **　データベースとテーブルの構成**

それぞれのデータベースをバックアップした後に、作成されたテーブル (testtable) に対し、以下の Transact-SQL を実行します (データが 100000 件追加されます)。

use testdb
declare @a bigint ,@sum bigint
set @a = 1
set @sum = 0
while @a < 100001
begin
 set @sum = @sum + @a
 insert testtable values(@a,'テスト',@sum)
 set @a = @a + 1
end
checkpoint

図 1-4-4 および図 1-4-5 が上記 SQL 実行後のそれぞれのデータベースの割り当て済み領域です。

図 1-4-4 **　フル復旧モデルに設定されたデータベースの割り当て済み領域**

図 1-4-5 **　シンプル復旧モデルに設定されたデータベースの割り当て済み領域**

フル復旧モデルではログ領域が膨大に増加しているのに対し、シンプル復旧モデルではログ領域が増えていないことがわかります。

このようにログのスペース要件のみを考えれば、シンプル復旧モデルを選択するのが最善です。しかし障害からの保護などの他要件を考慮にいれると必ずしも BEST な選択ではありません。

では BEST な選択をするために、次回は障害からの保護、特にバックアップを中心に SQL Server 2000 をとりあげてみたいと思います。

鈴木   智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。