MSDE FunClub 現在までのアクセスカウント数 最終更新日 : 2001/12/21
Microsoft Data Engine FunClub
Since 2001.12.21

MDF+NDF+LDF
データファイル２個［別々のグループ］
ログファイル１個
推奨データベース形式
トランザクションログファイルの圧縮
＊技術書の文責は堀川にありますので、お問い合わせは私までお願いします＊
この文書に基づいた運用結果に責任は負いません
マイクロソフト社へのお問い合わせはできません

　

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

    SELECT @@VERSION

で表示されたバージョン番号は、

    Microsoft SQL Server  2000 - 8.00.534 (Intel X86)  

です。

    SQLServer2000 + SP2 です。
　

2.データベースの定義
【MDF+NDF+LDF形式データベースを作成するSQL文】

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

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

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

［画面3-3］データベースオプションの確認

データファイルが２個、トランザクションログファイルが１個の合計３個のファイルから構成されるデータベースを作成します。
データファイルのmdfファイルとndfファイルは、別々のファイルグループに所属するようにしました。
これは、データベースの制御情報が記録されるプライマリグループと実際のデータが記録されるグループを分けることによって、データベースの圧縮操作が見事に働くことを期待しています。
結論から述べると、DBCC SHRINKDATABASE 命令によってデータファイルの圧縮が簡単にできました。このため『推奨データベース』と呼ぶことにします。

【注意】
MSDE/MSDE2000を使ってデータベースを作成すると、データベースは『ログの切捨て』になっています。オプションを変えてください。

　

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

   Backup Database MyTestDB
       To Disk = 'C:\Test\Data.bak'
復元基点となるデータベースの完全バックアップを実施します。
　

4.テスト用のテーブルを作成する
テーブルは、GROUP2ファイルグループに作成

    Use MyTestDB
GO
    Create Table Test0( ID int Identity(1,1) Primary key , DT char(8000) ) On GROUP2  
    Create Table Test1( ID int Identity(1,1) Primary key , DT char(8000) ) On GROUP2  
    Create Table Test2( ID int Identity(1,1) Primary key , DT char(8000) ) On GROUP2  
    Create Table Test3( ID int Identity(1,1) Primary key , DT char(8000) ) On GROUP2  
    Create Table Test4( ID int Identity(1,1) Primary key , DT char(8000) ) On GROUP2  
Go
トランザクションログファイルの圧縮を解説するために作成したテーブルです。
５個の調査用のテーブルを作成します。

テーブルはすべて、ファイルグループGROUP2に作成してください。
データベースの制御情報が記録されるプライマリファイルグループにデータが記録されるのを避けます

　

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
上記のスクリプトを実行すると
［画面3-4］スクリプト実行後のファイルサイズ

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

　

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

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

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

　

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

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

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

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

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

　　現在のトランザクションログファイルの全体の大きさ（CurrentSize）が、132272ページ
　　使用済みログページ（UsedPages）は、132272ページ
　　圧縮後のログファイルサイズ（EstimatedPages）は、128ページ（圧縮可能目標値）
だということがわかりました。１ページの大きさは8KBです。

トランザクションログファイルの最終位置に、ログ書き込みポインタが存在するためにファイルの圧縮効果が現れません(CurrentSizeとUsedPagesが同じ値となっている)。

そこでダミーのログを書き込んで、ログ書き込み位置をファイル先頭に戻します。

　

8.ダミーのログの書き込み
テーブルは、ファイルグループGROUP2に作成する

  Set NOCOUNT ON
  Use MyTestDB
Go

  Create Table Dummy( ID Int Identity(1,1) Primary Key , Dt Char(8000) ) ON GROUP2  
Go

    Declare @CurrentSize int , @UsedPages int
    Set @CurrentSize = 132272
    Set @UsedPages   = 132272
    While( @UsedPages < @CurrentSize+1 )
      Begin
         Insert Into Dummy(Dt) Values('a')
         Set @UsedPages = @UsedPages + 1
      End
Go
DBCCが出力したCurrentSizeとUsedPagesの値を使って、ダミーのログを書き込みます。
１ページが８KBなので、１レコード８KB程度のレコードを書き込んで、ログの書き込み位置をファイル先頭に移動します。

プライマリファイルグループにテーブルを作成しないように。ファイルグループGROUP2に作成します。

　

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

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

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

　

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

    Use MyTestDB
    DBCC SHRINKFILE ( MyTestDB_Log ,1 )
Go
　

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

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

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

　

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

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

［画面3-10］圧縮に成功

データファイルの圧縮に成功しました。
すべてのファイルが初期サイズに戻りました

　

　

Homeに戻る


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