MSDE FunClub		最終更新日 : 2001/12/22
Microsoft Data Engine FunClub	Since 2001.12.20

シングルファイルデータベース トランザクションログファイルの圧縮 【一般的な方法】 この記事には原因不明なオペレーションミスにより 結果に誤りがありました 訂正記事をご覧下さい

＊技術書の文責は堀川にありますので、お問い合わせは私までお願いします＊ この文書に基づいた運用結果に責任は負いません マイクロソフト社へのお問い合わせはできません

1.調査に使用したデータベースのエンジンのバージョン

SELECT @@VERSION で表示されたバージョン番号は、 Microsoft SQL Server 2000 - 8.00.534 (Intel X86) です。 SQLServer2000 + SP2 です。

　

2.データベースの定義 【シングルファイルデータベースを作成するSQL文】

CREATE DATABASE MyTestDB ON PRIMARY -- データファイル ( NAME = MyTestDB_DAT , -- 論理ファイル名 FILENAME = 'C:\TEST\MyTestDB.mdf', -- 物理ファイル名 SIZE = 1MB , -- 初期サイズ FILEGROWTH = 10% , -- 自動拡張単位 MAXSIZE = UNLIMITED -- ファイル最大サイズ（無制限） ) LOG ON -- トランザクションログファイル ( NAME = MyTestDB_LOG , -- 論理ファイル名 FILENAME = 'C:\TEST\MyTestDB.ldf', -- 物理ファイル名 SIZE = 1MB , -- 初期サイズ FILEGROWTH = 10% , -- 自動拡張単位 MAXSIZE = UNLIMITED -- ファイル最大サイズ )

シングルファイルデータベースを作成します。 データファイルが１個、トランザクションログファイルが１個です。 ファイルの初期サイズは、1MBとします。 データベースオプションでは、自動圧縮は付いておりません。 復旧モデルはフルにしています。 【注意】 MSDE/MSDE2000を使ってデータベースを作成すると、データベースは『ログの切捨て』になっています。オプションを変えてください。

　

3.データベースの完全バックアップを実施する データベースの復元基点の作成

Backup Database MyTestDB To Disk = 'C:\Test\Data.bak'

トランザクションログファイルのログのバックアップを実行する時には（切り捨ても同時に実施）、データベースの復元の基点となる完全バックアップファイルが存在しないと、ログのバックアップ時に警告メッセージが表示されます。 ★★★★★【表示される警告メッセージ】★★★★★ 現在のデータベース バックアップは存在しません。このログ バックアップを、以前のデータベース バックアップをロールフォワードするためには使用できません。 ★★★★★ここまで★★★★★ トランザクションログのバックアップを実行しても、復元基点となるデータベースの完全バックアップを実施していないために、そのログのバックアップファイルの使い道が無効であるという意味です。 完全バックアップを実行します。

　

4.テスト用のテーブルを作成する

Use MyTestDB GO Create Table Test0( ID int Identity(1,1) Primary key , DT char(8000) ) Create Table Test1( ID int Identity(1,1) Primary key , DT char(8000) ) Create Table Test2( ID int Identity(1,1) Primary key , DT char(8000) ) Create Table Test3( ID int Identity(1,1) Primary key , DT char(8000) ) Create Table Test4( ID int Identity(1,1) Primary key , DT char(8000) ) Go

トランザクションログファイルの圧縮を解説するために作成したテーブルです。 ５個の調査用のテーブルを作成します。 【注意】 SQLServer7.0及びSQLServer2000の仕様上、１行（レコード）の最大サイズは、8060バイトです。 １レコードが8060バイトを超える大きなレコードの設計はできません(Text型やimage型を除いた大きさ)。 Books Onlineの『最大容量仕様』を参照下さい。

　

5.レコード登録用スクリプト 最終的にはすべてのレコードは削除されます

Set NOCOUNT ON Use MyTestDB GO Declare @i int , @max_recno int Declare @j int , @max_count int Select @j = 1 Select @max_count = 6 while( @j <= @max_count ) Begin Print 'Now = ' + Cast(@j as varchar(2)) Select @i = 1 Select @max_recno = 1000 While( @i <= @max_recno ) Begin Insert into Test0(DT) Values( CAST(@i As Char(8000)) ) Select @i = @i + 1 End --Copy Record Insert Into Test1(DT) Select DT From Test0 Insert Into Test2(DT) Select DT From Test1 Insert Into Test3(DT) Select DT From Test2 Insert Into Test4(DT) Select DT From Test3 Select @j = @j + 1 --Delete Record If( (@j%2) = 1 ) Begin Delete from Test0 Delete from Test1 Delete from Test2 Delete from Test3 Delete from Test4 End End Go

上記のスクリプトを実行すると のように、大きなファイルサイズになりますので注意してください。 トランザクションログファイルが１GBを超えます。 スクリプトの実行は、８分程度の時間がかかります。

　

6.トランザクションログのバックアップとその切り捨て

