不要なSQL Serverトランザクションログファイルを削除する

By:Sergey Gigoyan|Updated:2015-11-18|Comments(9)|Related:More>Database Administration

Problem

SQL Serverでは複数のトランザクションログファイルを使用できますが、必要かどうかという疑問が生じます複数のトランザクションログファイルを持つことの利点は何ですか。 Multipetransactionログファイルを使用すると、SQL Serverがそれらを並行して使用できるため、パフォーマンスが向上する可能性があるという誤解が、一部の開発者の間で SQL Serverは、現時点では一つのトランザクションログファイルのみを使用し、この場合は並列処理はありません。 トラブルシューティングのために複数のログファイルが必要な場合があります。 したがって、通常、複数のログファイルを持つ必要はありません。データベースに複数のログファイルがあり、1つだけを保持する必要がある場合を考えてみましょう。このヒントは、不要なログファイルを正しく削除し、1つだけを保持する方法を説明することを目的としています。

ソリューション

不要なログファイルを削除する方法を説明する前に、SQL Serverがログファイルでどのように動作するかを簡単に説明しましょう: データベースに複数のログファイルがある場合、SQL Serverは最初のログファイルがいっぱいになるまで書き込みを続け、次に2番目のログファイルに切 最後のログファイルがいっぱいになると、SQL Serverは最初のログファイルに戻り、サイクルが続行されます。ただし、前述したように、複数のログファイルが必要になることがあります。 たとえば、ログファイルが配置されているディスクがいっぱいになり、別の場所に2番目のログファイルを作成する必要がありますが、問題のトラブルシューティング後、2番目のログファイルを削除する必要があります。次に、2つのログファイルを持つデータベースがあり、2番目のログファイルを削除することがタスクであると仮定します。次のスクリプトは、2つのログファイルとTestTableテーブルを持つTestDBデータベースを作成します。 それを自分で実行するには、交換する必要があります”D:\SQL 既存のフォルダパスを持つデータ”。

USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL

内部トランザクションログファイルは、トランザクションログファイル内の管理単位である仮想ログファイル(VLF)で構成されています。 これは、データベースエンジンがログファイルを拡大または縮小すると、完全なVlfでそれを実行することを意味します（たとえば、VLFの半分を縮小するこ 仮想ログファイルのサイズと物理ログファイルの数は固定されておらず、データベースエンジンによって動的に管理されます。 トランザクションログファイルを内部的に監視するには、仮想ログファイルに関する情報を提供する”DBCC LOGINFO”コマンドを使用します。 以下のスクリプトでは、データベースにこのコマンドを使用しました:

--Getting log files infoDBCC LOGINFO('TestDB')

その結果は次のようになります:

TestDB

FileIDは、データベースのログファイルIdです。 ステータスは、VLF再利用可能なornotであることを示します（可能な値：0-はい、2-いいえ）。 ご覧のとおり、VLF withStatus=2は1つだけです。 今、私たちはTestTableにデータを挿入し、ログファイルがどのように成長しているかを監視します:

USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n

この例では、両方のログファイルが増加し、2番目のログファイル（FileID=3）にも「Status=2」のVlfがあることがわかります:

VLFs_with_Status=2

次に、Testdb_Log2を削除する必要があります。ファイル：example.ldf セカンダリログファイルのみを削除できることに注意してください。 プライマリログファイルの削除は、SQL Serverでは許可されていません。各データベースには1つのプライマリログファイルのみがあり、データベース作成スクリプトで作成された最初のログファイルがプライマリと見なされます。2番目のログファイルを削除しようとすると:

USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2

次のメッセージが表示されます:

ALTER_DATABASE_TestDB

トランザクションログファイルが空の場合にのみ削除できるため、最初に空にする必要がありますit.To これを行うには、トランザクションログをバックアップする必要があります。 “TestDB”データベースは新しく作成され、完全バックアップがないため、TestDBデータベースの完全データベースバックアップを発行する必要があります。:

--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'

トランザクションログバックアップでは、ログファイルが切り捨てられます(例外がいくつかありますが、このstipの範囲外です)。 Log truncationは、非アクティブな仮想ログファイルを論理ログの先頭から削除し、ログファイル内の領域を解放します。 ただし、切り捨てによって物理ログファイルのサイズは縮小されません。 それだけで再利用することができ、その中のスペースを解放します。 もう一度”DBCC LOGINFO”を実行してみましょう:DBCC LOGINFO

見ることができるように、”Testdb_Log2に仮想ログファイルはありません。ステータス=2のldf”ファイルと今、私たちのログファイルは空であり、除去の準備ができています:

--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2

削除は成功しました。

Remove TestDB

ただし、情報を再度ログに記録すると、論理ログファイルがまだ存在することがわかります:

--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files

sys.database_files

別のログバックアップを行うと、ファイルは削除されます:

--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files

ログ情報の確認
次の手順
  • トランザクションログの使用状況と不要なログファイルを削除する方法を確認するには、このヒントに注意してくださ
  • 次のリソースをチェックアウトします。
    • データベース管理/トランザクションログ
    • https://technet.microsoft.com/en-us/library/ms191433(v=sqlです。105).aspx
    • https://technet.microsoft.com/en-us/library/ms179355(v=sqlです。105).aspx
    • https://technet.microsoft.com/en-us/library/ms345414(v=sqlです。105).aspx

スクリプトを取得する

次のヒントボタン

著者について
MSSQLTips著者Sergey GigoyanSergey Gigoyanは、データベース設計、開発、パフォーマンスチューニング、最適化、高可用性、BIおよびDW設計に焦点を当て、10年以上の経験を持つデータベースプロです。
すべての私のヒントを見る

記事最終更新日:2015-11-18

Leave a Reply

コメントを残す

メールアドレスが公開されることはありません。