| MSDE FunClub |
|
最終更新日 : 2001/12/22 |
| Microsoft Data Engine FunClub |
|
データファイル２個［プライマリグループ］ ログファイル１個 トランザクションログファイルの圧縮 |
この文書に基づいた運用結果に責任は負いません マイクロソフト社へのお問い合わせはできません |
| MSDE FunClub |
|
最終更新日 : 2001/12/22 |
| Microsoft Data Engine FunClub |
|
データファイル２個［プライマリグループ］ ログファイル１個 トランザクションログファイルの圧縮 |
この文書に基づいた運用結果に責任は負いません マイクロソフト社へのお問い合わせはできません |
| 1.調査に使用したデータベースのエンジンのバージョン |
SELECT @@VERSION
で表示されたバージョン番号は、
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
です。
SQLServer2000 + SP2 です。
|
| 2.データベースの定義 【MDF+NDF+LDF形式データベースを作成するSQL文】 MDFとNDFファイルは、同じプライマリファイルグループに所属します |
CREATE DATABASE MyTestDB
ON PRIMARY --プライマリグループのデータファイルの定義
(
NAME = MyTestDB_DAT , -- 論理ファイル名
FILENAME = 'C:\TEST\MyTestDB.mdf', -- 物理ファイル名
SIZE = 1MB , -- 初期サイズ
FILEGROWTH = 10% , -- 自動拡張単位
MAXSIZE = UNLIMITED -- ファイル最大サイズ（無制限）
)
,
( --プライマリグループ内にもう1個データファイルの作成
NAME = MyTestDB_DAT2 , -- 論理ファイル名
FILENAME = 'C:\TEST\MyTestDB.ndf', -- 物理ファイル名
SIZE = 1MB , -- 初期サイズ
FILEGROWTH = 10% , -- 自動拡張単位
MAXSIZE = UNLIMITED -- ファイル最大サイズ（無制限）
)
LOG ON -- トランザクションログファイル
(
NAME = MyTestDB_LOG , -- 論理ファイル名
FILENAME = 'C:\TEST\MyTestDB.ldf', -- 物理ファイル名
SIZE = 1MB , -- 初期サイズ
FILEGROWTH = 10% , -- 自動拡張単位
MAXSIZE = UNLIMITED -- ファイル最大サイズ
)
|
|
データファイルのmdfファイルとndfファイルは、同じプライマリファイルグループに所属するようにしました。
【注意】 |
| 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
|
|
トランザクションログファイルの圧縮を解説するために作成したテーブルです。 ５個の調査用のテーブルを作成します。
|
| 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
|
上記のスクリプトを実行すると
のように、大きなファイルサイズになりますので注意してください。 |
| 6.トランザクションログのバックアップとその切り捨て |
Backup Log MyTestDB
To Disk = 'C:\Test\Log.bak'
|
トランザクションログファイルのバックアップを実施して、ログの切捨てを実行しても
切り捨て後のログファイルの大きさに、まったく変化がない。 |
| 7.トランザクションログファイルの圧縮 |
Use MyTestDB
DBCC SHRINKFILE ( MyTestDB_Log ,1 )
Go
|
トランザクションログファイルを圧縮します。最初のログファイルの大きさは1MBなので、目標1MBに圧縮を実行します。
圧縮前の大きさは、1,108,800KBです。 DBCCコマンドが、情報メッセージを出力しているので、それを見てみます（グリッド画面）。
現在のトランザクションログファイルの全体の大きさ（CurrentSize）が、130704ページ トランザクションログファイルの最終位置に、ログ書き込みポインタが存在するためにファイルの圧縮効果が現れません(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
|
|
トランザクションログファイルの大きさが、1MBの初期サイズに戻りました。 しかしデータファイルの大きさが気になります。小さくすることはできないのでしょうか？
|
| 11.データベース全体の圧縮 |
DBCC SHRINKDATABASE ( MyTestDB , 1 )
|
|
データベースはほとんど空き状態なので、１％を目指して圧縮してみます。
データファイルの圧縮に成功しました。 |
