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                 -- ファイル最大サイズ
   )
　

［画面2-1］作成されたデータベースのファイル

［画面2-2］作成されたデータベースのオプション状態

シングルファイルデータベースを作成します。
データファイルが１個、トランザクションログファイルが１個です。
ファイルの初期サイズは、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
上記のスクリプトを実行すると
［画面2-3］スクリプト実行後のファイルサイズ

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

　

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

    Backup Log MyTestDB
        To Disk = 'C:\Test\Log.bak'
トランザクションログファイルのバックアップを実施して、ログの切捨てを実行しても
［画面2-4］ログを切り捨てても大きさが変わらない

切り捨て後のログファイルの大きさに、まったく変化がない。
ログのバックアップファイルは、1GBになります。

　

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

    Use MyTestDB
    DBCC SHRINKFILE ( MyTestDB_Log ,1 )
Go
［画面2-6］ログファイルの圧縮

トランザクションログファイルを圧縮します。最初のログファイルの大きさは1MBなので、目標1MBに圧縮を実行します。
［画面2-5］ログファイルの圧縮。サイズが少ししか変わらない

圧縮前の大きさは、1,108,800KBです。
圧縮後の大きさは、1,045,632KBです。
ファイルサイズを圧縮させても、あまり効果がないことがわかりました。

DBCCコマンドが、情報メッセージを出力しているので、それを見てみます（グリッド画面）。

［画面2-7］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'
２度目のトランザクションログファイルのバックアップを実施して、ログの切捨てを実行しても
［画面2-8］ログを切り捨てても大きさが変わらない

切り捨て後のログファイルの大きさに、まったく変化がない。

　

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

    Use MyTestDB
    DBCC SHRINKFILE ( MyTestDB_Log ,1 )
Go
　

［画面2-9］２度目のログファイルの圧縮

２度目のトランザクションログファイルの圧縮では、DBCCのエラーメッセージが表示されない。

［画面2-10］圧縮に成功！

ファイルサイズと使用サイズが128ページとなっている！

［画面2-11］ログファイルが１MBに戻った

トランザクションログファイルの大きさが、1MBの初期サイズに戻りました。

しかしデータファイルの大きさが気になります。小さくすることはできないのでしょうか？

　

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


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

［画面2-12］２度目の完全バックアップの実施

　

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

     exec sp_detach_db 'MyTestDB'
　
データベースをデタッチして、データファイルとログファイルの名前を変更します。
拡張子.oldを付けました。
［画面2-13］ファイル名の変更

　

13.データベースの復元

     Restore Database MyTestDB
        From Disk = 'C:\Test\Data2.bak'
　
データベースを復元して、そのファイルサイズを見ました。
変化がありません。
［画面2-14］データベースの復元

元のファイルと同じサイズで、復元されております。
この事実からデータベースの完全バックアップでは、データベースの最適化が行なわれていないことがわかります。

【注意（2001/12/22追加）】この最適化とは、ファイルの圧縮という意味です

　

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

     DBCC SHRINKDATABASE ( MyTestDB , 1 )
　
データベースはほとんど空き状態なので、１％を目指して圧縮してみます。

［画面2-15］データベースの圧縮

［画面2-16］データファイルの圧縮に成功

データファイルは、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の単位は％です

［画面2-17］データファイルの圧縮

［画面2-18］データファイルの圧縮に成功

データファイルを予定通り、29MBに圧縮させることに成功しました。
しかしこれ以上の圧縮はできません。ここであきらめましょう。

ログファイルが大きくなっていますね。圧縮ができそうです。
ここから先のログファイルの圧縮にも頑張ってください。

　

　

Homeに戻る


（株）日本技術ソフト開発　責任編集：堀川　明
 MSDE FunClubに関するご意見・ご要望等ございましたら、 msdefun@horikawa.ne.jp　までご連絡下さい。
MSDE FunClubの運営は、マイクロソフト社とは一切の関係はありません