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 のようにデータベースとテーブルを作成します。

表 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) を削除してみます。

上記の 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 つのファイルグループがあります。

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

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

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

ページのトップへ

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

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

トランザクションログは 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)。

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

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

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

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

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

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

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

表 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 実行後のそれぞれのデータベースの割り当て済み領域です。

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

このようにログのスペース要件のみを考えれば、シンプル復旧モデルを選択するのが最善です。しかし障害からの保護などの他要件を考慮にいれると必ずしも 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# を勉強中。

ページのトップへ