SQLShack

Posted on by admin

この記事では、複数のSQL Serverトランザクションログファイルの使用方法と、セカンダリトランザクションログファイルを削除するプロセスについ

はじめに

既定では、SQL Serverデータベースにはプライマリデータファイルとトランザクションログファイルが含まれています。 複数のデータファイルを構成し、複数のデータファイルにデータを分割することをお勧めします。 これらのデータファイルを別々のストレージドライブに格納して、複数のIOを持つことができます。 これは、データ管理を支援し、パフォーマンスを向上させ、ファイルグループに応じてバッ

データベースアーキテクチャ

データベースアーキテクチャ

データベースに対しても複数のトランザクションログファイルを持つことができます。

SQL Serverでは、複数のトランザクションログファイルが順番に使用されます。 2つのトランザクションログファイルがあるとします。 Sql Serverは一度に一つのログファイルを使用し、一度にいっぱいになると、SQL Serverは別のログファイルを使用します。 SQL Serverはそれを並行して使用しないため、パフォーマンス上の利点は得られません。 理想的には、データベースごとにトランザクションログファイルを1つだけ持つ必要があります。

実稼働データベースがあり、ディスクの領域が不足しているという重大な警告を受け取ったとしましょう。 調査の結果、このドライブにトランザクションログファイルが格納されていることがわかりました。 アクティブなトランザクションのため、このSQL Serverトランザクションログファイルはいっぱいです。 ログファイルを縮小しようとしましたが、機能しませんでした。 トランザクションログもバックアップしますが、アクティブなトランザクションのために、必要な領域を解放できませんでした。 空き領域のある別のディスクに別のトランザクションログファイルを追加すると、この問題が解決されます。

SQL Serverはトランザクションログファイルにデータを書き込むためにシリアルモードを使用するため、後で追加のログファイルを削除する必要があります。 追加のログファイルを削除するプロセスを見てみましょう。

複数のSQL Serverトランザクションログファイルを持つデータベースを作成する

SQL Server Management StudioでSQLインスタンスに接続します。 オブジェクトエクスプローラーペインのデータベースノードを右クリックし、新しいデータベースコ:

新しいデータベースを作成します

新しいデータベースを作成する

新しいデータベースウィンドウが開きます。 データベース名を指定し、別のトランザクションログファイルを追加します。 テスト目的で、トランザクションログファイルの自動拡張を無効にしました。 トランザクションログファイルの初期サイズは8MBです:

複数のログファイルの指定

複数のログファイルを指定する

すべてを設定したら、OKボタンをクリックして、デフォルトのデータ/ログファイルディレクトリにデータベースを作成します。 テーブルを作成してデータを挿入する:

1
2
3
4
5
6
7
複数のログファイルを使用します。
GO
CREATE TABLE Employee
(EmpID INT IDENTITY(1, 1),
EmpName VARCHAR(50)
);
従業員への挿入(EmpName)値(‘Raj’)

SQL Serverトランザクションログファイルでの仮想ログファイルの状態の表示

記事”SQL Serverトランザクションログアーキテクチャ”では、トランザクションログフ 各トランザクションログファイルは、複数の仮想ログファイルで構成されます。 トランザクションログファイルは、複数の仮想ログファイル(VLF)の組み合わせです。 次のスクリーンショットは、ログファイルの物理的および論理的なアーキテクチャを示しています:

SQL Server transactionnログの物理および論理アーキテクチャ

SQL Server transactionnログの物理および論理アーキテクチャ

SQL Serverは、初期ログサイズと自動拡張ファイル(自動拡張構成に基づく)に基づいて、最小VLFのデータベースを統計 次の図では、SQL Serverトランザクションログファイルを垣間見ることができます:

循環ログファイル

循環ログファイル

単一のトランザクションログファイルの場合、SQL Serverは循環仮想ログファイルパスを使用します。 以下を使用して、仮想ログファイルの数とその状態を確認できます:

  1. DBCC LOGINFO(‘Database’)–これは古いステートメントであり、すべてのSQL Serverバージョン
  2. 動的管理ビュー sys.dm_db_log_info(DBID)で動作します。 SQL Server2016SP2以降から使用できます

VLFチェックには、任意のコマンドを使用できます。 この記事では、動的管理ビュー(DMV)を使用します):

1
2
sys.dm_db_log_info(DB_ID(‘MultileLogFiles’)から*
を選択します’));

Monitor VLF status

上のスクリーンショットでは、サンプルデータベースに2つのトランザクションログファイル(file_id2とfile_id3)が含まれていることを確認しました。

  • File_Id2にアクティブなVLFがあります(vlf_active=1およびvlf_status=2)
  • File_Id3にアクティブなVLFがありません(vlf_active=0およびvlf_status=2)=2)

アクティブなVLFが変更されるように、テーブルにさらにいくつかのレコードを挿入しましょう:

1
2
従業員に挿入(EmpName)値(‘Raj’)
移動3000

トランザクションログ領域の使用状況の監視とVLFステータスは、DMV(sys.dm_db_log_space_usage)を使用して実行されます。:

  • VLFステータス

    1
    2
    sys.dm_db_log_info(DB_ID(‘MultileLogFiles’)からDb_Name()をDatabaseName、File_Idをtransaction_log_file_id、vlf_active、vlf_status
    として選択します’));

  • トランザクションログの使用状況(使用済みおよび空き領域)

    1
    2
    3
    4
    5
    total_log_size_in_bytes*1を選択します。0/1024/1024total_log_size_in_mb,
    used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_mb,
    (total_log_size_in_bytes-used_log_space_in_bytes)*1.0/1024/1024
    sys.dm_db_log_space_usageからfree_log_space_in_mb
    として;

