今回は仕事で発生した「SQL Serverのジョブが正常終了できない」現象について、詳しい内容とその対応方法を紹介します。
SQL Server 2017
Windows Server 2016
現象：ジョブが正常終了しない
会社では、SQL Serverの各データベースのフルバックアップを実行する処理をSQL Serverの「ジョブ」に登録して、夜間に自動実行させていました。
ある日突然、それまで正常に動いていたジョブに異常が発生しました。
- ジョブの起動はできるが終了しない
- バックアップ処理は終了できている(ファイルが出力されている)
- ジョブの履歴に何も書かれない(ジョブ開始ログも無い)
- SQL Serverエージェントのログにはエラーが書かれている
ジョブはSQL Server本体の機能ではなく、「SQL Serverエージェント」サービスが持っている機能です。
よって、SQL Server本体とは分離していますが、本体と密接に連携して動きます。
エージェントがおかしい場合でも、SQL Server本体は問題なく動作します。
ジョブの起動はできるが終了しない
まず、上記の「ジョブの起動はできるが終了しない」状態とは、例えばSQL Server Management Studio(以降SSMSと呼称)で登録されているジョブを手動で実行した場合は以下の画面が表示されます。
当画像では2行表示されており、一行目の「開始」行の状態は「成功」。
これはジョブの呼び出し自体が成功していることを表しています。
ジョブの処理が完了すると、2行目の「実行」行の状態も「成功」や問題があれば「失敗」と更新されるのですが、今回の事象では2行目の「実行」行はいつまで経っても状態が更新されませんでした。
ただ、ジョブが呼び出しているバックアップ処理自体は正常に完了しており、バックアップファイルの出力先にはバックアップファイルが出力できています。
よって、この2行目の状態が変わらないことは本来異常です。
ジョブの履歴に何も書かれない
また、「ジョブの履歴に何も書かれない」状態とは、例えばSSMSにてジョブの履歴は以下のように表示されます。
本来は、上記画像のように、ジョブを呼び出した回数分の行が表示されており、ジョブを呼び出してからジョブ内の個別のステップの実行結果なども含めて履歴として確認できるのですが、今回の事象では、ジョブの呼び出し履歴から全く書かれない状態になっていました。
その結果、以前にブログの記事で紹介している、ジョブの実行結果を取得するストアドも、ジョブの実行結果を取得できない状態になりました。
SQL Serverエージェントのログにはエラーが書かれている
また、「SQL Serverエージェントのログにはエラーが書かれている」状態ですが、実際に書かれていたエラーは以下です。
サーバー ”SQL Serverのホスト名” にログインできませんでした (ConnLogJobHistory)
SQLServer エラー: 53, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
ODBC エラー: 0、Login timeout expired [SQLSTATE HYT00]
SQLServer エラー: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
今回は、上記で「突然」と書きましたが、実は諸事情により同じタイミングでSQL Serverの累積パッチを充てて最新化をしていました。
それにより一部の内部的な挙動が変わってしまったのでしょう。
ジョブが終了できない原因
当項では、ジョブが終了できない現象に関する原因を紹介していきます。
私の環境では、大きく原因は二つ存在しました。
それを個々に説明していきます。
「SQL Server Browser」が起動していない
原因の調査をしていて気が付いたのは、SQL Serverを構成しているいくつかのサービスの一つの「SQL Server Browser」が起動していない。
「SQL Server Browser」の詳しい説明は以下のリンクを参照してください。
SQL Server Browser サービス(データベースエンジンとSSAS) -Microsoft公式ドキュメント
「SQL Server Browser」は主に、一つのサーバーに複数のSQLServerインスタンスを作成した場合に必要になります。
今回の環境では、インスタンスは一つしか無いので、SQL Server本体のデータベースを使用する分には必須のサービスという訳ではなかったはずですが、試しに「SQL Server Browser」サービスを起動して、SSMSからジョブを手動実行したところ、ジョブの終了自体はできるようになりました。
「SQL Server Browser」がジョブの実行にどのように関係しているのかは正直わからなかったのですが、私の環境では「SQL Server Browser」は起動している必要があるようでした。
ただ、ジョブは完了するようになったはずですが、やはりジョブの履歴にはジョブの起動時のログも、終了時のログも一切書かれないままです。
また、前述した「ジョブの実行結果を取得するストアド」も、相変わらず実行結果を取得できずに無限ループしていました。
よって、「SQL Server Browser」の開始だけでは不十分の様です。
ホスト名を過去に変更して旧ホスト名の設定が残っている
調査をしていて気になったのは、例えば前述した以下のSQL Server エージェントのログの内容です。
サーバー ”SQL Serverのホスト名” にログインできませんでした (ConnLogJobHistory)
この「SQL Serverのホスト名」の部分は、現在のホスト名ではなく、以前に設定していたホスト名が入っていました。
このサーバーは、当初別のホスト名を設定しており、そのホスト名を使用して本番環境切り替え前の検証などをしていました。
このサーバーを本番用DBサーバーとして切り替える際に、ホスト名を本番用の名前に変更して運用を開始しています。
その後、1年以上、今の状態で運用できており、その間にサーバーの再起動なども発生しておりますが、特に問題はありませんでした。
上記のエラーログに旧ホスト名が表示されているのは本来おかしい。
そこで、現在SQL Serverに設定されているホスト名を以下のSQLで確認します。
SELECT @@SERVERNAME
その結果、やはりサーバーの変更前の旧ホスト名が表示されます。
そこでホスト名を変更する場合の手順を調べたところ、本来はサーバーのホスト名を変更した後で、以下の設定をSQL Serverに実施する必要があるとのことでした。
EXEC sp_dropserver '旧ホスト名'; GO EXEC sp_addserver '新ホスト名', local; GO
SQL Serverのスタンドアロンインスタンスをホストするコンピューターの名前変更 -Microsoft公式ドキュメント
あぁ、確かにこの設定をするのは漏れていた・・・
ただ、既に今の状態で一年以上運用しており、SQL Server本体の根幹部分の設定でもある「ホスト名の変更」を今の段階で実施するのはリスクがあります。
よって、今回は以下の設定を入れることで対応しました。
「エイリアスローカルホストサーバー」の設定
SQL Serverエージェントのプロパティ→「ページの選択」内の「接続」を選択し「エイリアスローカルホストサーバー」を設定します。
ここには旧ホスト名ではなく、現在のホスト名を指定します。
このエイリアス(別名)を登録してあげることで、SQL Server エージェントから適切にSQL Server本体を見つけることができるようになり、ジョブの終了を正常に取得でき、且つ履歴にも実行状態が書かれるようになりました。
対応方法まとめ
- 「SQL Server Browser」サービスが起動しているか確認しよう
- 過去にホスト名を変更している場合は、ホスト名の設定に不整合が発生しているか確認しよう
【補足】SQL Server 2017の内部バージョンの違いによる挙動の差異
累積パッチを充てる前のSQL Server 2017環境と、累積パッチを充てた後のSQL Server 2017環境が用意できたため、ホスト名の扱いの違いを紹介しておきます。
累積パッチを充てる前の「サーバーのプロパティ」画面は以下です。
ウィンドウ上部のホスト名の表示と「名前」欄のホスト名は新ホスト名が入っています。
※ただ、SELECT @@SERVERNAME をすると、やはり内部的には旧ホスト名が設定されていることが確認できます。
バージョン：14.0.1000.169
次に、累積パッチを充てた後の「サーバーのプロパティ」画面は以下です。
ウィンドウ上部のホスト名には新ホスト名が表示されますが、「名前」欄のホスト名は旧ホスト名が表示されています。
バージョン：14.0.3381.3
この様に、「サーバーのプロパティ」画面だけで見ても、ホスト名の扱いが異なり、この部分の仕様の差異が今回の現象に繋がったと思われます。
それでは、今回も読んでいただきましてありがとうございました。
今回の記事が誰かの助けになれば幸いです。
また次回もよろしくお願いいたします。
