HowTo : Management Studio を使ってトランザクションログファイル (ldf) のサイズを小さくする方法
みなさん、こんにちは ! 「ログファイルが大きくなってディスク領域を圧迫し始めているので、ファイルサイズを小さくしたい」という内容の問合わせは今でも多く寄せられます。今回は、SQL Server Management Studio GUI を使って、トランザクションログファイルのサイズを小さくする手順を紹介します。 STEP 1 : データベースの復旧モデルを確認する STEP 2 : トランザクションログをバックアップする STEP 3 : トランザクションログファイルのサイズを小さくする ※復旧モデルが「単純」の場合、もしくは「完全」や「一括ログ」でデータベースの完全バックアップが一度も取得されていない場合、STEP 2 をスキップします。 以上の手順で、トランザクションログファイルのサイズを小さくしたいという状況のほとんどに対応可能だと思います。 ここに記載した方法でトランザクションログファイルのサイズを小さくできない場合は、おそらく、トランザクションログファイルのサイズを小さくする前に、レプリケーションやミラーリングのトラブルシューティングなどが必要になるでしょう。 STEP 1 : データベースの復旧モデルを確認する 復旧モデルが「単純」かそれ以外かによって、以降の手順が違ってきますので、まず最初に、データベースの復旧モデルを確認します。 手順 復旧モデルを確認するために、データベースのプロパティを表示します。データベースのプロパティは、オブジェクトエクスプローラーでデータベース名を右クリックし、「プロパティ」をクリックすることで表示できます。 表示されたダイアログボックスの左ペインで「オプション」を選択すると、右ペインに「復旧モデル」が表示されます。復旧モデルは、「単純」「完全」「一括ログ」のいずれかです。 STEP 2 : トランザクションログをバックアップする トランザクションログは、データベースファイルへの更新履歴ですので、データベースに対して更新を行うたびにトランザクションログファイルには履歴データが記録され、何もしなければ、トランザクションログファイル内の履歴データはどんどん増えていきます。 復旧モデルが「単純」の場合は、トランザクションログファイル内のデータ量が一定量を超えると、SQL Server がファイルの中身を消し、ファイル内に空き領域を作り、空いた領域は再利用されます。 一方、復旧モデルが「完全」または「一括ログ」に設定されている場合は、過去に一度でもデータベースの完全バックアップ (データベースフルバックアップ) を取得していると、SQL Server はファイルの中身を消すことはしませんので、トランザクションログファイル内のデータは、バックアップしなければ削除されません。 この STEP 2 は、復旧モデルが「完全」または「一括ログ」の場合のみ行います。「単純」の場合は、次の STEP 3 に進みます。 手順 データベースを右クリックし、「タスク」 - 「バックアップ」をクリックします。 「バックアップの種類」として「トランザクションログ」を選びます。もし、復旧モデルが「単純」に設定されている場合は、「トランザクションログ」は選択できません。 「バックアップセットの有効期限」は既定のまま、「バックアップ先」は、バックアップデータを書き込むファイル名を指定します。「ディスク」を指定して、「追加」でファイル名を指定して下さい。そのサーバーにテープデバイスがある場合には、「テープ」を選んでも構いません。 最後に「OK」を押すと、バックアップが開始されます。 復旧モデルが「完全」または「一括ログ」であっても、データベースの完全バックアップが一度も取得されていない場合、トランザクションログのバックアップは以下のエラーで失敗します。 メッセージ 4214、レベル 16、状態 1 現在、データベースのバックアップが存在しないので、BACKUP LOG を実行できません。 復旧モデルが「完全」または「一括ログ」であっても、データベースの完全バックアップが一度も取得されていない場合は、トランザクションログは自動的に切り捨てられていますので、このステップを実行せずに STEP 3 に進むことができます。 ! : バックアップファイルの出力先にバックアップデータを保持できるだけの十分な容量がない場合 バックアップファイルの出力先にすべてのバックアップデータを保持できるだけの容量がない場合、バックアップは失敗します。この場合、トランザクションログの切り捨ては行われません。バックアップファイルの出力先に十分な容量が確保できない場合は、データベースの復旧モデルを一時的に「完全」や「一括ログ」から「単純」に変更することで、STEP 3 を行うことができるようになります。 STEP 3 に進む前に CHECKPOINT が実行される必要がありますので、復旧モデル変更後は、データベースに対して CHECKPOINT が実行されるだけの量の更新が行われるのをしばらく待つか、明示的に CHECKPOINT を実行する必要があります。明示的な CHECKPOINT の実行方法は、本投稿内の「CHECKPOINT が実行されていない場合」を参照して下さい。 なお、この方法では、トランザクションログを使用したデータベース復旧はできなくなります。そのため、復旧モデルを「単純」に変更する前に、データベースへの書き込みを行わない状態にし、データベースの完全バックアップを取得することをお勧めします。これにより、一連の作業中に不測の事態が発生したとしても、作業開始前の状態までは確実に戻れるようになります。 STEP 3 : トランザクションログファイルのサイズを小さくする バックアップにより、トランザクションログファイルの中身を消したとしても、ファイル自体のサイズは小さくなりません。 ファイル内に空きがなくなれば、データを書き込むために再びファイルサイズを大きくしなければなりません。当然、ファイルサイズを変更するためには、メモリも CPU も使いますし、ある程度は時間もかかります。そのため、パフォーマンスの観点からは、トランザクションログファイルには、常に空き領域がある状態を保つ方が理想的です。ディスクの空き領域の問題などにより、ファイルを小さくしなければならない場合は、可能な限り小さくするというよりも、ある程度の余裕を持ったサイズにした方がいいでしょう。 手順 データベースを右クリックし、「タスク」、「圧縮」、「ファイル」の順にクリックします。 「ファイルの種類」は「ログ」、「ファイル名」は、大きくなってしまったトランザクションログファイルの論理名、「圧縮アクション」として「未使用領域の解放前にページを再構成する」を選択し、ファイルサイズの目標となるサイズを MB 単位で指定します。このサイズは目標サイズであるため、必ずしも、そのサイズまで小さくできるとは限りません。 この手順を実施してもトランザクションログファイルが小さくならない場合 以上の手順を実施することで、トランザクションログファイルのサイズは小さくできるはずですが、小さくならない場合は、STEP 2, 3 をもう一度繰り返してみて下さい。 もし、それでも小さくならない場合は、そのほとんどは、以下のいずれかが原因です。(これ以外にも原因となるものはありますが、それらは長時間存在し続けるものではないため、通常、上の手順を繰り返し行えば必ずファイルは小さくなります。) 実行中のトランザクションがある場合 トランザクションレプリケーションが構成されていて、まだ配布されていないトランザクションがある場合 データベースミラーリングが構成されていて、まだ配信されていないトランザクションがある場合 sync with backup オプションが ON に設定されているディストリビューションデータベースのバックアップが行われていない場合 CHECKPOINT が実行されていない場合 1. 実行中のトランザクションがある場合 実行中のトランザクションの有無は、GUI ではなく DBCC OPENTRAN コマンドにより確認します。 確認手順 [新しいクエリ] ボタン、もしくは、[ファイル] メニューの [新規作成] – [クエリを現在の接続で実行] から、Management Studio のクエリウィンドウを開き、以下を実行します。 DBCC OPENTRAN(‘データベース名’) 例 : DBCC OPENTRAN('test') 以下は、実行中のトランザクションがない場合の DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、実行中のトランザクションがあるからではありません。他の原因を確認する必要があります。 開かれたアクティブなトランザクションがありません。 以下は、実行中のトランザクションがある場合の DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、実行中のトランザクションがあるからです。トランザクションログファイルを小さくするためには、このトランザクションを終了した後に、STEP 2, 3 を実行する必要があります。 データベース 'test' のトランザクション情報。 最も古いアクティブなトランザクション: SPID (サーバー プロセス ID): 51 UID (ユーザー ID) : -1 名前 : user_transaction LSN : (43:2238:2) 開始時刻 : 12 12 2011 7:43:41:553PM SID : 0x0105000000000005150000005d28f57fd53ad8354354e02a481c0000 上で確認した SPID を使って以下のクエリを実行すると、このトランザクションを実行しているクライアントプロセスが実行されているマシン名やプログラム名、ログインユーザ名、PID 等を確認することができます。 select * from sys.dm_exec_sessions where session_id=上で確認した SPID 例 : select * from sys.dm_exec_sessions where session_id=51 対処方法 実行中のままになっているトランザクションの実行元マシン、アプリケーション名、PID などを確認できたら、そのアプリケーションがなぜトランザクションをずっと実行中のままなのかを確認しましょう。それが、正常と考えられる状況であれば、そのまま放置し、アプリケーションが処理を完了するのを待ちます。それが異常と考えられる状況であれば、アプリケーションを終了することで、トランザクションも終了させます。 もし、トランザクションを実行しているアプリケーションに対する操作ができないような場合には、以下を実行することで、SQL Server 側からこのトランザクションを強制的に終了させることも可能です。ただし、これを行うと、アプリケーション側ではエラーを受け取り、トランザクションはロールバックされます。 手順 オブジェクトエクスプローラーで対象 SQL Server インスタンスを右クリックし、「利用状況モニター」を起動します。 上で確認した SPID を右クリックし、「強制終了」をクリックします。 もう一度 DBCC OPENTRAN を実行して実行中のトランザクションがなくなっていることを確認後、再度 STEP 2 を実行します。 2. トランザクションレプリケーションが構成されていて、まだ配布されていないトランザクションがある場合 確認手順 [新しいクエリ] ボタン、もしくは、[ファイル] メニューの [新規作成] – [クエリを現在の接続で実行] から、Management Studio のクエリウィンドウを開き、以下を実行します。 DBCC OPENTRAN(‘データベース名’) 例 : DBCC OPENTRAN('test') 以下は、トランザクションレプリケーションの未配布トランザクションがない場合の、DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、トランザクションレプリケーションの未配布トランザクションがあるからではありません。 開かれたアクティブなトランザクションがありません。 以下は、トランザクションレプリケーションの未配布トランザクションがある場合の、DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、トランザクションレプリケーションの未配布トランザクションがあるからです。トランザクションログファイルを小さくするためには、この未配布トランザクションを配布した後に、STEP 2, 3 を実行する必要があります。 データベース 'test' のトランザクション情報。 レプリケートされたトランザクション情報: 配布された最も古い LSN : (39:46:1) 配布されなかった最も古い LSN : (39:47:1) ここで言う「配布 (distribution/distribute)」とは、ログリーダーエージェントが、パブリケーションデータベースのトランザクションログからトランザクションログを読み取り、読み取ったトランザクションの内容をディストリビューションデータベースに格納することです。 対処方法 配布されていないトランザクションがパブリケーションデータベースにある場合、それは、ログリーダーが正しく動いていない、もしくは、動けていないことが原因です。トランザクションログファイル内の未配布トランザクションを配布してトランザクションログファイルのサイズを小さくするためには、ログリーダーが動かない原因を排除して、レプリケーションを再開することが必要です。 手順 レプリケーションの状態を確認するために、[レプリケーション] を右クリックし、「レプリケーション モニターの起動」を起動して、レプリケーションの状態を確認してみましょう。 レプリケーション モニターでエラーが確認できる場合、「サブスクリプション ウォッチリスト」内の行をダブルクリックして、エラーの詳細が確認できます。 発生している可能性のあるエラーはいろいろとあるので、ここではその解決方法までは伝えられませんが、発生しているエラーの原因を特定して、その原因を排除し、レプリケーションを再開した後に STEP 2, 3 を実行すれば、トランザクションログファイルのサイズを小さくすることができます。 3. データベースミラーリングまたは可用性グループが構成されていて、まだ配信されていないトランザクションがある場合 データベースミラーリングと AlwaysOn 可用性グループもトランザクションレプリケーションと同様にトランザクションログをベースとした機能です。プリンシパルサーバー/プライマリレプリカからミラーサーバー/セカンダリレプリカへのトランザクション配信が行えなくなると、配信が可能になった時に配信を再開できるように、プリンシパルサーバー/プライマリレプリカ上のトランザクションログは、バックアップをしても削除されなくなります。この場合は、配信を再開することが、トランザクションログファイルのサイズを小さくするために必要なことです。 確認手順 データベースミラーリングおよび可用性グループの状態は、Management Studio のオブジェクトエクスプローラーで確認することができます。以下は、データベースミラーリングの場合の例です。 上の画像のように、「同期済み」となっている場合には、トランザクションログファイルが小さくならない原因は、ミラーリングや可用性グループではありません。 それ以外の場合、ミラーリングや可用性グループが原因である可能性があります。 対処方法 「同期中」である場合は、しばらく待った後、再度ミラーリングの状態を確認して下さい。 「接続解除」である場合は、ミラーサーバーが起動しているかどうかを確認して下さい。起動していない場合は、起動して下さい。 起動している状態でも「接続解除」になっている場合は、ping により、ネットワーク接続に問題がないかどうかを確認して下さい。ping が通る場合は、プリンシパルサーバー上で Management Studio や sqlcmd.exe から、ミラーサーバーとなっている SQL Server へ接続できるかどうか確認して下さい。これらのテストに失敗する場合は、名前解決を含むネットワークの問題やファイアウォールの設定の問題である可能性があるため、ネットワークやファイアウォールの設定を見直して下さい。 プリンシパル上の Management Studio や sqlcmd を用いてミラーサーバーに接続できる場合、プリンシパルサーバー上の SQL Server ERRORLOG (SQL Server 2019 既定のインスタンスの場合 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 下の ERRORLOG ファイル) を参照し、エラーの原因を突き止める必要があります。 ミラーリングの状態が「同期済み」となれば、STEP 2, 3 を実行することで、トランザクションログファイルのサイズを小さくすることができます。 4. sync with backup オプションが ON に設定されているディストリビューションデータベースのバックアップが行われていない場合 ディストリビューションデータベースの sync with backup オプションが有効になっている場合、トランザクションレプリケーションのパブリケーションデータベースでは、トランザクションログバックアップを行ったとしても、ディストリビューションデータベースのフルデータベースバックアップが完了するまではトランザクションログは切り捨てられません。 対処方法 ディストリビューションデータベースのフルデータベースバックアップを行います。ディストリビューションデータベースのフルデータベースバックアップ完了後、パブリケーションデータベースのトランザクションログバックアップを行います。 < 参考情報 > スナップショット レプリケーションおよびトランザクション レプリケーションのバックアップと復元の方式 https://docs.microsoft.com/ja-jp/sql/relational-databases/replication/administration/strategies-for-backing-up-and-restoring-snapshot-and-transactional-replication?view=sql-server-ver15 5. CHECKPOINT が実行されていない場合 確認手順 これが原因になることはほとんどありませんが、復旧モデルが「単純」で上の STEP 2, 3 を実行してもトランザクションログファイルサイズが小さくならない場合は、CHECKPOINT が実行されていないために、トランザクションログが切り捨てられていない可能性があります。 この状況に該当しているかどうかを判断するためには、sys.databases カタログビューの log_reuse_wait_desc を確認します。CHECKPOINT または XTP_CHECKPOINT となっている場合には、該当しています。 select name, log_reuse_wait_desc from sys.databases なお、インメモリ OLTP (メモリ最適化ファイルグループ) 機能を使用している場合には、パフォーマンスの観点から、最後の CHECKPOINT 以降に生成されたトランザクションログが 1.5GB 以上になるまでは自動的には CHECKPOINT は実行されないようになっています。そのため、インメモリ OLTP を使用していないデータベースに比べて、トランザクションログファイルの使用量が多くなる場合があります。この動作に起因して CHECKPOINT が自動的に実行されていない場合であっても、以下の方法で明示的に CHECKPOINT を実行すれば、トランザクションログは切り捨てられます。 対処方法 Management Studio のクエリウィンドウを開き、トランザクションログファイルのサイズを小さくしたいデータベースに変更した後に、CHECKPOINT コマンドを実行して下さい。 以下の例は、test データベースのトランザクションログファイルを小さくしたい場合に実行する CHECKPOINT コマンドの実行例です。 対象データベースを選択し、そのデータベース上で実行することがポイントです。 その後、STEP 3 を再実行して下さい。
パフォーマンスログ採取手順
こんにちは。SQL Server サポート チームです。 今回は、SQL Server で問題が起きた際の調査に使用するパフォーマンスログの採取方法についてご案内します。 ■概要 よく弊社にお問合せいただく問題の中で「クエリタイムアウトが発生した」、「接続タイムアウトが発生した」、「クエリのパフォーマンスが劣化した」といった調査にはパフォーマンスログの取得を依頼させていただいております。 パフォーマンスログからは、「CPU 利用率」や「メモリ使用量」、「ディスク I/O の使用量」、「SQL Server の動作状況」などの情報が含まれます。 これらを見ることで、SQL Server が動作していた時のリソースの使用状況などから問題の状況について確認し、通常時の情報と比較することで問題の傾向を確認することができます。 本記事では下記について記載いたします。 ・採取する際の注意事項 ・GUIを使用した採取手順 ・バッチを使用した採取手順 ・最大サイズを指定してローテーションさせるオプションの設定 ・特定のイベントが発生したタイミングでパフォーマンスログの採取を停止させる方法 ■1.採取する際の注意事項 パフォーマンスログの採取は比較的、サーバーへの負荷は低く、該当マシンへの影響は低いですが、万が一影響が出た場合には早急に停止してください。 また、ログの出力先は十分な空き領域がある事をご確認ください。 出力されるログの量は環境に依存するため、対象の環境で少しの間採取して、出力される量をご判断ください。 ■2.GUIを使用した採取手順 1) [パフォーマンスモニター] を検索し、起動します。 2) 左ペインの [データコレクタセット] を展開します。 3) [ユーザー定義] を右クリックし、[新規作成] -> [データコレクタセット] を選択します。 4) 名前を指定し、[手動で作成する] を選択した上、[次へ] を押します。 5) [データログを作成する] + [パフォーマンスカウンター] を選択し、[次へ] を押します。 6) 追加を選択します。 7) 以下の例に沿って、後述のパフォーマンスカウンターを追加していきます。 例1) 採取するカウンタが下記の場合 - [Processor] : 選択したオブジェクトのインスタンス：<すべてのインスタンス> % Privileged Time 「パフォーマンス オブジェクト」で [Processor] を展開、[% Privileged Time] を選択します。 「選択したオブジェクトのインスタンス」で <すべてのインスタンス> を選択します。 -> "追加" を押します。 例2) 採取するカウンタが下記の場合 - [Processor] : 選択したオブジェクトのインスタンス：<すべてのインスタンス> 全てのカウンタ 「パフォーマンス オブジェクト」で [Processor] を展開し、全てのカウンタが選択されていることを確認し、「選択したオブジェクトのインスタンス」で <すべてのインスタンス> を選択します。 -> "追加" を押します。 <採取対象のカウンタ一覧> - [Logical Disk] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Memory] : 全てのカウンタ - [Network Interface] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [NUMA Node Memory] 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Paging File] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Physical Disk] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Process] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Processor Information] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [Server] 全てのカウンタ - [Server Work Queues] 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [System] : Processor Queue Length、Context Switches/sec - [TCPv4] 全てのカウンタ - [TCPv6] 全てのカウンタ --------------------- ↓↓↓↓ 以下は SQL Server に関するカウンタとなります。 ↓↓↓↓ ※パフォーマンスオブジェクト名の "SQL Server" は、既定のインスタンスの場合となります。 名前付きインスタンスの場合、"SQL Server" の部分が "MSSQL$[名前付きインスタンス名]" となりますのでご注意下さい。 Windows Internal Database の場合、"SQL Server" の部分が "MSSQL$MICROSOFT##WID" となります。 --------------------- - [SQL Server: Access Methods] : 全てのカウンタ - [SQL Server: Availability Replica] : 全てのカウンタ ※このカウンターは AlwaysOn 可用性グループを構成している場合に追加してください。 - [SQL Server: Buffer Manager] : 全てのカウンタ - [SQL Server: Databases] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [SQL Server: Database Replica] : 全てのカウンタ ※このカウンターは AlwaysOn 可用性グループを構成している場合に追加してください。 - [SQL Server: General Statistics] : 全てのカウンタ - [SQL Server: Latches] : 全てのカウンタ - [SQL Server: Locks] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [SQL Server: Memory Manager] : 全てのカウンタ - [SQL Server: Memory Node] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ - [SQL Server: SQL Statistics] : 全てのカウンタ - [SQL Server: Transactions] : 全てのカウンタ - [SQL Server: Wait Statistics] : 選択したオブジェクトのインスタンス：すべてのインスタンス 全てのカウンタ 😎 全てのカウンタを追加した事を確認し、[OK] を押します。 9) サンプルの間隔を 3 秒へ設定し、[次へ] を押します。 10) [ルートディレクトリ] にデータの保存場所を選択し、[次へ] を選択します。 11) [保存して閉じる] を選択し [完了] を押します。 12) 左ペインの [データコレクタセット] -> [ユーザー定義] に作成したデータコレクタセットが表示されます。右クリックから、開始を選択することでカウンタの採取が開始します。 ※ 右クリックから、停止を選択することでカウンタの採取が停止します。 13) 10) で指定されたフォルダに作成されたログ ファイルを採取します。 ■3.バッチを使用した採取手順 このバッチは GUI を使用した採取手順でご案内したデータコレクタセットを、お客様の環境 (インスタンス名) に合わせて自動で作成するものとなっております。 バッチの既定の設定は下記となります。 必要に応じて[◆3.1.データコレクタセットの名称、データコレクタセットのルートディレクトリ (データの保存場所) の変更方法]や[◆3.2.データコレクタセットのサンプルの間隔の変更方法]の手順を参考に変更ください。 データコレクタセットの名称：mytest データコレクタセットのルートディレクトリ (データの保存場所) ：bat ファイルと同じディレクトリの Log フォルダ配下 データコレクタセットのサンプルの間隔： 3 秒 また、データコレクタセットの作成後は、◆3.3.の手順を参考に正しくカウンタが登録されていることをご確認のうえ、データを取得いただくようお願いいたします。 ◆3.1.データコレクタセットの名称、データコレクタセットのルートディレクトリ (データの保存場所) の変更方法 本手順は必須ではないため、必要に応じて実施してください。 採取用スクリプト (PerfCollector_v1.0.zip) 内の create.bat を編集し、下記の変数 collectorname, location の値を書き換えることで作成するデータコレクタセットの設定を変更します。 set collectorname=mytest set location=.\logs ※データコレクタセット作成後に GUI で変更することも可能です。 ◆3.2.データコレクタセットのサンプルの間隔の変更方法 本手順は必須ではないため、必要に応じて実施してください。 下記の [◆3.3.データコレクタセットのカウンター確認方法] の手順 1-3 を実施し、手順 4 の [プロパティ] の [サンプル間隔] で変更できます。 ◆3.3.データコレクタセットのカウンター確認方法 1) [パフォーマンスモニター] を検索し、起動します。 2) 左ペインの [データコレクタセット] を展開します。 3) [ユーザー定義] を展開し、左ペインで変更したいデータコレクタセット (画像では mytest です) をクリックし、変更したい [パフォーマンスカウンター] (画像は mytest です) を右クリックし、プロパティを開きます。 4) [プロパティ] より現在追加されているパフォーマンスカウンターを確認します。 ◆3.4.（実行方法） 1) 採取用スクリプト (PerfCollector_v1.0.zip) を資料採取対象のマシンの任意の場所にコピーし展開します。 2) [コマンドプロンプト] を検索し、[管理者として実行] より起動します。 3) ポップアップが表示されますので "はい" をクリックします。 4) タイトルに "管理者: コマンドプロンプト" が表示されていることをご確認します。 5) PerfCollector_v1.0 を展開したフォルダーに移動し、PerfCollector_v1.0 を以下のように実行します。 >create 6) コマンドプロンプトでエラーが返ってこないことを確認します。 7) [◆3.3.データコレクタセットのカウンター確認方法] を参照して、登録されたカウンタを確認します。 8 ) [■2.GUIを使用した採取手順] の手順 12 以降を参照して、情報採取を開始してください。 ■4.最大サイズを指定してローテーションさせるオプションの設定 本手順は必須ではないため、必要に応じて実施してください。 1) [パフォーマンスモニター] を検索し、起動します。 2) 左ペインの [データコレクタセット] を展開します。 3) [ユーザー定義] をクリックし、変更したい [データコレクタセット] (画像は mytest です) を右クリックし、プロパティを開きます。 4) [停止条件] タブにて、"最大サイズ" チェックボックスをオンにし、ログ サイズを指定して [OK] ボタンをクリックします。 ここで指定したサイズが小さいと現象発生時の情報が上書きされる可能性がございますため、指定するサイズには十分ご注意ください。 ※ 一定期間採取を実施していただき、その実績を基に設定する方法をご検討ください。 ※ 循環設定での最大サイズの上限は 4GB 未満となります。そのため、最大サイズは 1GB ~ 3.5GB 程度に設定することをお奨めいたします。(画像は 2GB です。) ※ 循環設定した状態で OS を再起動すると、取得したパフォーマンスログが高い確率で破損するため、再起動前にデータコレクタセットでの採取を停止ください。 5) 変更したい [データコレクタセット] (画像では mytest です) をダブルクリックし、右ペインのパフォーマンス カウンタ DataCollector01 を右クリックしプロパティを選択します。 6) [ファイル] タブにて、[ログ モード] より "循環" を選択し、[OK] ボタンをクリックします。 以上の手順によって、設定ができました。 ■5.特定のイベントが発生したタイミングでパフォーマンスログの採取を停止させる方法 本手順は必須ではないため、必要に応じて実施してください。 1) [タスクスケジューラ] を検索し、起動します。 2) 右ペインの [タスクの作成] をクリックします。 3) [タスクの作成] ウィザード [全般] より、タスクの名前を入力し、[ユーザーがログオンしているかどうかにかかわらず実行する(W)]と[最上位の特権で実行する(I)] にチェックを入れます。(画像はタスクの名前を test としています) 4) [タスクの作成] ウィザード [トリガー] -> [新規] で [新しいトリガー] ウィザードが開かれるので、[タスクの開始] より [イベント時] を選択し、イベントについての設定をします。 問題となる事象に関する[イベント ID]を入力します。また、[遅延時間を指定する] にチェックを付け、15分間を選びます。 5) [タスクの作成] ウィザード [操作] -> [新規] で [新しい操作] ウィザードが開かれるので、[操作] より [プログラムの開始] を選択し、 [設定] -> [プログラム/スクリプト] に cmd.exe と入力し、[引数の追加] に /C "logman stop xxx" を入力します。( xxx はデータコレクタセットの名前を入れます。画像では mytest というデータコレクタセットを指定しています) 設定が終わったら [新しい操作] ウィザード [OK] を押して、操作の作成を完成します。 6) [タスクの作成] ウィザード [条件] -> [電源] より、必要に応じて [コンピュータを AC 電源で使用している場合のみタスクを開始する] のチェックを外す。 7) [タスクの作成] ウィザード [OK] より、タスクの作成を完了する。 以上の手順によって、設定ができました。 上記手順で採取した情報をマイクロソフトのサポートまでお寄せいただきますようお願いいたします。 どうぞ、よろしくお願いいたします。
拡張セキュリティ更新プログラム (ESU) について
こんにちは、 SQL Server サポートです。 今回は、SQL Server サポート期間とサポート終了後の拡張セキュリティ更新プログラム (ESU)について紹介します。 （目次）========================== SQL Server サポート期間について 拡張セキュリティ更新プログラム (ESU)について 拡張セキュリティ更新プログラム (ESU)適用の準備 拡張セキュリティ更新プログラム (ESU)適用の流れ 拡張セキュリティ更新プログラム (ESU)のよくある質問 ========================== SQL Server サポート期間について SQL Server に関するサポートをご利用の際に注意する点として、SQL Server のバージョンごとのサポート期間があります。 １つの製品にはメインストリームサポートの期間と延長サポートの期間があります。 メインストリームサポートは製品リリースから約５年間提供されます。サポート内容は、製品の不具合修正、設計変更要求、セキュリティの脆弱性対応などが行われ、テクニカルサポートへの問い合わせもほぼすべてのサポート契約で可能です。 延長サポートはメインストリームサポートが終了してから約５年間提供されます。サポート内容は、セキュリティの脆弱性など重大な問題のみ対応が行われ、テクニカルサポートへのお問い合わせはプレミアサポート契約や有償のインシデントパックなどの契約が必要です。 ※本文章は５年間のサポート提供を約束するものではありませんので、各製品の具体的なサポート期間はサポートライフサイクルをご確認ください。 ※テクニカルサポートへの問い合わせ可否は各サポート契約をご確認ください。 延長サポート期間の終了後は、定期的なセキュリティ更新プログラムが提供されなくなり、テクニカルサポートへの製品の問い合わせができなくなることを意味しています。 延長サポート期間終了後のバージョンで運用すると、セキュリティやコンプライアンスのリスクが大幅に高まります。そのため、お客様には最新バージョンにアップグレードすることを強くお勧めしています。 一方、新しいバージョンにアップグレードするためにはアプリケーションの動作検証なども含めて様々な作業が必要となり、時間を要することが考えられます。 そのようなお客様のためにマイクロソフトは特定の製品について拡張セキュリティ更新プログラム (Extended Security Update, 以下 ESU ) をご提供しています。 拡張セキュリティ更新プログラム (ESU) については、下記で紹介します。 拡張セキュリティ更新プログラム (ESU)について 拡張セキュリティ更新プログラム (ESU) は、延長サポート期間終了後の対象製品に対して、セキュリティに関する更新プログラムのご提供及びサポートへの製品の問い合わせが可能となるプログラムになります。 ※ バージョンや製品によって提供有無および提供期間が異なりますのでご注意ください。 製品ライフサイクルに関する FAQ - 拡張セキュリティ更新プログラム | Microsoft Docs 拡張セキュリティ更新プログラム (ESU) を受けるためには２つの方法があります。 ・Azure への移行 仮想マシンの利用料金と SQL Server ライセンスの利用料金以外の Azure の追加課金はございません。 SQL Server のライセンス持ち込みの場合はライセンス利用料金はかかりません。 ・オンプレ環境での ESU の契約 オンプレミス環境を対象とした ESU の費用を支払う方法は、以下2種類があります。 - 時間単位の従量課金 インターネット等を経由して Azure Arc に常に接続している必要があり、時間単位で費用が請求されます。 - 年単位 EA, EAS, SCE, EES などに基づくソフトウェアアシュアランス付きのライセンスを利用している場合、1年目、2年目、3年目の単位で費用を支払うことができます。 ご参考） Azure Arc によって有効化された SQL Server 拡張セキュリティ更新プログラム：ESU サブスクリプションの請求について 拡張セキュリティ更新プログラム (ESU)適用の準備 拡張セキュリティ更新プログラムは Azure Portal を経由して配布されるため、Azure Portal への登録が必要になります。 ESU費用は、年単位で支払う方法と従量課金で支払う方法の2パターンがあります。 ESU費用を年単位で支払う場合、Azure 試用版をご利用いただくことが可能ですが、試用期間は12か月のため、経過後は従量課金制に移行するか異なるアカウントで試用版を利用しての再作成が必要になります（※）。 ※ESU 費用を従量課金で支払う場合 異なるアカウントを再作成すると異なる環境に対する ESU の登録であると判断され、すでに支払い済みの ESU の費用も過去にさかのぼって重複して請求されるため、再作成せずに、従量課金制に移行して継続でご使用ください。Azure Arc のみの使用であれば、 ESU の従量課金費用以外に請求はありません。 ■無料アカウントサインアップ手順 1. Web ブラウザーで、Azure の無料アカウントを今すぐ作成する | Microsoft Azure にアクセスします。 2. [開始 (無料)] を選択します。 3. Microsoft または GitHub のアカウントでサインインするか、無料の Microsoft アカウントを作成します。 4. [自分の情報] ページで、適切な国またはリージョンを選択します。 氏名、メール アドレス、電話番号を入力します。 お住まいの国によっては、VAT 番号など、追加のフィールドが表示される場合があります。 [次へ] を選択して続行します。 5. [電話による本人確認] 画面で、国番号を選択し、自分がすぐに使える電話の番号を入力します。 6. テキストまたは通話で確認コードを取得できます。 関連するボタンを選択し、[確認コード] ボックスにコードを入力して、[コードの確認] を選びます。 7. 確認コードが正しい場合は、有効なクレジット カードの詳細を入力するように求められます。 カード情報を入力し、[次へ] を選択します。 8. 最後の手順では、契約とプライバシーに関する声明を確認し、[サインアップ] を選択します。 登録後は Azure Portal（https://portal.azure.com/） より、作成したアカウントでサインイン可能です。 ご参考）Azure 試用版利用方法 Azure 無料アカウントを使用して無料サービスを作成する - Microsoft Cost Management | Microsoft Learn ■最新のサービスパック適用 拡張セキュリティ更新プログラムを適用するには、ご利用のバージョンの最新の Service Pack を適用しておく必要があります。 参考として、2024年7月18日現在の公開情報の内容を記載します。 拡張セキュリティ更新プログラム FAQ - SQL Server | Microsoft Learn ***** 抜粋 ***** 拡張セキュリティ更新プログラムのメリットを活用するためには、最新の SQL Server Service パックを実行している必要がありますか? はい。ESU を適用するために、最新の Service パックを使用して SQL Server を実行する必要があります。 Microsoft では、最新の Service パックに適用可能な更新プログラムのみを作成します。 ***** 抜粋 ***** 拡張セキュリティ更新プログラム (ESU)適用の流れ - Azure Azure 上の環境で、SQL 仮想マシンの自動修正が有効化している環境は自動的に適用されます。 ご参考）Azure 仮想マシンでの SQL Server の自動修正 (Resource Manager) SQL Server VM の自動修正 (Resource Manager) - SQL Server on Azure VMs | Microsoft Docs - オンプレミス 下記の公開情報に記載の手順を参考に、Azure Arc から更新プログラムを取得してください。 延長セキュリティ更新プログラムとは - SQL Server | Microsoft Learn 従量課金でのESUの購入はこちらを参照ください。 Extended Security Updates - SQL Server enabled by Azure Arc | Microsoft Learn 拡張セキュリティ更新プログラム (ESU)のよくある質問 ESU に関するよくある質問は、こちらのリンクをご確認ください。 拡張セキュリティ更新プログラム FAQ - SQL Server | Microsoft Learn 製品ライフサイクルに関する FAQ - 拡張セキュリティ更新プログラム | Microsoft Learn 以下は、特によくお問い合わせいただくご質問です。 Q. 拡張セキュリティ更新プログラム (ESU) がリリースされたかどうかはどこで確認すればよいですか。 A. ESU がリリースされた際は、Azure Arc の画面のほか、下記サイトで確認できます。 セキュリティ更新プログラム ガイド - Microsoft SQL Server の最新の更新プログラムとバージョン履歴 - SQL Server | Microsoft Learn 延長セキュリティ更新プログラムとは - SQL Server | Microsoft Learn Q. Azure Arc の登録に費用はかかりますか。 A. Azure ArcにSQL Serverを登録する費用はかかりません。 ESUをオンプレミス環境のSQL Serverに適用する際の追加費用としては、ESUをご購入いただく分のみとなりますのでご安心ください。 Q. Azure で稼働中のSQL Server 2012 へ 手動でESU の適用ができますか。 A. はい、可能です。 Azure VM にてSQL Server を利用されるお客様は、修正プログラムの自動適用が無効にされている場合、Windows Server の「更新プログラムの確認」にて、ESU の確認及びインストールすることが可能です。 Q. オンプレミス環境の SQL Server 2012 へ自動でESUを適用する方法はありますか。 A. Azure Arc に接続することでパッチの自動インストールが可能です。 SQL Server を Azure Arc に自動的に接続する - SQL Server enabled by Azure Arc | Microsoft Learn Q.SQL Server 2012 ESUの利用は、Azure サブスクリプションへの登録が必要と認識しています。どのサブスクリプションに登録すればよいですか。 A. ESU を利用するためのサブスクリプションに制限事項はありません。 すでに登録いただいている既存のサブスクリプションでも、新規作成したものでもどちらでも問題ありませんので、ご都合のいいサブスクリプションをご利用ください。 Q.Azure Arc での対象のインスタンス登録時に[請求書ID] セクションに入力するESU 請求書番号とは何でしょうか。 A.ESU請求番号（またはESU ID）とは、ESU ライセンスをパートナー様から購入した際に払い出される番号となります。購入時の請求書をご確認ください。 登録いただくことで、お客様にて、対応する ESU の購入情報との組み合わせの管理にご利用いただけるものとなります。 Q. SQL Server 2012 のESU を利用するためにAzure の手続きの前提条件として「Azure Connected SQL Server Onboarding ロールを割り当てます。」とありますが、どこに割り当てるのでしょうか。 A.SQL Server のインスタンスの登録を行うユーザーに対して[Azure Connected SQL Server Onboarding] (日本語の場合は[Azure Connected SQL Server オンボード])ロールを付与してください。 Q.ESUを購入したらサポートへの問い合わせができますか。 A.ESU をご購入済みかつサポートプランをお持ちのお客様は、サポートへのお問い合わせが可能となります。 Azure に移行した場合は、Azure サポート プランで技術サポートを受けられます。 Q.ESUの2年目のみ購入するなど、一部だけ購入することはできますか。 A. いいえ、できません。 ESUの 1 年目を購入していない場合は、1 年目と 2 年目の拡張セキュリティ更新プログラムを購入する必要があります。同様に、 3 年目に初めて購入する場合は、3 年間の拡張セキュリティ更新プログラムをすべて購入する必要があります。 ESU費用を従量課金で支払う場合、 "ESU の提供開始時刻" から "従量課金での支払い登録を行った時刻" までの累積の時間単位の料金が最初の1時間以内に請求されます。つまり、ESU 提供開始から半年後に従量課金の支払い登録を行った場合、半年分の ESU 費用が最初の一時間以内に請求されます。 Q.ESUの2年目（3年目）を購入したのですが、Azure Arc上で再度インスタンスを登録する必要がありますか。 A. 1年目および2年目の購入時に Azure Arc にすでに登録済みの場合は、既存の登録済みのインスタンスに対し、新しく購入した請求書IDを紐づけることで Azure Arc上の ESU 有効期限が更新されます。 ESUの3年目をご利用になる場合は、そこまでのESUも購入する必要があるため、3年目を登録時には1,2,3 年目すべてのESU請求書番号がインスタンスに紐づけられた状態で登録します。 //画面例 請求書ID登録後、有効期限が更新されます。 //3年目のESUご利用の場合、1-3年目すべてのESU請求書番号を登録します。
SQL Server R Services またはSQL Server Machine Learning Servicesをオフラインでインストールまたは更新プログラムを適用するための方法
こんにちは SQL Server サポートチームです。 今回は、SQL Server R Services またはSQL Server Machine Learning Servicesをオフラインでインストールまたは更新プログラムを適用するための方法について紹介します。 日本語版のSQL Server をご利用いただいているお客様より、オフライン環境でSQL Server R Services またはSQL Server Machine Learning Servicesをインストールまたは更新プログラムを適用した際に、失敗するというお問い合わせをいただくことがあります。 これはSQL Server の不具合が原因となりますが、修正目途がたっていない状況となりますため、ブログで回避策を紹介させていただきます。 作業の流れは下記の通りとなります。 １．SQL Server R Services またはSQL Server Machine Learning Servicesをオフラインでインストールする場合 1) インターネットに接続できる環境で、リリースバージョンにあったcab ファイルをダウンロードします。 各リリースバージョンのcabファイルのダウンロード URL は、次のページをご確認ください。 SQL Server 2019 の場合： https://learn.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-ver15#1---download-2019-cabs SQL Server 2017 の場合： https://learn.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-2017#1---download-2017-cabs SQL Server 2016 の場合： https://learn.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-2016#1---download-2016-cabs 2) 適用対象のサーバーに、cabをコピーします。 ※ 任意のパスで結構です。ここでは例として、C:\Temp にコピーするとします。次からの手順は、C:\Temp 配下にコピーした場合の手順となります。 3) 各cabファイルの末尾が1033となっているため、1041にコピーします。 例）SQL Server 2019 の場合 SPO_4.5.12.120_1033.cab -> SPO_4.5.12.120_1041.cab SPS_9.4.7.25_1033.cab -> SPS_9.4.7.25_1041.cab SRO_3.5.2.125_1033.cab -> SRO_3.5.2.125_1041.cab SRS_9.4.7.25_103 3.cab -> SRS_9.4.7.25_1041.cab この結果C:\Temp 配下に、次のファイルが存在することになります。 SPO_4.5.12.120_1033.cab SPO_4.5.12.120_1041.cab SPS_9.4.7.25_1033.cab SPS_9.4.7.25_1041.cab SRO_3.5.2.125_1033.cab SRO_3.5.2.125_1041.cab SRS_9.4.7.25_1033.cab SRS_9.4.7.25_1041.cab 4) インストールを行います。 ※ [Microsoft Machine Learning Server コンポーネントのオフライン インストール] では [インストール パス] に、「C:\Temp」を指定します。 4-1) インストール メディアを開き、setup.exe を右クリックし、管理者として実行します。 4-2) セットアップ ウィザードで、オープンソースの R または Python コンポーネントに対するライセンスのページが表示されたら、 [同意する] をクリックします。 ライセンス条項に同意すると、次の手順に進むことができます。 4-3) [オフライン インストール] ページが表示されたら、 [インストール パス] に、以前にコピーした CAB ファイルが格納されているフォルダーを指定します。 図1．Microsoft Machine Learning Server コンポーネントのオフライン インストール画面 4-4) 画面の指示に従って続行し、インストールを完了します。 参考情報 セットアップの実行 https://docs.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-ver15#run-setup 2．SQL Server R Services またはSQL Server Machine Learning Servicesの更新プログラムをオフラインで適用する場合 1) インターネットに接続できる環境で、更新プログラムにあったcab ファイルをダウンロードします。 各更新プログラム用のcabファイルのダウンロード URL は、次のページをご確認ください。 SQL Server 2019 の場合： https://docs.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-cab-downloads?view=sql-server-ver15#sql-server-2019-cabs SQL Server 2017 の場合： https://docs.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-cab-downloads?view=sql-server-2017#sql-server-2017-cabs SQL Server 2016 の場合： https://docs.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-cab-downloads?view=sql-server-2016#sql-server-2016-cabs 2) 適用対象のサーバーに、cabをコピーします。 ※ 任意のパスで結構です。ここでは例として、C:\Temp にコピーするとします。次からの手順は、C:\Temp 配下にコピーした場合の手順となります。 3) 各cabファイルの末尾が1033となっているため、1041にコピーします。 例）SQL Server 2019 CU8 の場合 SPO_4.5.12.479_1033.cab -> SPO_4.5.12.479_1041.cab SPS_9.4.7.958_1033.cab -> SPS_9.4.7.958_1041.cab SRO_3.5.2.777_1033.cab -> SRO_3.5.2.777_1041.cab SRS_9.4.7.958_1033.cab -> SRS_9.4.7.958_1041.cab この結果C:\Temp 配下に、次のファイルが存在することになります。 SPO_4.5.12.479_1033.cab SPO_4.5.12.479_1041.cab SPS_9.4.7.958_1033.cab SPS_9.4.7.958_1041.cab SRO_3.5.2.777_1033.cab SRO_3.5.2.777_1041.cab SRS_9.4.7.958_1033.cab SRS_9.4.7.958_1041.cab 4) インストールを行います。 ※ [Microsoft Machine Learning Server コンポーネントのオフライン インストール] では [インストール パス] に、「C:\Temp」を指定します。 4-1) 更新プログラムのインストーラーを実行します。ライセンス条項に同意し、[機能の選択] ページで、累積的な更新プログラムが適用される機能を確認します。 機械学習機能を含む、現在のインスタンスにインストールされているすべての機能が表示されます。 図2. 機能の選択画面 4-2) ウィザードを続行し、R および Python ディストリビューションのライセンス条項に同意します。 [オフライン インストール] ページが表示されたら、 [インストール パス] に、以前にコピーした CAB ファイルが格納されているフォルダーを指定します。 図3. Microsoft Machine Learning Server コンポーネントのオフライン インストール画面 参考情報 累積的な更新プログラムの適用 https://docs.microsoft.com/ja-jp/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-ver15#apply-cumulative-updates ※ 本Blogの内容は、2021年5月現在の内容となっております。
Windows 上で動作するSQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法
こんにちは。SQL Server サポート チームです。 今回は、Windows 上で動作するSQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法についてご案内します。 SQL Server ではインスタンスに接続する際の認証に使用する（サーバー全体の権限を管理する）ログインという名称のプリンシパルと、インスタンスに接続した後に各データベースに接続する際の認証に使用する（データベースの権限を管理する）ユーザーという名称のプリンシパルが存在します。 本記事ではログインという表記の場合はログインプリンシパルを表し、ユーザーという表記の場合はユーザープリンシパルを意図したものとなります。 一般的にクライアントは、ログインプリンシパルを指定して SQL Server のインスタンスに接続し、ログインプリンシパルとマッピングされたユーザープリンシパルの権限を用いて、データベースにアクセスします。 下記図のようになります。 ■概要 Server A で稼働しているインスタンスにあるデータベースを、別の Server B のインスタンスに移行（バックアップリストア/デタッチアタッチなど）すると、以下のようなエラーが発生して、Server B のインスタンスに接続ができない場合があります。 ユーザー ' ログイン名 'の ログインに 失敗しました。 (Microsoft SQL Serverエラー: 18456) この問題は、Server A の SQL Server のインスタンスに存在していたログインが、Server B の SQL Server のインスタンスに存在しないために発生することがあります。 下記図が問題のイメージです。 この問題への対処は、Server B で適切なユーザーとマッピングさせたログインを新規に作成するか、本記事で説明する方法で Server A から Server B にログインをコピーします。 また、Server B のインスタンスに Server A と同じ名称のログインを作成して、Server B に移行したデータベースにアクセスしようとしたときに下記のようなエラーが発生して、データベースに接続ができない場合があります。 現在のセキュリティ コンテキストでは、サーバー プリンシパル "<ログイン名>" はデータベース "<データベース名>" にアクセスできません。 この問題は、Server A と Server B でログインの名称が同じでも内部的にログインを識別する SID が異なっていることが原因で発生します。 下記図が問題のイメージです。 この問題への対処は、 ALTER User を使用して Server B 上でログインとユーザーを再度マッピングするか、本記事で説明する方法で Server A から Server B にログインをコピーします。 本記事では、Server A のインスタンスに存在するログインをパスワードを保ったまま別の Server B のインスタンスにコピーする対応策について紹介します。 ■前提条件 ・本作業はすべて sysadmin ロールの権限を持ったログインにて実施します。 ■手順 1. SQL Server Management Studio（以下、SSMS）やその他のクライアントツールを使って Server A のインスタンスに接続します。 以下スクリプトを実行して、ログインとそのパスワードを転送するのに必要なスクリプトを生成するためのストアドプロシージャを作成します。 USE [master] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE [dbo].[sp_hexadecimal] ( @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT ) AS BEGIN DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue END go IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE [dbo].[sp_help_revlogin] ( @login_name sysname = NULL ) AS BEGIN DECLARE @name SYSNAME DECLARE @type VARCHAR (1) DECLARE @hasaccess INT DECLARE @denylogin INT DECLARE @is_disabled INT DECLARE @PWD_varbinary VARBINARY (256) DECLARE @PWD_string VARCHAR (514) DECLARE @SID_varbinary VARBINARY (85) DECLARE @SID_string VARCHAR (514) DECLARE @tmpstr NVARCHAR (1024) DECLARE @is_policy_checked VARCHAR (3) DECLARE @is_expiration_checked VARCHAR (3) Declare @Prefix VARCHAR(255) DECLARE @defaultdb SYSNAME DECLARE @defaultlanguage SYSNAME DECLARE @tmpstrRole NVARCHAR (1024) IF (@login_name IS NULL) BEGIN DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' AND p.name not like '##%' ORDER BY p.name END ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name ORDER BY p.name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary,@name , @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''') BEGIN' Print @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END SET @Prefix = ' EXEC master.dbo.sp_addsrvrolemember @loginame=''' SET @tmpstrRole='' SELECT @tmpstrRole = @tmpstrRole + CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END + CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END + CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END + CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END + CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END + CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END + CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END + CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END FROM ( SELECT CONVERT(NVARCHAR(100),SUSER_SNAME(sid)) AS [LoginName], sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin FROM sys.syslogins WHERE ( sysadmin<>0 OR securityadmin<>0 OR serveradmin<>0 OR setupadmin <>0 OR processadmin <>0 OR diskadmin<>0 OR dbcreator<>0 OR bulkadmin<>0 ) AND name=@name ) L PRINT @tmpstr PRINT @tmpstrRole PRINT 'END' END FETCH NEXT FROM login_curs INTO @SID_varbinary,@name , @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage END CLOSE login_curs DEALLOCATE login_curs RETURN 0 END 2. 下記のステートメントを実行して、マスターデータベースに2つのストアドプロシージャ sp_hexadecimal と sp_help_revlogin が作成されたことを確認します。 name 列がそれぞれのストアドプロシージャになっている行が出力されていれば、作成されていると判断できます。 USE [master] GO SELECT * FROM sys.procedures WHERE name IN ('sp_hexadecimal','sp_help_revlogin') GO 3. SSMS のクエリ エディタを一度選択した状態で、SSMS のメニューバーから [クエリ] -> [結果の出力] -> [結果をテキストで表示] オプションを選択します。 4. [結果をテキストで表示] 設定を行ったクエリ エディタ―で下記のステートメントを実行します。 EXEC sp_help_revlogin 5. sp_help_revlogin ストアドプロシージャを実行することで、ログインを作成するためのスクリプトが出力されます。 IF NOT ~ END までが一つの塊となるため、Server B にコピーしたいログインに関するスクリプトを抜粋します。 なお、このスクリプトで作成した SQL Server 認証のログインは、元の SID と元のパスワードを保持したものが作成されます。 ・ SQL Server 認証のログイン（ sqlUser01）の例 -- Login: sqlUser01 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sqlUser01') BEGIN CREATE LOGIN [sqlUser01] WITH PASSWORD = <暗号化されたパスワード> HASHED, SID = <ハッシュ>, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = ON END ・Windows 認証のログイン（ <ドメイン名>\winUser01）の例 -- Login: <ドメイン名>\winUser01 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<ドメイン名>\winUser01') BEGIN CREATE LOGIN [<ドメイン名>\winUser01] FROM WINDOWS WITH DEFAULT_DATABASE = [master] END 6. 移行先のサーバー（Server B）での手順 SSMSなどのクライアントツールを使って Server B に接続し、手順5 で抜粋したスクリプト（sp_helprevloginの出力結果）をクエリとして実行します。 ■注意事項 手順6 で移行先のサーバー（Server B）での手順を実行する前に、以下の情報を確認してください。 Server A とServer B が異なるドメインにある場合で、 Windows 認証のログインをコピーする場合は、出力スクリプトを変更する必要があります。 手順5 で抜粋した CREATE LOGIN ステートメントには元のドメイン名が記載されているため、新しいドメイン名を使って置き換える必要があります。 新しいドメインでアクセスを許可された統合ログインは、元のドメインのログインと同じSIDを持たないため、ユーザーはこれらのログインから孤立した状態となります。 これらの孤立したユーザーを解決する方法の詳細については、「SQL Serverを実行しているサーバー間でデータベースを移動するときの権限の問題を解決する方法」を参照してください。 Server A とServer B が同じドメイン内にある場合は、同じ SID が使用されますので、ユーザーが孤立することは通常ありません。 この記事の手順では、特定のログインに対するデフォルトのデータベース情報の転送はされません。 これは、デフォルト データベースが Server B に存在するとは限らないためです。 送信元サーバーと送信先サーバーの照合順序の違いによる注意点： 大文字小文字を区別しない Server A と大文字小文字を区別する Server B： Server A の照合順序が大文字小文字を区別せず、Server B の照合順序が大文字小文字を区別する場合があります。 この場合、Server B のインスタンスにログイン名とパスワードを転送した後、ユーザーはパスワードをすべて大文字で入力する必要があります。 大文字小文字を区別する Server A と大文字小文字を区別しない Server B： Server A の照合順序が大文字小文字を区別し、Server B の照合順序が大文字小文字を区別しない場合があります。 この場合、以下のいずれかの条件が成立しない限り、ユーザーは Server B のインスタンスに転送したログイン名とパスワードを使ってログインすることができません。 ・元のパスワードのすべての文字が大文字である。 ・元のパスワードに文字が含まれていない。 両方のサーバーで大文字と小文字が区別されている、または大文字と小文字が区別されていない： Server A とServer B の両方のソート順が大文字と小文字を区別している場合や、Server A と Server B の両方のソート順が大文字と小文字を区別していない場合があります。 これらの場合、ユーザーには問題が発生しません。 Server B 上のインスタンスで出力スクリプトを実行した際に以下のようなエラーメッセージが表示された場合： Msg 15433、レベル 16、状態 1、Line 1 の指定されたパラメーター sid が使用されています。 →Server B 上のインスタンスに出力スクリプト内の名前と同じ名前を持つログインがすでに存在する。 Msg 15025、レベル 16、状態 1、行 1 サーバー プリンシパル 'MyLogin' は既に存在します。 →Server B 上のインスタンスに出力スクリプトの SID と同じ SID を持つログインがすでに存在する。 上記エラーを回避するために、次の操作を行う必要があります。 1. 出力スクリプト内のログイン情報を確認します。 2. Server B 上のインスタンスの sys.server_principals ビューの内容を調べます。 3. 必要に応じて、これらのエラー メッセージに対処します。 本記事は、インスタンス間でログインとパスワードを転送する - SQL Server | Microsoft Docs の情報を一部補足した内容となっております。
DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK)
インデックスの再構築 (ALTER INDEX REBUILD または DBCC DBREINDEX) を行う目的が、ページ密度を向上させて、データベースファイル内の使用領域サイズを小さくすることであれば、インデックス再構築後にデータベース圧縮を行っても、その目的は損なわれません。しかし、インデックスを再構築する目的が、論理断片化を解消し、検索パフォーマンスを向上させることであれば、インデックス再構築後にデータベース圧縮を行うと、その目的は達せられなくなります。 ページ密度 (Page Density) : ページ内でデータが占める割合。100% の場合、それ以上そのページにはデータが入らない、空き領域がない状態。ただし、行サイズとページサイズの関係上、100% になることはほとんどない。(例 : 1 行 100 バイトの場合、1 ページには、8060 バイトのデータが入るため、ギッシリ詰め込んでも 8060%100=60 と 60 バイトの空きは必ずできてしまう。) 論理断片化 (Logical Fragmentation / Logical Scan Fragmentation) 率 : ページの物理的な順番と論理的なリンクが異なる割合。ページ番号はファイルの先頭から順に 0, 1, 2 ... と振られているが、論理的なページのリンクが 81 の次に 48 など、ページ番号順になっていない割合。 なぜ？ インデックスを再構築すると、インデックスは再作成され、各インデックスページは FILLFACTOR の設定に従ってデータで埋められます。また、データは、可能な限り物理的な順番に並ぶように配置されます。その結果、ページ密度が高くなり、論理断片化が解消します。 データベースやデータベースファイルの圧縮 (DBCC SHRINKDATABASE や SHRINKFILE) を実行すると、ファイルの後ろの方にあるデータは前方に移動され、ファイルの後方に空き領域が作り出され、その作り出されたファイル後方の空き領域部分を切り捨てることで、データベースファイルサイズが小さくなります。 インデックスを再構築した後にデータベースやデータベースファイルの圧縮を行うと、せっかく物理的に並んだデータが再び移動させられ、物理的に並んだ状態ではなくなってしまいます。つまり、インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。 実際に見てみましょう ---------------------------------------------- -- データベースを作成します。 create database shrinktest go use shrinktest go -- あとでファイルの前方に空きを作るために、ダミーのテーブルを作成します。 -- 1 ページに1 行入る計算です。 create table dbo.dummy (c1 int primary key clustered, c2 nvarchar(4000)) go declare @i int set @i=0 while (@i<3000) begin insert into dbo.dummy values (@i, REPLICATE(N'X',4000)) set @i+=1 end -- 断片化を発生させるためのテーブルを作成します。 create table dbo.tab (c1 int primary key clustered, c2 nvarchar(800)) go -- 偶数キー値のデータを入れます。 declare @i int set @i=0 while (@i<100) begin insert into dbo.tab values (@i, REPLICATE('X',800)) set @i+=2 end -- 奇数キー値のデータを入れます。 -- これにより断片化が発生します。 declare @i int set @i=1 while (@i<100) begin insert into tab values (@i, REPLICATE('X',800)) set @i+=2 end go -- 断片化の状況を見てみましょう select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go 断片化率 63.3333333333333%、ページ密度 66.5678280207561% です。 index_type_ desc alloc_unit_ type_desc index_ depth index_ level avg_ fragmentation_ in_percent fragment_ count avg_ fragment_ size_ in_pages page_ count avg_page_space_ used_in_percent CLUSTERED INDEX IN_ROW_DATA 2 0 63.3333333333333 20 1.5 30 66.5678280207561 CLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 -- インデックスを再構築します。 alter table tab rebuild go -- インデックス再構築後の断片化の状況を見てみましょう。 select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go 論理断片化が 15% に低下し、ページ密度が 99.864096861873% まで上昇しました。 index_type_ desc alloc_unit_ type_desc index_ depth index_ level avg_ fragmentation_ in_percent fragment_ count avg_ fragment_ size_ in_pages page_ count avg_page_space_ used_in_percent CLUSTERED INDEX IN_ROW_DATA 2 0 15 5 4 20 99.864096861873 CLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 3.18754633061527 -- ダミーのテーブルを削除して、ファイル内に空き領域を作成します。 drop table dbo.dummy go -- データファイル圧縮を実行します。 dbcc shrinkfile('shrinktest') go -- データファイル圧縮後の断片化の状況を見てみましょう。 select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go ページ密度は変化ありませんが、断片化率が 90% まで上昇してしまいました。インデックス再構築する前よりも断片化が進んでしまったことになります。 index_type_ desc alloc_unit_ type_desc index_ depth index_ level avg_ fragmentation_ in_percent fragment_ count avg_ fragment_ size_ in_pages page_ count avg_page_space_ used_in_percent CLUSTERED INDEX IN_ROW_DATA 2 0 90 19 1.05263157894737 20 99.864096861873 CLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 3.18754633061527 圧縮処理により、ファイルの後ろの方にあるデータは前方に移動され、ファイルの後方に空き領域が作り出されます。 その作り出されたファイル後方の空き領域部分を切り捨てることで、データベースファイルサイズが小さくなります。インデックスを再構築した後にデータベースやデータベースファイルの圧縮を行うと、せっかく物理的に並んだデータが再び移動させられ、物理的に並んだ状態ではなくなってしまいます。 つまり、インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。 -------------- この記事は以下のBlogの画像のリンク切れを修正し、再掲したものになります。 DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK) | Microsoft Docs