上記のクエリの次の出力が得られます:

  • 左側では、プライマリログファイル（file_id2）がいっぱいになると、次のログファイル（file_id3）に移動することがわかります。 この時点で、プライマリログファイルとセカンダリログファイルの両方にアクティブなVLFがあります。 完全復旧モデルのデータベースでは、vlfを非アクティブとしてマークするようにトランザクションログバックアップが必要です
  • 右側の出力（DMVを使用）には、トランザクションログファイルが使用されているスペース8.51MBが表示されます。 ご存知のように、各トランザクションログファイルに8MBのサイズを設定し、ログの自動拡張を無効にしました。 プライマリログファイルがいっぱい(8MB)になると、SQL Serverはセカンダリトランザクションログファイル

セカンダリSQL Serverトランザクションログファイル

に切

REMOVE FILE句を追加し、削除するファイルを指定します:

1
2
ALTER DATABASE REMOVE FILE
GO

このステートメントを実行すると、次のエラーメッセージが表示されます:

エラーメッセージ

エラーメッセージ

注:アクティブなトランザクションログファイルは削除できません。

以前は、プライマリログファイルがいっぱいになると、SQL Serverはセカンダリログファイルを使用することがわかりました。 セカンダリトランザクションログを空にして、削除できるようにする必要があります。

完全復旧モデルを持つSQLデータベースでは、sql Serverがログを切り捨てることができるように、トランザクションログバックアップを使用します。 このためには、トランザクションログサイズ、アクティブなVLF、アクティブなトランザクションに応じて、複数のログバックアップが必要になる場合があります。

完全なデータベースとトランザクションログバックアップを実行しましょう:

  • データベースの完全バックアップ

    1
    データベースMultipleLogFilesをディスクにバックアップ=’C:\Temp\MultipleLogFiles…..bak’

  • トランザクションログバックアップ

    1
    ディスクへのバックアップログMultipleLogFiles=’C:\Temp\MultipleLogFiles_log…..trn’

バックアップが完了したら、VLFの状態を確認します。 セカンダリログファイルを削除できるように、アクティブなVLFは最初のログファイル（プライマリ）にある必要があります。 プライマリログファイル（file_id2）のみがアクティブであることを確認しました（VLFステータス2）:

VLFステータス

さて、セカンダリトランザクションログファイルは問題なく削除する必要があります。 編集したAlter database文をもう一度実行してみましょう。 セカンダリトランザクションログファイルが削除されます:

セカンダリログファイルの削除

セカンダリログファイルの削除

セカンダリトランザクションログファイルが削除されたら、GUIとSYSTEM view sysを使用したT-SQLを使用して確認します。データベースファイル:

1
2
3
4
5
sysからfile_id、
name、
type_desc、
physical_name
を選択します。データベースファイル;

削除されたセカンダリトランザクションログファイルは、次のスクリーンショット:

削除されたログファイルの確認

削除されたログファイル

をチェックして、データベースを右クリックして既存のファイルを表示します。 削除されたトランザクションログファイルもGUIに表示されます。 しかし、なぜ？

ログファイルSSMSの検証

ログファイルの検証SSMS

編集したAlter databaseステートメントを再度実行し、SQL Serverがトランザクションログファイルを再度削除するかどうかを確認します。

SQL Serverが指定されたログファイルを見つけることができませんでしたというメッセージが表示されました:

エラーメッセージ

エラーメッセージ

SQL Serverは、後続のログバックアップの後にトランザクションログファイルを削除します。 別のログバックアップを作成し、トランザクションログファイルがまだ存在することを確認しましょう:

1
ディスクへのバックアップログMultipleLogFiles=’C:\Temp\MultipleLogFiles_log_1…..trn’

トランザクションログバックアップ

トランザクションログバックアップ

GUIメソッドとT-SQLメソッドの両方でトランザクションログファイルを確認します。 削除されたトランザクションログファイルが表示されないことがわかります。

結論

この記事では、セカンダリSQL Serverトランザクションログの使用法とそれを削除するプロセスについて説明しました。 特に運用データベースでは、複数のトランザクションログファイルを使用しないでください。 アクティビティを計画する前にデータベースのバックアップを取り、生産性の低い時間に実行する必要があります。

  • 著者
  • 最近の投稿
ラジェンドラ-グプタ
インドのグルガオンでMCSA認定およびMicrosoft認定トレーナーとして、13年の経験を持つRajendraは、パフォーマンスの最適化、監視、高可用性、災害復旧戦略と実装に焦点を SQL Server、Azure、MySQL、Linux、Power BI、パフォーマンスチューニング、AWS/Amazon RDS、Git、および関連技術に関する数百の権威ある記事の著者であり、これまでに10m以上の読者に閲覧され
彼は、単一のトピックに関する記事の最大の無料オンラインコレクションの一つの作成者であり、SQL Server Always On Availability Groupsに関する50部のシリーズを持っています。 SQL Serverコミュニティへの貢献に基づいて、彼はSQLShackで2020年と2021年に連続して権威ある「best author of the year」を含むさまざまな賞を受賞しています。
ラージは常に新しい挑戦に興味を持っているので、彼の文章でカバーされているテーマについて相談する必要がある場合は、ラージンドラで連絡することが[email protected]
Rajendra Guptaによるすべての投稿を表示

ラジェンドラ-グプタ
Rajendra Guptaによる最新の投稿(すべて見る)
  • ARMテンプレートを使用してSql Server Linuxイメージを使用してAzureコンテナインスタンスをデプロイする-2021年12月21日
  • AWS RDS SQL ServerとAmazon Rdsのリモートデスクトップア10, 2021

Related Posts

コメントを残す

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