SQLServer7.0のLDF圧縮

質問

お世話になってます。

あるサーバー（サーバーA）上のSQLServer7.0で作成したバックアップファイルを
復元先のサーバー（サーバーB）に復元したところ、MDFファイルが3GBに対し、
LDFファイルが100GBありました。
LDFファイルのサイズを圧縮しようと、SQLServer7.0のメニューで、

　1. フルバックアップ
　2. トランザクションログの切り捨て
　3. 圧縮
　※以下「1.」、「2.」、「3.」を何度か繰り返し。

を行ってみましたが、300MB程度減っただけで、それ以上は小さくなりませんでした。

　SHRINKDATABASE
　や
　TRUNCATE

も試してみましたが、サイズ、変わりませんでした。

他に試すことはありますでしょうか？

回答No.1

uams · ベストアンサー

懐かしいですね、SQL Server 7。

ちょっとメニューがどうだったか覚えていないのですが、バックアップをファイル単位（データまたはトランザクション）でできるのであれば、トランザクションファイルだけをバックアップすれば、トランザクションログの使用領域は小さくなります。その上でトランザクションファイルの圧縮をすれば、ファイルとしての実体は小さくなります。この手順は現在のバージョンでも変わりありません。

あとは復旧モデルを「完全」以外にする、別のバージョン（たとえば無償の2008 Expressなど）に復元してみるなどが考えられます。

サーバーの設定の影響もあるかもしれないので、可能であれば、サーバーAで同じ作業をして小さくなるのを確認してからフルバックアップしたものを復元した方が確実かと思います。

あと、試したことはないのですが、LDF（トランザクションファイル）は実データではないので、一度デタッチしてLDFを削除し、同名のLDF（新規のデータベースを作って同名のLDFを作成、ファイルの新規作成で0バイトのテキストファイルをリネームして～.ldfで保存など）を作ってアタッチすることもできると聞いたことがあります。以前SQL Server 2008でそれを試したらうまくいかず、さらに元のLDFを削除してしまったためにどうにもならなくなった思い出があるので、試すのであれば削除ではなく別の場所に退避しておくことをお勧めします。

お礼

ありがとうございます。
お返事が遅くなってしまいました。

実のところ今回、SQL7のDBバックアップファイルからSQL2000に復元することが目的だったのですが、
LDFが大きいせいか、SQL2000上で復元完了後、OS自体が壊れてしまいます。
(Cドライブに入れない等、エラー多発で、さらには再起動するとOSが見つからないエラーになってしまいます、、、)

で、仕方なく、MDFのみSQL2000上に移しアタッチしたところ、新しいLDFが作成され、
無事にSQL2000上にDBを移行することができました。

回答No.2

Tom-3

下記のSQLを実行してみてください。

見やすいように半角スペース2つは全角スペース1つに変換してます。

使用される際は「全角スペース1つ」を「半角スペース2つ」に変換してください。
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
　　　　@MaxMinutes INT,
　　　　@NewSize INT

-- *** 検索条件により、次の 3 行を変更してください。 ***
USE　　 データベース名　　　　　　　　　　　　-- ログを圧縮するデータベース名を指定してください。
SELECT　@LogicalFileName = 'ログファイル名',　-- 圧縮したいログファイルを確認指定してください。
　　　　　　　　　　　　　　　　　　　　　　　-- (ログファイルは sp_helpfile で確認可能です)
　　　　@MaxMinutes = 10,　　　　　　　　　　 -- ログファイル圧縮処理の実行時間を指定します。
　　　　@NewSize = 512　　　　　　　　　　　　-- 単位は MB です。

-- セットアップ/初期化
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- 8K ページ
　FROM sysfiles
　WHERE name = @LogicalFileName
SELECT 'Original Size of' + db_name() + ' LOG is ' +
　　　　CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
　　　　CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
　FROM sysfiles
　WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
　(DummyColumn char (8000) not null)

-- ログをラップし、それを切り捨てます。
DECLARE @Counter　 INT,
　　　　@StartTime DATETIME,
　　　　@TruncLog　VARCHAR(255)
SELECT　@StartTime = GETDATE(),
　　　　@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- 最初の圧縮を試みます。
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- 必要な場合に、ログをラップします。
WHILE　　 @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- 有効期間は切れていません
　　　AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 -- ログは圧縮されていません　　
　　　AND (@OriginalSize * 8/1024) > @NewSize　-- 新しいサイズに渡される値は、現在のサイズ未満です。
　BEGIN -- 外部ループ
　　SELECT @Counter = 0
　　WHILE　((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
　　　BEGIN -- 更新します
　　　　INSERT DummyTrans VALUES ('Fill Log')　-- char フィールドなので、8000 バイト挿入します。
　　　　DELETE DummyTrans
　　　　SELECT @Counter = @Counter + 1
　　　END　 -- 更新
　　EXEC (@TruncLog)　-- ログの切り捨て。
　END　 -- 外部ループ
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
　　　　CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
　　　　CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
　FROM sysfiles
　WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** データベース全体のバックアップを実行します ***'
SET NOCOUNT OFF

お礼

ありがとうございます。
お返事が遅くなってしまいました。

実のところ今回、SQL7のDBバックアップファイルからSQL2000に復元することが目的だったのですが、
LDFが大きいせいか、SQL2000上で復元完了後、OS自体が壊れてしまいます。
(Cドライブに入れない等、エラー多発で、さらには再起動するとOSが見つからないエラーになってしまいます、、、)

で、仕方なく、MDFのみSQL2000上に移しアタッチしたところ、新しいLDFが作成され、
無事にSQL2000上にDBを移行することができました。