Backup Log MyTestDB To Disk = 'C:\Test\Log.bak'

トランザクションログファイルのバックアップを実施して、ログの切捨てを実行しても 切り捨て後のログファイルの大きさに、まったく変化がない。 ログのバックアップファイルは、1GBになります。

　

7.トランザクションログファイルの圧縮

Use MyTestDB DBCC SHRINKFILE ( MyTestDB_Log ,1 ) Go

トランザクションログファイルを圧縮します。最初のログファイルの大きさは1MBなので、目標1MBに圧縮を実行します。 圧縮前の大きさは、1,108,800KBです。 圧縮後の大きさは、1,045,632KBです。 ファイルサイズを圧縮させても、あまり効果がないことがわかりました。 DBCCコマンドが、情報メッセージを出力しているので、それを見てみます（グリッド画面）。 　　現在のトランザクションログファイルの全体の大きさ（CurrentSize）が、130704ページ 　　使用済みログページ（UsedPages）は、130704ページ 　　圧縮後のログファイルサイズ（EstimatedPages）は、128ページ（圧縮可能目標値） だということがわかりました。１ページの大きさは8KBです。 トランザクションログファイルの最終位置に、ログ書き込みポインタが存在するためにファイルの圧縮効果が現れません(CurrentSizeとUsedPagesが同じ値となっている)。 そこでダミーのログを書き込んで、ログ書き込み位置をファイル先頭に戻します。

　

8.ダミーのログの書き込み

Set NOCOUNT ON Use MyTestDB Go Create Table Dummy( ID Int Identity(1,1) Primary Key , Dt Char(8000) ) Go Declare @CurrentSize int , @UsedPages int Set @CurrentSize = 130704 Set @UsedPages = 130704 While( @UsedPages < @CurrentSize+1 ) Begin Insert Into Dummy(Dt) Values('a') Set @UsedPages = @UsedPages + 1 End Go

DBCCが出力したCurrentSizeとUsedPagesの値を使って、ダミーのログを書き込みます。 １ページが８KBなので、１レコード８KB程度のレコードを書き込んで、ログの書き込み位置をファイル先頭に移動します。

　

9.再度のトランザクションログのバックアップとその切り捨て

Backup Log MyTestDB To Disk = 'C:\Test\Log2.bak'

２度目のトランザクションログファイルのバックアップを実施して、ログの切捨てを実行しても 切り捨て後のログファイルの大きさに、まったく変化がない。

　

10.再度のトランザクションログファイルの圧縮

Use MyTestDB DBCC SHRINKFILE ( MyTestDB_Log ,1 ) Go

２度目のトランザクションログファイルの圧縮では、DBCCのエラーメッセージが表示されない。 ファイルサイズと使用サイズが128ページとなっている！ トランザクションログファイルの大きさが、1MBの初期サイズに戻りました。 しかしデータファイルの大きさが気になります。小さくすることはできないのでしょうか？

　

11.データベースの完全バックアップを実施します

Backup Database MyTestDB To Disk = 'C:\Test\Data2.bak'

　

12.データベースをデタッチします（切り離し）

exec sp_detach_db 'MyTestDB'

データベースをデタッチして、データファイルとログファイルの名前を変更します。 拡張子.oldを付けました。

　

13.データベースの復元

Restore Database MyTestDB From Disk = 'C:\Test\Data2.bak'

データベースを復元して、そのファイルサイズを見ました。 変化がありません。 元のファイルと同じサイズで、復元されております。 この事実からデータベースの完全バックアップでは、データベースの最適化が行なわれていないことがわかります。 【注意（2001/12/22追加）】この最適化とは、ファイルの圧縮という意味です

　

14.データベース全体の圧縮

DBCC SHRINKDATABASE ( MyTestDB , 1 )

データベースはほとんど空き状態なので、１％を目指して圧縮してみます。 データファイルは、258,432KBから51,456KBになりました。ただログファイルが増えてしまいました。 DBCCの報告を見ると、3672ページまで小さくなることがわかります。3672*8=29376KB（29MB）です。 【注意(2001/12/22記事訂正)】 追試により、このオペレーション操作によって、データファイルの圧縮も実現できます。 訂正記事をご覧下さい なぜ初回掲載時に圧縮がうまく行かなかったのか、その原因は不明です

　

15.データファイルの圧縮

Use MyTestDB DBCC SHRINKFILE ( MyTestDB_DAT , 29 , NOTRUNCATE ) Go DBCC SHRINKFILE ( MyTestDB_DAT , 29 , TRUNCATEONLY ) Go

データベースファイルを、29MB目指して圧縮させます。引数の29の単位はMBです。 DBCC SHRINKDATABASEの単位は％です データファイルを予定通り、29MBに圧縮させることに成功しました。 しかしこれ以上の圧縮はできません。ここであきらめましょう。 ログファイルが大きくなっていますね。圧縮ができそうです。 ここから先のログファイルの圧縮にも頑張ってください。