前回はローリング アップグレードということで、パッシブ ノードからパッチを適用していました。
アクティブ ノードからパッチを適用したらどうなるのだろうと思って試してみました。

Read the rest of this entry »

TechNet フォーラムを見ていたところ SQL Server 2008 R2 のローリングア ップグレードを使用したパッチ適用についての投稿がありました。

そういえば、SQL Server 2008 以降のクラスタ環境ではローリング アップグレードのシナリオが追加されていたなということを思い出し、良い機会だったので試してみました。
# Tech･Ed 2009 のセッションでの話があったということをブログに書いていたのですが試していませんでした…。

技術情報としては以下の情報が参考になります。
# フォーラムに記載されていた技術情報になります。2008 R2 でも同じ手順になります。
SQL Server 2008 フェールオーバー クラスタにローリング更新プログラムおよび Service Pack を適用する方法

今回は使用しないのですが以下のような技術情報もあります。
FIX: Error message when you perform a rolling upgrade in a SQL Server 2008 cluster : "18401, Login failed for user SQLTESTAgentService. Reason: Server is in script upgrade mode. Only administrator can connect at this time.[SQLState 42000]"
SQL Server フェールオーバー クラスター インスタンスをアップグレードする方法 (セットアップ)

Read the rest of this entry »

今回は [データベースの整合性確認の負荷] について振り返っていきたいと思います。

前回の振り返りでまとめていたデータベースの整合性確認 (DBCC CHECKDB) ですが、データベースの状態を確認するためには大事になる処理ですが、整合性を確認するためには負荷がかかります。

Read the rest of this entry »

先日、SQL Server 2005 SP4 が提供されました。
Microsoft SQL Server 2005 Service Pack 4 RTM

Windows Server 2008 のクラスタ環境にインストールした SQL Server 2005 にサービスパックを適用する場合には注意点がありますのでまとめてみたいと思います。
# 2008 以外でも発生するかもしれません。(2003 では発生しなかった気もするのですが)

SQL Server 2005 のメインストリームサポート終了日が [2011/04/12] と半年を切っており最新のサービスパック適用の機会があるかもしれないので軽く書いておこうかと。

 

今回は SP4 を対象としてまとめていますが、SP1 ～ SP3 , Cumulative Update に関しても同様の手順が必要になります。
# CU に関しては、発生するかは更新内容によって変わると思うのですが。

クラスタ環境で SQL Server 2005 のサービスパックをインストールする場合は、SQL Server のリソースを保持しているノードでサービスパックのインストールを実行します。

SQL Server のリソースを保持していないノードでサービスパックのインストールを実行しても、[MSSQLSERVER] (SQL Server ノーサービスコンポーネント) は選択をすることができません。

クラスタ環境の SQL Server 2005 でサービスパックをインストールすると [データベース サービス] のインストールで失敗する場合があります。

インストールの失敗ですが以下の技術情報が該当します。
An error message is logged in the Summary.txt file when a SQL Server 2005 service pack, cumulative update or cluster hotfix installation fails: "The Transaction Manager is not available"

SQL Server 2005 のサービスパックのインストールログですが以下のディレクトリに出力されます。
[C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix]

このディレクトリの中に、[Summary.txt] がありますので、このファイルの内容を確認してみます。

Product Installation Status Product                   : セットアップ サポート ファイル Product Version (Previous): 4035 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_SqlSupport.msi.log Error Number              : 0 Error Description         : ———————————————————————————- Product                   : データベース サービス (MSSQLSERVER) Product Version (Previous): 4311 Product Version (Final)   : Status                    : 失敗 Log File                  : Error Number              : 11009 Error Description         : 正常に修正プログラムが適用されたパッシブ ノードはありません ———————————————————————————- Product                   : SQL Server Native Client Product Version (Previous): 4035 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_sqlncli.msi.log Error Number              : 0 Error Description         : ———————————————————————————- Product                   : クライアント コンポーネント Product Version (Previous): 4311 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB2463332_sqlrun_tools.msp.log Error Number              : 0 Error Description         : ———————————————————————————- Product                   : SQLXML4 Product Version (Previous): 4035 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_sqlxml4.msi.log Error Number   &#16 0;          : 0 Error Description         : ———————————————————————————- Product                   : 旧バージョンとの互換性 Product Version (Previous): 2312 Product Version (Final)   : Status                    : 未選択 Log File                  : Error Description         : ———————————————————————————- Product                   : Microsoft SQL Server VSS Writer Product Version (Previous): 4035 Product Version (Final)   : Status                    : 処理中 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_SqlWriter.msi.log Error Number              : 0 Error Description         : ———————————————————————————-

データベース サービスで [正常に修正プログラムが適用されたパッシブ ノードはありません] のエラーが出力されています。
このログは [アクティブ ノード] (SQL Server のリソースを持っているノード) のログになりますので、パッシブ ノードでもログを確認してみたいと思います。
# ログはアクティブ ノードと同じディレクトリに出力されています。

Product Installation Status Product                   : セットアップ サポート ファイル Product Version (Previous): 4035 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_SqlSupport.msi.log Error Number              : 0 Error Description         : ———————————————————————————- Product                   : データベース サービス (MSSQLSERVER) Product Version (Previous): 4311 Product Version (Final)   : 5000 Status                    : 要再起動 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB2463332_sqlrun_sql.msp.log Error Number              : 3010 Error Description         : ———————————————————————————- Product                   : SQL Server Native Client Product Version (Previous): 4035 Product Version (Final)   : 5000 Status                    : 成功 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_sqlncli.msi.log Error Number              : 0 Error Description         : ———————————————————————————- Product                   : クライアント コンポーネント Product Version (Previous): 4311 Product Version (Final)   : Status                    : 失敗 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB2463332_sqlrun_tools.msp.log Error Number              : 29549 Error Description         : MSP Error: 29549  COM+ カタログにアセンブリ C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll をインストールして構成できませんでした。エラー: -2146233087 エラー メッセージ: Unknown error 0x80131501 エラーの説明: トランザクション マネージャは利用できません。 (HRESULT からの例外: 0x8004D01B) ———————————————————————————- Product                   : SQLXML4 Product Version (Previous): 4035 Product Version (Final)   : Status                    : 失敗 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_sqlxml4.msi.log Error Number              : 2 Error Description         : Windows インストーラ MSI ファイルをインストールできません ———————————————————————————- Product             &#160 ;     : 旧バージョンとの互換性 Product Version (Previous): 2312 Product Version (Final)   : Status                    : 未選択 Log File                  : Error Number              : 0 Error Description         : ———————————————————————————- Product                   : Microsoft SQL Server VSS Writer Product Version (Previous): 4035 Product Version (Final)   : Status                    : 失敗 Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB2463332_SqlWriter.msi.log Error Number              : 2 Error Description         : Windows インストーラ MSI ファイルをインストールできません ———————————————————————————-

 

今回の現象ですが、パッシブ ノードで [データベース サービス] のサービスパック適用に失敗しているのではなく、[クライアント コンポーネント] に対してのサービスパック適用で失敗しています。

この現象ですが、[クラスタの MSDTC のリソースをクライアント コンポーネントの適用が実行されているノードが所有していない] と発生します。

失敗した時のリソースの配置状況は以下のようにしていました。

DTC と SQL Server のリソースを [2008-CLS-01] というサーバーで所有させていました。
この状態でサービスパックを適用したため今回の現象が発生していました。

 

■Windows Server 2008 のクラスタ環境に SQL Server 2005 のサービスパックを適用

ここからが本題で適用するための手順になります。

まずはクラスタのリソースを以下のように配置します。

[2008-CLS-01] でサービスパックのインストーラーを起動するため、SQL Server のリソースは [2008-CLS-01] に配置しています。
DTC のリソースは [2008-CLS-02] (SQL Server から見てパッシブ となっているノード) に配置しています。

この状態でサービスパックのインストーラーを実行します。

1. [次へ] をクリックします。
   
2. [同意する] を選択して、[次へ] をクリックします。
   
3. 適応するコンポーネントを選択して、[次へ] をクリックします。
   
4. リモート ユーザーのアカウント情報を入力し、[次へ] をクリックします。
   パッシブ ノード側ではここで指定したアカウントを使用してサービスパックの適用が実施されます。
   
5. [次へ] をクリックします。
   
6. [次へ] をクリックします。
   
7. [インストール] をクリックして、インストールを開始します。
   

インストールをクリックするとパッシブ ノードのタスク スケジューラに [SqlNodeInstall] といタスクが作成され、パッシブ ノードでサービスパックのインストールが開始されます。

このタスクを使用して、インストーラー実行時にパッシブノードの [C:UpdateTemp] にコピーされたサービスパックのプログラムが実行されます。

パッシブ ノードのタスクマネージャーを確認すると [msiexec.exe] や [hotfix.exe] が起動されていることが確認できます。

 

インストールを開始してしばらくしているとパッシブ ノードで以下のダイアログが表示されます。
# パッシブ ノードにサービスパックのインストール時に指定したアカウントでログオンしておく必要があります。

Windows Server 2008 + SQL Server 2005 の場合、VS 2005 がプログラム互換性アシスタントの警告対象となります。
DTC をパッシブ ノードで実行している場合は、このダイアログが表示できるのですが、アクティブ ノードで DTC を実行している場合はこのダイアログが表示されません。

そのため、クライアント コンポーネントのアップデートができずにエラーとなってしまうようです。

クライアント コンポーネントはアクティブ / パッシブのノード両方でインストールする必要があります。
そのため、アクティブ ノードでクライアントコンポーネントのアップデートが実行される際には、DTC のリソースはアクティブ ノードで実行しておく必要があります。

上記のダイアログがパッシブ ノードで表示されたら、クラスターの管理コンソールを開き、リソースの配置を以下の用に変更しておきます。
# DTC のリソースを [2008-CLS-02] → [2008-CLS-01] に移動しました。

DTC の配置が終わったら、[プログラムを実行する] をクリックします。
# アクティブ ノードでクライアントコンポーネントの適用が実行される前までに DTC のリソースを移動しておけば問題ありません。

パッシブ ノードでインストールが終了すると、アクティブ ノードでインストールがされます。

アクティブ ノードでも [クライアント コンポーネント] のインストールが実行されるタイミングで、プログラム互換性アシスタントのダイアログが表示されますので、[プログラムを実行する] をクリックします。
# アクティブ ノードで DTC のリソースが実行されていないと今度はアクティブ ノードでインストールが失敗します。

この手順でインストールをするとサービスパックの適用が正常に完了します。

SQL Server 2005 のサービスパックをクラスタ環境に適用する場合はクラスタの DTC リソースの配置状況に注意する必要があります。

この現象ですが、[クライアント コンポーネント] の更新が入らない場合は発生しないので、CU の適用時にはクライアントコンポーネントが更新対象として含まれていなければ失敗はしないと思います。

今回は [データベースの整合性確認] について振り返っていきたいと思います。

[メンテナンス プラン ウィザード] では、 [データベースの整合性確認] のメンテナンス タスクを作成することが可能です。

このタスクを実行するとデータベースの整合性をチェックすることができます。
セミナーの中では、バックアップの取得前等に実行をすることでデータベースに問題が無いかを確認しバックアップを取得することが可能というようなお話がありました。

今回は、この整合性確認についてまとめていきたいと思います。

■データベースの整合性確認で実行される内容

まずはデータベースの整合性確認のタスクで実行される内容について確認していきたいと思います。
作成したデータベースの整合性確認のタスクで実行される T-SQL を確認してみます。

データベースの整合性確認のタスクでは、実行対象として指定したデータベースに対して [DBCC CHECKDB] が実行されます。
# [インデックスを含める] を外すと [NOINDEX] オプションを指定して実行されます。

以下は、BOL の DBCC CHECKDB の記載内容の引用になります。

次の操作を実行し、指定したデータベース内のすべてのオブジェクトの論理的および物理的な整合性をチェックします。

• データベースに対して DBCC CHECKALLOC を実行。
• データベース内にあるすべてのテーブルとビューに対して DBCC CHECKTABLE を実行。
• データベースに対して DBCC CHECKCATALOG を実行。
• データベース内にあるすべてのインデックス付きビューの内容を検証。
• FILESTREAM を使用して varbinary(max) データをファイル システムに格納する場合のテーブルのメタデータとファイル システムのディレクトリおよびファイルの間のリンクレベルの一貫性を検証。
• データベースの Service Broker データを検証。

DBCC CHECKDB では、上記の処理を実行してデータベースの整合性が取れているかを確認する処理になります。

■整合性確認を実施しないとどうなるか

データベースの整合性確認を行う事でどのようなことが分かるか、簡単に試してみたいと思います。

今回は、バイナリエディタでデータファイル (mdf) を開き、一部のデータを書き換え、データの内容とページのチェックサムが一致しないようにしてみました。
データを書き換える前のテーブルの全件を取得した情報がこちらになります。

SQL Server 2008 以降であれば、データがどのページに格納されているかは [sys.fn_PhysLocFormatter(%%physloc%%)] を使用することで確認することができます。
# この関数は、Undocument の関数です。
上のデータは、以下のクエリを実行して取得しています。

SELECT     sys.fn_PhysLocFormatter(%%physloc%%) AS [PageNo],     * FROM     Table_1

 

SQL Server のページにはチェックサムがあり、データの内容を元に計算したチェックサムとデータの内容が一致しているかを確認することで、ページが破損しているかを検出する機能がありますのでページに一貫性が無いとエラーとして検出することができます。
# データベースオプションで [CHECKSUM] / [TORN_PAGE_DETECTION] / [NONE] のいずれかを設定することが可能です。([TORN_PAGE_DETECTION] は下位互換となっていますが。)

今回はバイナリエディタでデータベースのファイルを開いて、 (1:114:0) のページを壊してみました。

ページを壊した後に、データを全件取得すると以下のデータまでは取得できるのですが、 (1:114:0) のデータを読み込もうとした際にエラーが発生します。

メッセージ 824、レベル 24、状態 2、行 1 SQL Server で、一貫性に基づいた論理 I/O エラーが検出されました: 正しくないチェックサム (必要なチェックサム: 0x8b1229d0、実際のチェックサム: 0x831a29d0)。 このエラーは、ファイル ‘F:SQL2008R2TEST.mdf’ のオフセット 0x000000000e4000 にあるデータベース ID が 5 のページ (1:114) の 読み取り 中に発生しました。 SQL Server エラー ログまたはシステム イベント ログ内の別のメッセージで詳細情報が報告されることもあります。 このエラー状態は深刻で、データベースの整合性を損なう可能性があるので、すぐに解決する必要があります。 完全なデータベース一貫性確認 (DBCC CHECKDB) を実行してください。 このエラーには多くの要因があります。 詳細については、SQL Server オンライン ブックを参照してください。

(1:114) という情報が表示されていますので、ファイル ID 1 (mdf ファイル) の 114 ページに一貫性のエラー (チェックサムとデータが不整合) が発生していることが確認できます。

今回、私が壊したページ番号と一致しますね。

破損ページの操作をした場合、イベント ビューアーのアプリケーションにも同様の情報が出力されますので、監視ソフト等でログ監視をしているのであれば、エラーを検知することもできます。

SQL Server のログにも情報は出力されています。

今回はデータを全件読み込みしたので破損ページも読み込まれエラーが発生しましたが、破損したページにアクセスをしなければ正常にデータを読み込むことができます。
# Col = 4 のデータを読もうとするとエラーになってしまうので飛ばしています。(先行読み込みの可能性が)

破損したページを操作しないとエラーにはなりませんので、データベースに異常があるかは分かりません。

DBCC CHECKDB を定期的に実行することで障害により発生した潜在的に潜んでいるデータベースの異常をチェックすることが可能です。
[DBCC CHECKDB(N’TEST’)] を実行して、今回整合性のエラーが発生している [TEST] データベースでデータベースのチェックをしてみます。

‘TEST’ の DBCC 結果。 Service Broker メッセージ 9675、状態 1: 分析されるメッセージ型: 14。 Service Broker メッセージ 9676、状態 1: 分析されるサービス コントラクト: 6。 Service Broker メッセージ 9667、状態 1: 分析されるサービス: 3。 Service Broker メッセージ 9668、状態 1: 分析されるサービス キュー: 3。 Service Broker メッセージ 9669、状態 1: 分析されたメッセージ交換のエンドポイント: 0。 Service Broker メッセージ 9674、状態 1: 分析されたメッセージ交換グループ: 0。 Service Broker メッセージ 9670、状態 1: 分析されるリモート サービス バインド: 0。 Service Broker メッセージ 9605、状態 1: 分析されたメッセージ交換の優先度: 0。 ‘sys.sysrscols’ の DBCC 結果。 オブジェクト "sys.sysrscols" の 7 ページには 637 行あります。 ‘sys.sysrowsets’ の DBCC 結果。 オブジェクト "sys.sysrowsets" の 1 ページには 93 行あります。 ‘sys.sysallocunits’ の DBCC 結果。 オブジェクト "sys.sysallocunits" の 2 ページには 107 行あります。 ‘sys.sysfiles1’ の DBCC 結果。 オブジェクト "sys.sysfiles1" の 1 ページには 2 行あります。 ‘sys.syspriorities’ の DBCC 結果。 オブジェクト "sys.syspriorities" の 0 ページには 0 行あります。 ‘sys.sysfgfrag’ の DBCC 結果。 オブジェクト "sys.sysfgfrag" の 1 ページには 2 行あります。 ‘sys.sysphfg’ の DBCC 結果。 オブジェクト "sys.sysphfg" の 1 ページには 1 行あります。 ‘sys.sysprufiles’ の DBCC 結果。 オブジェクト "sys.sysprufiles" の 1 ページには 2 行あります。 ‘sys.sysftinds’ の DBCC 結果。 オブジェクト "sys.sysftinds" の 0 ページには 0 行あります。 ‘sys.sysowners’ の DBCC 結果。 オブジェクト "sys.sysowners" の 1 ページには 14 行あります。 ‘sys.sysprivs’ の DBCC 結果。 オブジェクト "sys.sysprivs" の 1 ページには 130 行あります。 ‘sys.sysschobjs’ の DBCC 結果。 オブジェクト "sys.sysschobjs" の 1 ページには 56 行あります。 ‘sys.syscolpars’ の DBCC 結果。 オブジェクト "sys.syscolpars" の 8 ページには 488 行あります。 ‘sys.sysnsobjs’ の DBCC 結果。 オブジェクト "sys.sysnsobjs" の 1 ページには 1 行あります。 ‘sys.syscerts’ の DBCC 結果。 オブジェクト "sys.syscerts" の 0 ページには 0 行あります。 ‘sys.sysxprops’ の DBCC 結果。 オブジェクト "sys.sysxprops" の 0 ページには 0 行あります。 ‘sys.sysscalartypes’ の DBCC 結果。 オブジェクト "sys.sysscalartypes" の 1 ページには 34 行あります。 ‘sys.systypedsubobjs’ の DBCC 結果。 オブジェクト "sys.systypedsubobjs" の 0 ページには 0 行あります。 ‘sys.sysidxstats’ の DBCC 結果。 オブジェクト "sys.sysidxstats" の 2 ページには 158 行あります。 ‘sys.sysiscols’ の DBCC 結果。 オブジェクト "sys.sysiscols" の 2 ページには 309 行あります。 ‘sys.sysbinobjs’ の DBCC 結果。 オブジェクト "sys.sysbinobjs" の 1 ページには 23 行あります。 ‘sys.sysaudacts’ の DBCC 結果。 オブジェクト "sys.sysaudacts" の 0 ページには 0 行あります。 ‘sys.sysobjvalues’ の DBCC 結果。 オブジェクト "sys.sysobjvalues" の 22 ページには 159 行あります。 ‘sys.sysclsobjs’ の DBCC 結果。 オブジェクト "sys.sysclsobjs" の 1 ページには 16 行あります。 ‘sys.sysrowsetrefs’ の DBCC 結果。 オブジェクト "sys.sysrowsetrefs" の 0 ページには 0 行あります。 ‘sys.sysremsvcbinds’ の DBCC 結果。 オブジェクト "sys.sysremsvcbinds" の 0 ページには 0 行あります。 ‘sys.sysxmitqueue’ の DBCC 結果。 オブジェクト "sys.sysxmitqueue" の 0 ページには 0 行あります。 ‘sys.sysrts’ の DBCC 結果。 オブジェクト "sys.sysrts" の 1 ページには 1 行あります。 ‘sys.sysconvgroup’ の DBCC 結果。 オブジェクト "sys.sysconvgroup" の 0 ページには 0 行あります。 ‘sys.sysdesend’ の DBCC 結果。 オブジェクト "sys.sysdesend" の 0 ページには 0 行あります。 ‘sys.sysdercv’ の DBCC 結果。 オブジェクト "sys.sysdercv" の 0 ページには 0 行あります。 ‘sys.syssingleobjrefs’ の DBCC 結果。 オブジェクト "sys.syssingleobjrefs" の 1 ページには 146 行あります。 ‘sys.sysmultiobjrefs’ の DBCC 結果。 オブジェクト "sys.sysmultiobjrefs" の 1 ページには 106 行あります。 ‘sys.sysguidrefs’ の DBCC 結果。 オブジェクト "sys.sysguidrefs" の 0 ページには 0 行あります。 ‘sys.syscompfragments’ の DBCC 結果。 オブジェクト "sys.syscompfragments" の 0 ページには 0 行あります。 ‘sys.sysftstops’ の DBCC 結果。 オブジェクト "sys.sysftstops" の 0 ページには 0 行あります。 ‘sys.sysqnames’ の DBCC 結果。 オブジェクト "sys.sysqnames" の 1 ページには 97 行あります。 ‘sys.sysxmlcomponent’ の DBCC 結果。 オブジェクト "sys.sysxmlcomponent" の 1 ページには 99 行あります。 ‘sys.sysxmlfacet’ の DBCC 結果。 オブジェクト "sys.sysxmlfacet" の 1 ページには 112 行あります。 ‘sys.sysxmlplacement’ の DBCC 結果。 オブジェクト "sys.sysxmlplacement" の 1 ページには 18 行あります。 ‘sys.sysobjkeycrypts’ の DBCC 結果。 オブジェクト "sys.sysobjkeycrypts" の 0 ページには 0 行あります。 ‘sys.sysasymkeys’ の DBCC 結果。 オブジェクト "sys.sysasymkeys" の 0 ページには 0 行あります。 ‘sys.syssqlguides’ の DBCC 結果。 オブジェクト "sys.syssqlguides" の 0 ページには 0 行あります。 ‘sys.sysbinsubobjs’ の DBCC 結果。 オブジェクト "sys.sysbinsubobjs" の 1 ページには 3 行あります。 ‘sys.syssoftobjrefs’ の DBCC 結果。 オブジェクト "sys.syssoftobjrefs" の 0 ページには 0 行あります。 ‘sys.queue_messages_1977058079’ の DBCC 結果。 オブジェクト "sys.queue_messages_1977058079" の 0 ページには 0 行あります。 ‘sys.queue_messages_2009058193’ の DBCC 結果。 オブジェクト "sys.queue_messages_2009058193" の 0 ページには 0 行あります。 ‘sys.queue_messages_2041058307’ の DBCC 結果。 オブジェクト "sys.queue_messages_2041058307" の 0 ページには 0 行あります。 ‘sys.filestream_tombstone_2073058421’ の DBCC 結果。 オブジェクト "sys.filestream_tombstone_2073058421" の 0 ページには 0 行あります。 ‘sys.syscommittab’ の DBCC 結果。 オブジェクト "sys.syscommittab" の 0 ページには 0 行あります。 ‘LockEscalationEvent’ の DBCC 結果。 オブジェクト "LockEscalationEvent" の 1 ページには 1 行あります。 ‘Table_1’ の DBCC 結果。 メッセージ 8928、レベル 16、状態 1、行 1 オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data): ページ (1:114) を処理できませんでした。詳細については、他のエラーを参照してください。 メッセージ 8939、レベル 16、状態 98、行 1 テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)、ページ (1:114)。テスト (IS_OFF (BUF_IOERR, pBUF->bstat)) が失敗しました。値は 12716041 と -4 です。 メッセージ 8976、レベル 16、状態 1、行 1 テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:114) がスキャンでは見つかりませんでしたが、このページは親ページ (1:93) と前ページ (1:110) から参照されています。以前に発生したエラーをすべて確認してください。 メッセージ 8978、レベル 16、状態 1、行 1 テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:115) に前ページ (1:114) からの参照がありません。チェーン リンケージに問題がある可能性があります。 オブジェクト "Table_1" の 9 ページには 9 行あります。 CHECKDB により、テーブル ‘Table_1’ (オブジェクト ID 2137058649) に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。 CHECKDB により、データベース ‘TEST’ に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。 repair_allow_data_loss は DBCC CHECKDB (TEST) で見つかったエラーの最小修復レベルです。 DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。

DBCC CHECKDB を実行することで、データベースの状態をチェックしてくれますので、破損している領域を操作しなくても異常があるかどうかの確認をしてくれます。

イベントビューアーと SQL Server のログにも結果の概要が出力されますので、後で結果を再確認することも可能です。

SQL Server のダンプのテキストも出力されています。

[msdb..suspect_pages] を SELECT することで直近の情報を取得することもできます。

 

DBCC CHECKDB をバックアップ実行前に実施する理由ですが、[障害が発生しているデータベースのバックアップは障害が発生した状態] となるからです。

障害が発生していない状態のデータベースのバックアップがないと破損している領域を修復することができません。
# SQL Server 2008 以降のデータベースミラーリングであれば、破損ページを読み込んだ際にミラー側から修復することも可能ですが。

そのため、バックアップを取得する前にデータベース自体に異常が無いかを確認して、その後にバックアップを取ることが推奨されます。

セミナーの中で質問 / 回答があったのですが、DBCC CHECKDB によるデータベースの整合性確認はデータベースのサイズが大きいとかなり負荷がかかります。
そのため、バックアップのたびに毎回実行するのが難しいかもしれませんね。

次の投稿では [データベースの整合性確認の負荷] についてまとめてみたいと思います。

今回は [バックアップポリシー] について振り返っていきたいと思います。
# 参加から 2 週間経過したのでテンポよくまとめていかないとだいぶ記憶が薄れてきました…。

セミナーでは以下のような図を元にバックアップポリシーについてのお話しがありました。

 

■バックアップの基本的な設定

上の図は復旧モデルを [完全] または [一括ログ] にした場合のバックアップの基本的な設定になります。
# [単純] 復旧モデルの場合は、ログのバックアップが取れませんので、データベースのバックアップのみになります。

セミナーでは日に一度のデータベースバックアップと、一時間おきのログのバックアップで設定というような例でお話をされていたと思います。

SQL Server のバックアップは大きく分けて 3 種類あります。
# ファイル / ファイルグループでのバックアップも取得ができるのですが、今回は割愛しておきます。

• 完全
• 差分
• トランザクションログ

完全 / トランザクションログは上の図で [データベース] [ログ] と書かれているバックアップになります。

[完全バックアップ] は、その時点 (バックアップを実行したタイミング) でのデータまで復元することが可能なバックアップとなります。
完全バックアップはそのバックアップ単体で戻すことが可能ですのでバックアップにはログも含まれています。

 

[差分バックアップ] は、前回の完全バックアップからの差分データのみを含んだバックアップになります。
完全バックアップを元にした差分になりますので、単体で戻すことはできず必ず前回の完全バックアップが必要となります。

また、差分バックアップは前回の完全バックアップからの差分データですので、完全バックアップを取得してからの期間が長くなり、全データが更新された状態になってしまうと、完全バックアップ相当のバックアップファイルのサイズとなります。

 

[トランザクションログバックアップ] は、ログレコードのバックアップになります。
復旧モデルのときにもお話しがあったのですが、復旧モデルを [完全] [一括ログ] にしている場合は必須になるバックアップとなります。
これらの復旧モデルに関しては、ログレコードは自動的に切り捨てはされず、バックアップを取得したタイミングで切り捨てが行われ、切り捨てられた個所が再利用可能となります。
そのため、ログのバックアップを取得しないとログがずっと蓄積された状態となり、ログ領域のディスクが枯渇することになります。
ログのバックアップは障害発生時に重要となるバックアップのため、できるだけ短い間隔で取得することが重要であるとセミナーではお話がありました。
# 1 時間おきのログのバックアップというお話はこのタイミングであった気がします。
データベースの完全バックアップを取得していない状態ではログバックアップは取得することはできません。

 

バックアップについてですが、以下の資料がわかりやすいと思います。
SQL Server 2000 の資料ですが、それ以降のバージョンでも考え方は同じですので。
第 4 章 バックアップと復元

 

■定期的なバックアップの設定

定期的なバックアップの設定方法として、[メンテナンスプランウィザード] によるバックアップの設定が紹介されていました。

ワンタイムのバックアップであれば、データベースを右クリックして、[タスク] → [バックアップ] からバックアップを取得することが可能です。

このバックアップに関しては、ワンタイムになりますので定期的なバックアップの取得の設定はできません。

定期的なバックアップをウィザード形式で設定する場合は、セミナーで紹介のあった [メンテナンスプランウィザード] を使用する事で設定ができます。

メンテナンスプランウィザードは [管理] → [メンテナンス プラン] → [メンテナンス プラン ウィザード] から起動することができます。

メンテナンス プラン ウィザードを使用することで、データベースのメンテナンスをする仕組みを数ステップで設定が可能となります。

今回のセミナーでお話しのあったメンテナンスプランに似たものを実際に作ってみたいと思います。

1. [次へ] をクリックします。
   
2. [タスクごとに個別のスケジュールを使用する] を選択して、[次へ] をクリックします。
   
3. [データべすのバックアップ (完全)] [データベースのバックアップ (トランザクション ログ)] を選択して、[次へ] をクリックします。
   
4. [次へ] をクリックします。
   
5. [完全] バックアップの取得対象とバックアップのスケジュールを設定し、[次へ] をクリックします。
   今回は [すべてのデータベース] を毎日 [22:00] に取得するように設定をしています。
   
6. トランザクションログのバックアップを取得するデータベースとスケジュールを選択し、[次へ] をクリックします。
   今回は、[すべてのユーザー データベース] を [毎日1 時間置き] にバックアップを取得するように設定しています。
   すべてのユーザー データベースですが復旧モデルが単純以外のデータベースが対象となります。
   
7. ログの出力場所を設定し、[次へ ]をクリックします。
   
8. [完了] をクリックします。
   
9. [閉じる] をクリックします。
   

以上でメンテナンスプランの作成は完了です。

今回は、[完全 バックアップ] の取得と、[トランザクションログ] のバックアップの取得を設定していますので二つのジョブが SQL Server エージェントに作成されています。

作成した 2 つのジョブを手動で実行して、バックアップの取得状況を確認してみたいと思います。
ジョブを手動実行する場合は、対象のジョブを右クリックして、[ステップでジョブを開始] をクリックします。

今回は、H ドライブにアックアップを取得しているのですが、データベースのバックアップ (.bak) とトランザクションログのバックアップ (.trn) が取得されていることが確認できます。

今回のメンテナンスプランですが、バックアップを取得するたびにタイムスタンプが設定されたバックアップファイルが蓄積されていきます。

また、ログに関してもバックアップを実行するたびに蓄積されていきます。

このバックアップとログの履歴 (世代) 管理ですが、[メンテナンス クリーンアップ タスク] を使用することで管理することができます。
先ほど作成したメンテナンスプランにこの履歴クリーンアップタスクを組み込んでみたいと思います。

作成したメンテナンスプランは、[管理] → [メンテナンス プラン] の下に作成がされますので、作成したプランをダブルクリックします。

そうするとメンテナンスプランのデザインウィンドウが開きます。

先ほど作成したプランが、[Subplan_1] [Subplan_2] として設定されています。
今回は [サブプランの追加] をクリックして、[Subplan_3] として[メンテナンス クリーンアップ タスク] を設定します。

タスクをダブルクリックすると設定画面になります。
このタスクを使用することで、バックアップファイルとメンテナンスプランのレポートを削除することが可能です。
拡張子単位で指定はできるのですが、ワイルドカードは使えないので、[bak] [tran] 用のバックアップファイル削除タスクを用意します。

テキストレポートのクリーンアップタスクとしては以下の内容を設定しました。

今回はスケジュールも設定し、このような形でサブプランを設定してみました。

今回は 4 週経過したファイルが対象になっていますので、以下の PowerShell を実行して、ファイルの更新日付を変更してみました。
# クリーンアップタスクでファイル操作をする際の日付はファイルの更新日付が検索対象となります。

Get-Item "C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLLogMaintenancePlan_Subplan_1_20101220221815.txt" | Set-ItemProperty -name LastWriteTime -Value (Get-Date).AddDays(-28)

これで、メンテナンスレポートの日付が 28 日前 (4 週間前) になりました。

それでは、作成したクリーンアップタスクんのジョブを実行してみます。
ジョブを実行したことで、先ほどファイルの更新日付を変更したメンテナンスレポートが削除されていることが確認できます。

バックアップ ファイルに関しても更新日付が条件に一致するのであれば削除されます。

メンテナンス プラン ウィザードを使用することでバックアップのメンテナンスプランを GUI のウィザードベースで柔軟に設定することが可能となります。

今回の投稿では設定をしなかったのですが、メンテナンスプランには [データベースの整合性確認] というメンテナンス タスクがあります。

セミナーの中ではこのタスクについてもお話がありました。

次の投稿では、データベースの整合性確認についてまとめてみたいと思います。

SQL Server のインストールですが、インストール時に [インスタンス ルート ディレクトリ] を設定します。

インスタンス ルート ディレクトリは任意のディレクトリを指定できますので、デフォルトの [C:Program FilesMicrosoft SQL Server] から変更することが可能です。

このインスタンス ルート ディレクトリですが、変更した際に一点注意点があります。
今回は、C ドライブではなく E ドライブに設定をしてみました。

インスタンス ルート ディレクトリ以外は C ドライブに設定しています。

SQL Server をインストールすると以下のサービスがインストールされます。

• SQL Active Directory Helper Service
• SQL Server
• SQL Server Agent
• SQL Server Browser
• SQL Server VSS Writer

インスタンス ルート ディレクトリに選択したディレクトリには、[SQL Server] [SQL Server Agent] のサービスのファイルが格納されます。

サービス用のプログラムが C ドライブ以外にあることは問題ないのですが、この状態では Windows Server バックアップで [システム状態] [ベアメタル 回復] のバックアップを取得する際に、SQL Server 用のサービスに必要なプログラムが格納されているドライブも取得対象となります。
# このあたりは、System Writer の VSS Writer が制御しているみたいですね。レジストリ (ImagePath の値) を直接修正して、サービスで使用するプログラムの場所を変更すればインスタンス ルート ディレクトリを取らなくすることもできますが。危険すぎてこの方法は使わないですよね…。

最初は [ベア メタル回復] のバックアップ取得を試してみます。

ベア メタル回復を選択すると、自動的にベアメタル回復に必要なドライブが選択されるのですが、C ドライブだけでなく E ドライブも自動的に選択がされます。
E ドライブを外そうとしても以下のメッセージが表示され、ドライブ / ドライブ以下のフォルダを外すことができません。

[システム状態] を選択すると、システム状態の回復に必要なものが取得されます。
# ドライブの選択状況は変わらず、内部でシステム状態の回復に必要なものが自動で取得されます。

システム状態を選択した際に取得されるバックアップを確認してみます。
3 つの VHD ファイルが作成されています。

これですが、[システムで予約済み] (通常にインストールをした際の先頭 100MB) [C ドライブ] [E ドライブ] のバックアップとなっています。

Windows Server 2008 R2 の場合はファイル単位でバックアップが取得できるようになているので、各ドライブのバックアップはシステム状態として必要最低限のファイルがバックアップされています。
# C ドライブであればユーザープロファイルのディレクトリは除外されています。

E ドライブのバックアップを確認してみるとサービスで使用している 2 つのファイルのみが取得されています。

Windows Server 2008 R2 であれば、ファイル単位で取れるのでシステム状態を取得した時にインスタンス ルート ディレクトリを C ドライブ以外にしても最小限のファイルがバックアップされることになります。

 

Windows Server 2008 ではどうなるでしょう。

Win
dows Server 2008 では[システム回復を有効にする] がベアメタル回復相当のバックアップとなります。
このオプションを有効にすると、インスタンス ルート ディレクトリに指定したドライブも自動的に選択されます。

この動きは、Windows Server 2008 R2 と同じですね。

それでは、システム状態のバックアップを取得するとどうなるでしょう。
Windows Server 2008 の場合は、[wbadmin start systemstate

backup] というコマンドラインを使用して、システム状態だけを取得することが可能です。
# [Start Backup] で取得する場合は、[-allCritical] というオプションを指定します。

システム状態を取得する場合に必要となるファイルが含まれているドライブを自動で判断する動きは Windows Server 2008 R2 と変わりません。

Windows Server 2008 の場合は、デフォルトでは先頭の 100MB の領域は作成されないので、2 つの VHD ファイルが取得されます。

E ドライブ用に取得された VHD の中身を見てみます。

Windows Server 2008 R2 と異なり、サービス以外のファイルも取得されています。

Windows Server 2008 の場合は、ボリューム単位でのバックアップしか取得ができないため、SQL Server のサービスで使用しているファイル以外のものも取得がされます。

そのため、インスタンス ルート ディレクトリのドライブに SQL Server のデータベースを格納していると、Windows Server 2008 ではシステム状態のバックアップでデータベースのファイルも取得がされてしまい、バックアップのファイルサイズが肥大化する可能性があります。

インスタンス ルート ディレクトリを変更することはあまりないかもしれませんが、変更する場合はバックアップの取得で影響がないようにデータベースのファイルを配置するような考慮が必要そうですね。

先日、SQL Server の WMI イベント警告について投稿をしました。

WMI イベント警告は SQL Server Agent 経由で実行されるため、SQL Server の権限も SQL Server Agent のサービスアカウントのものになります。

BOL に記載されているのですが、WMI イベント警告で警告を受信するためには、以下の権限が必要となります。

WMI 警告の通知を受信するには、SQL Server エージェントのサービス アカウントに、WMI イベントを含む名前空間、および ALTER ANY EVENT NOTIFICATION に対する権限が許可されている必要があります。

 

SQL Server Agent をローカルシステムアカウント (LocalSystem : NT AUTHORITYSYSTEM) で実行した場合は、特に設定を変更しなくても WMI イベント警告を受信できると思います。

SQL Server Agent を一般ユーザー (Users グループのユーザー) で起動した場合はどうなるでしょう。

イベントの条件に達してもイベントビューアーのアプリケーションに [124] のエラーが出力されて WMI イベント警告が実行されません。

この挙動の原因ですが、ローカルシステムアカウントの場合は、[ALTER ANY EVENT NOTIFICATION] のサーバーレベルの権限が付与されています。
サーバーレベルの権限は以下のクエリで確認ができます。

EXECUTE? AS LOGIN = ‘NT AUTHORITYSYSTEM’ SELECT SUSER_NAME(), USER_NAME() SELECT * from fn_my_permissions(NULL, ‘SERVER’) ORDER BY permission_name REVERT

SQL Server Agent が SQL Server に接続する場合は、[NT SERVICESQLAgent$<インスタンス名>] のログインで実行がされます。
# OS によってはサービスアカウントがそのまま使用されていることもあります。

このログインは、Windows 認証になっているので、Windows アカウントを使用したログインとなっています。
Windwos? Server 2008 R2 の場合はこのアカウントはサービス SID になっていて、Windows のログインやグループではなく、サービスに直接関連づいたアカウントになっています。

サービス SID の場合は、上記のクエリだと権限が見えないのですよね…。
サービス SID を使用していない OS (XP / 2003)? の場合は、SQL Server Agent の起動アカウントに、[ALTER ANY EVENT NOTIFICATION] の権限は付与されていませんでした。
ローカルシステム以外で SQL Server Agent を起動しているときは、以下のクエリを実行して、権限を付与する必要があります。

USE [master] GO GRANT ALTER ANY EVENT NOTIFICATION TO [NT SERVICESQLAgent$SQL2008R2]

権限を外す場合はこちらになります。
# REVOKE で権限を外します。

USE [master] GO REVOKE ALTER ANY EVENT NOTIFICATION TO [NT SERVICESQLAgent$SQL2008R2]

ALTER ANY EVENT NOTIFICATION の権限を付与することで、WMI イベントを SQL Server Agent のサービス起動ユーザーで受信できるようになり、WMI イベント警告を実行できるようになります。

WMI イベントを含む名前空間に関しては、インストール直後の状態の権限で問題は無いようです。

WMI の権限の確認についてですが、ファイル名を指定して実行から MMC を起動して、[WMI コントロール] を追加します。

MMC に WMI のスナップインを追加した後に、WMI コントロールのプロパティを開きます。

WMI コントロールのプロパティが開いたら、[セキュリティ] タブの [RootMicrosoftSqlServerServerEvents<インスタンス名>] を選択して、[セキュリティ] をクリックします。

そうすると、WMI 名前空間に対してのセキュリティを確認することができます。

SQL Server Agent のサービス SID には、[メソッドの実行] [アカウントの有効化] [セキュリティの読み取り] の権限が付与されています。

WMI イベント警告を実行するために必要な権限はこの権限でよいようで、デフォルトから設定は変更しなくても実行することができました。

サーバーレベルで権限を付与する必要があり、この操作は普段あまりやらない作業だと思いますので、ちょっと戸惑うかもしれないですね。

今回は [パフォーマンス条件警告] について振り返ってみたいと思います。

セミナーの中で SQL Server のパフォーマンス条件警告をしようして、ログの使用状況が一定の閾地に達した場合にログのバックアップを取得して、ログを切り捨てるという方法が紹介されました。

それでは、パフォーマンス条件警告についてまとめていきたいと思います。

■パフォーマンス条件警告設定の流れ

パフォーマンス条件警告ですが、[SQL Server Agent] を使用して動作します。

設定は

1. 警告の条件を設定
2. 警告が発生した際に実行する SQL Server Agent のジョブを設定
3. 警告が発生したことの通知方法を設定

という流れになります。
# 実際の設定は順不同でジョブと通知方法は必須ではないですが。

 

■パフォーマンス条件警告を設定

それでは、実際にパフォーマンス条件警告を設定していきたいと思います。
今回はセミナーでお話しのあったものと同じ設定を行っていきます。

1. [警告] を右クリックして、[新しい警告] をクリックします。
   
2. 警告の条件を設定します。
   今回は以下の内容で設定を行っています。
   
   TEST データベースのログが 200,000KB 使用された場合に警告を発生するように設定しています。
3. [応答] の [ジョブの実行] を有効にして、[新しいジョブ] をクリックします。
   
   今回は警告を作成する作業の流れで、新しいジョブを作成していますが、事前に作成したジョブを使う事も可能です。
4. ジョブの [名前] を入力します。
   
5. [ステップ] の [新規作成] をクリックします。
   
6. ログのバックアップを取得するためのステップを作成し、[OK] をクリックします。
   # 今回のパフォーマンス条件警告は後でスクリプト化したものを記載しますのでコマンドの内容はここでは割愛します。
   
7. [OK] をクリックします。
   
8. [OK] をクリックします。
   

以上で設定は完了です。

今回は通知設定はしていませんが、通知は以下の方法で行う事ができます。

 

それでは実際にデータを追加して挙動がどのように変わるかを試してみたいと思います。
今回も自動チェックポイントは無効にしています。

パフォーマンス条件警告を無効にしている場合の状態はこのようになります。
# 作成した警告は、個別に無効化することができます。

ログの書き込み待ちとバックアップ処理時間は 2 軸にしていますが、これらは発生していないことが確認できます。

それでは、パフォーマンス条件警告を有効にして同じ処理を実行してみます。

こちらも、バックアップ処理時間は 2 軸に表示しています。
ログの書き込み待ちに関しては、カウンタがあがっていることを示すために、元の値に 10,000 をかけています。

パフォーマンス条件警告の設定内容が有効になっていますので、ログファイルの使用状況が一定に達したタイミングで自動的にログのバックアップが行われています。(バックアップ処理時間はデータ/ログのバックアップを取得すると発生します。)

今回作成したパフォーマンス条件警告のサンプルスクリプトがこちらになります。

USE [msdb] GO /****** Object:  Job [TEST Database Log Backup]    Script Date: 12/16/2010 21:09:09 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/16/2010 21:09:09 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’TEST Database Log Backup’,         @enabled=1,         @notify_level_eventlog=0,         @notify_level_email=0,         @notify_level_netsend=0,         @notify_level_page=0,         @delete_level=0,         @description=N’使用できる説明はありません。’,         @category_name=N'[Uncategorized (Local)]’,         @owner_login_name=N’WIN-1QM471JPL04Administrator’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [TEST Database Log Backup]    Script Date: 12/16/2010 21:09:09 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’TEST Database Log Backup’,         @step_id=1,         @cmdexec_success_code=0,         @on_success_action=1,         @on_success_step_id=0,         @on_fail_action=2,         @on_fail_step_id=0,         @retry_attempts=0,         @retry_interval=0,         @os_run_priority=0, @subsystem=N’TSQL’,         @command=N’BACKUP LOG [TEST] TO  DISK = N”F:BackupSQL2008R2TEST.bak” WITH NOFORMAT, INIT,  NAME = N”TEST-トランザクション ログ  バックアップ”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO ‘,         @database_name=N’TEST’,         @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO   EXEC msdb.dbo.sp_add_alert @name=N’TEST Database Log Auto Backup’,         @message_id=0,         @severity=0,         @enabled=0,         @delay_between_responses=0,         @include_event_description_in=0,         @category_name=N'[Uncategorized]’,         @performance_condition=N’MSSQL$SQL2008R2:Databases|Log File(s) Used Size (KB)|TEST|>|200000′,         @job_id=N’e5518186-0c48-4c08-a14c-3c5c285730ef’ GO

このスクリプトですが、[INIT] を指定してログのバックアップを取得しています。
本来の運用では、この指定は NG です。
# テスト用でログのバックアップを蓄積したくなかったので INIT でバックアップを上書きしています。
ログのバックアップはログチェーンという考えがあり、連続したログのバックアップが担保されている必要があります。
ログチェーンが途切れてしまうとログを使用したリストアができませんので、本番運用で使う場合は [NOINIT] かログファイルの名称を動的に生成するようにしてログを上書きしないようにする必要があります。

■Twitter での質問について

セミナー中に Twitter で以下のような質問がありました。
「オンライン中のトランザクションログバックアップのパフォーマンス劣化はどれくらいなのでしょうか？」

ログのバックアップをするとログの書き込み待ちが発生しているのが上のグラフで確認することができます。
# 今回の環境では 200 MB 程度のログのバックアップを書き込む際には、書き込み待ちは最大で 7ms 程度になっていました。

グラフではなくWRITELOG の待ち事象でも比較をし手みたいと思います。

パフォーマンス条件警告を設定していない場合はこのような待ち事象になります。

waiting_tasks_count = 619,844 ? 519,766 = 100,078
wait_time_ms = 459,265 ? 396,561 = 62,704

パフォーマンス条件警告を設定した場合はこのようになります。

waiting_tasks_count = 720,408 ? 619,881 = 100,527
wait_time_ms = 532,001 ? 459,405 = 72,596

ログのバックアップをしている最中はログの使用状況がなだらかになりますのでログの書き込みにも多少の影響が発生します。

また、BOL に以下のように書かれているようにバックアップの実行を行うと CHECKPOINT が発生します。

チェックポイントは次の状況で作成されます。

• CHECKPOINT ステートメントが明示的に実行された場合。接続を確立するために、現在のデータベースでチェックポイントが作成されます。
• データベースで最小ログ記録操作が実行された場合。たとえば、一括ログ復旧モデルを使用しているデータベースで一括コピー操作が実行された場合です。
• ALTER DATABASE を使用して、データベース ファイルが追加または削除された場合。
• SHUTDOWN ステートメント、または SQL Server (MSSQLSERVER) サービスを停止することによって、SQL Server のインスタンスが停止された場合。どちらの場合でも、SQL Server のインスタンスの各データベースでチェックポイントが作成されます。
• データベースの復旧にかかる時間を短縮するために、SQL Server のインスタンスにより、各データベースで定期的に自動チェックポイントが作成されている場合。
• データベースのバックアップが作成された場合。
• データベースのシャットダウンが必要な動作が実行された場合。たとえば、AUTO_CLOSE が ON に設定されていて、データベースへの最後のユーザー接続が終了した場合、またはデータベースの再起動が必要なデータベース オプションが変更された場合です。

データベースのバックアップにはログのバックアップも含まれています。
先ほどのグラフにチェックポイントの発生状況も追加してみます。

バックアップのタイミングに合わせてチェックポイントが発生していることが確認できます。
チェックポイントが発生するとメモリ上のダーティーページをデータファイルに書きだしますので、データファイルに対しても負荷がかかることになります。

この辺はディスクの性能にも依存するのでどれくらいというのは難しいところなのですが、ログの書き込み待ち + チェックポイントによるデータファイルへの書き出しを劣化分として見込んでおく必要があると思います。
# この辺はディスク性能とドライブ構成に依存するところがあるのですよね…。

 

■WMI イベント警告

これは警告のちょっとした応用なのですが、パフォーマンス条件警告ではなく、[WMI イベント警告] を使用することでイベントを受信してジョブを実行することが可能となります。

例えば、ロックエスカレーションが発生した場合にそのイベントを受信してテーブルにロックエスカレーションが発生した時に実行されたクエリを保存してくれるような警告が作れると運用で便利そうですよね。

WMI イベント警告を使用すると、イベントを受信して特定のジョブを実行するということができるようになります。
# イベントを取得してログに出すだけであれば SQL トレースでもできるのですけどね。

BOL には [サンプル : WMI Provider for Server Events の使用による SQL Server エージェント警告の作成] という形でデッドロックをトラップしてテーブルにデータを書きだすサンプルが紹介されています。
# サンプル : WMI Provider for Server Events の使用による SQL Server エージェント警告の作成

ただし、内容に一か所誤りがあり、

@command= N’INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData))))’

を

@command= N’INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)’

にしないと動かなかったはずですが。
# シングルクォート × 2 が足りていないのですよね…。

WMI イベント警告を使用するための事前準備として 2 つの作業があります。

1. 対象のデータベースで [Service Broker] を有効にする。
   WMI イベントを受信するために対象のデータベースで [Service Broker] を有効にします。
   BOL に以下のように記載されています。
   
   

   SQL Server エージェントが WMI イベントを受信するには、msdb および AdventureWorks2008R2 で Service Broker が有効化されている必要があります。

   msdb に関してはデフォルトで Service Broker が有効になっているはずなのですが、ユーザーデータベースではデフォルトでは無効になっています。
   Service Broker を有効にするためにはデータベースのプロパティを開いて、[Broker が有効] を [True] にする必要があります。
   
   ただし、この変更をする際にはデータベースに対して [LCK_M_X] (排他ロック) を取得しにいきますので、対象のデータベースに接続中のユーザーがいると変更できません。
   今回は、SQL Server のサービスを再起動して、サクッと全ロックを外してから実行してしまいました。
   クエリで設定を変更する場合は以下のクエリを実行します。
   # DB 名は適宜変更する必要があります。

   USE [master] GO ALTER DATABASE [TEST] SET  ENABLE_BROKER WITH NO_WAIT GO

2. SQL Server エージェントで [トークンの置き換え] を有効化
   WMI イベント警告を使用した場合、SQL Server のジョブステップの中で WMI のイベントのプロパティを使用することが可能です。
   WMI のプロパティを使用する場合は、SQL Server エージェントで [トークンの置き換え] を有効にする必要があります。
   たとえば、ロックエスカレーションが発生した際クエリ (SQL) を取得する場合には、WMI(TextData) というように記述をするのですが、[トークンの置き換え] が有効になっていないとこの設定がそのまま文字データ (WMI(TextData) という単純な文字列) として認識されてしまいます。
   WMI(TextData) → 実行されていたクエリ に変換するためには、トークンの置き換えを有効にする必要があります。
   # ジョブ ステップでのトークンの使用

   ロックエスカレーション時のクエリは SQL Server 2008 からでないと取得できなかった気もします…。

   トークンの置き換えはSQL Server エージェントのプロパティから [警告システム] を選択して、[警告に応答するすべてのジョブのトークンを置き換える] を有効にします。
   
   クエリで有効にする場合は、以下のような内容になります。

   USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,         @alert_replace_runtime_tokens=1

以上で、事前準備は完了です。

基本的な流れはパフォーマンス条件警告と変わりません。
WMI イベント警告特有な内容としては、WMI の名前空間を指定するのとクエリを WQL ステートメントで記載する箇所になります。

名前空間に関しては通常の WMI の名前空間を指定すれば問題ありません。
SQL Server のサーバーイベントに関しては以下の指定をします。

\.rootMicrosoftSqlServerServerEvents<インスタンス名>

私の環境ではこのような指定ですね。
[\.rootMicrosoftSqlServerServerEventsSQL2008R2]

WQL ステートメントを記載するためには、イベント名を記載しなくてはいけないのですが、イベント名は [wbemtest.exe] (Windows Management Instrumentation テスト) を使用すると簡単に確認することができます。
# Windows Server 2008 R2 だと標準で入っています。

[接続] をクリックして、SQL Server の名前空間に接続をします。

名前空間に接続をしたら、[クラスの列挙] をクリックします。
[再帰] 選択して [OK] をクリックするとクラスの一覧が取得できます。

クラスとして表示されたものが WMI イベント警告で受信できるイベントになります。

今回はロックエスカレーションを受信したいので、[LOCK_ESCALATION] というイベントを受信することになります。
LOCK_ESCALATION をダブルクリックするとプロパティを取得することができます。
このプロパティ名がトークンの置き換えで使用できる WMI のプロパティ名になります。

 

WMI イベント警告としては以下のような設定を行います。

続いて応答で使用するジョブを作成します。
今回は発生時刻 / データベース名 / 実行していたクエリの情報をテーブルに格納したいと思います。
そのため、以下のテーブルを事前に作成しておきます。
# 今回は [TEST] というデータベース上に作成しています。

CREATE TABLE dbo.LockEscalationEvent     (     EventDate datetime NULL,     DatabaseName nvarchar(128) NULL,     SQL nvarchar(MAX) NULL     )  ON [PRIMARY]      TEXTIMAGE_ON [PRIMARY] GO

ジョブの名前は適当なものを設定します。

ジョブには以下のような内容を設定します。

コマンドにはトークンの置き換えを使用して、WMI のプロパティを ESCAPE_SQUOTE を使って、プロパティ内にシングルクォートが存在している場合は修飾するように指定をしたクエリを設定しています。

以上で、WMI イベント警告の設定は完了です。

それでは実際にロックエスカレーションを発生させて正常に動作するかを見ていきたいと思います。

UPDLOCK を設定して、トランザクション内で大量のデータを SELECT したためロックエスカレーションが発生しています。
そのあとに、JobStep が実行されていますね。
# JobStep は SQL Server Agent サービスの起動アカウントで SQL Server に接続して実行されています。
今回は LocalSystem アカウントで実行しています。

最後の JobStep を確認すると、INSERT 文が実行されていることが確認できます。

INSERT 文ですが、トークンの置き換えにより WMI のプロパティが文字列に置き換えられて実行されているのが確認できますね。
作成したテーブルを確認してみます。

イベントが発生した日と対象のデータベース、クエリがテーブルに格納されています。

WMI イベント警告を使用することで特定のイベント受信時にジョブを動かくすことができますのでうまく使うと運用が楽になるかもしれないですね。
今回作成した警告とジョブのサンプルスクリプトは以下になります。

USE [msdb] GO /****** Object:  Job [JOB LockEscalation]    Script Date: 12/16/2010 23:10:33 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/16/2010 23:10:33 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’JOB LockEscalation’,         @enabled=1,         @notify_level_eventlog=0,         @notify_level_email=0,         @notify_level_netsend=0,         @notify_level_page=0,         @delete_level=0,         @description=N’使用できる説明はありません。’,         @category_name=N'[Uncategorized (Local)]’,         @owner_login_name=N’WIN-1QM471JPL04Administrator’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [Step LockEscalation]    Script Date: 12/16/2010 23:10:33 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step LockEscalation’,         @step_id=1,         @cmdexec_success_code=0,         @on_success_action=1,         @on_success_step_id=0,         @on_fail_action=2,         @on_fail_step_id=0,         @retry_attempts=0,         @retry_interval=0,         @os_run_priority=0, @subsystem=N’TSQL’,         @command=N’INSERT INTO     dbo.LockEscalationEvent VALUES(     GETDATE(),     N”$(ESCAPE_SQUOTE(WMI(DatabaseName)))”,     N”$(ESCAPE_SQUOTE(WMI(TextData)))” ) ‘,         @database_name=N’TEST’,         @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO   USE [msdb] GO /****** Object:  Alert [WMI Lock Escalation]    Script Date: 12/16/2010 23:10:16 ******/ EXEC msdb.dbo.sp_add_alert @name=N’WMI Lock Escalation’,         @message_id=0,         @severity=0,         @enabled=1,         @delay_between_responses=0,         @include_event_description_in=0,         @category_name=N'[Uncategorized]’,         @wmi_namespace=N’\.rootMicrosoftSqlServerServerEventsSQL2008R2′,         @wmi_query=N’SELECT * FROM LOCK_ESCALATION’,         @job_id=N’6315f399-7921-4431-a4d1-89135a792fa9′ GO

パフォーマンス条件警告と WMI イベント警告を使用することでデータベースの特定の状態を受信してジョブを実行することが可能になります。
# WMI イベント警告は良い機会だったのでついでにまとめたものですが。

パフォーマンス条件警告でログのバックアップを取るのは今まで考えていなかったので、なるほどと思いました。

この後には [バックアップポリシーの検討] ということで、バックアップタクトの一例や障害発生時の復元の概要についてのお話がありました。

次の投稿では、バックアップポリシーについてまとめてみたいと思います。

今回は [自動拡張] について振り返ってみたいと思います。。

セミナーのログ管理のセッションでは [自動拡張] についてのお話がありました。

SQL Server のデータファイル / ログファイルには [自動拡張] という設定があります。
自動拡張を設定することでファイルの空きが無くなったときにディスクに空きがある場合、自動的に拡張するように設定することができます。

自動拡張のサイズは、[現在のサイズに対しての比率 (%)]  と [MB 単位] で、最大サイズに関しては [MB 単位] と [無制限] で指定することができます。

それでは、ログファイルの自動拡張についてまとめていきたいと思います。

■ログファイルの自動拡張について

ログファイルは以下の構成となっています。

自動拡張が発生すると以下のように拡張がされます。
# 手動拡張でも同様です。

拡張が発生すると拡張分のディスクサイズが確保されます、その拡張分が仮想ログ (VLF) で分割されます。
上の絵では、VLF が 4 つで構成された形になっていますが、拡張するサイズによって 4 / 8 / 16 のどの個数で分割するかが決まります。
そのため、細かな拡張を繰り返すとログファイルを構成する VLF の個数が多くなりパフォーマンスに影響が出てきます。
# これに関しては別の機会にまとめようと思っています。

それでは、実際の環境で自動拡張を見ていきたいと思います。

現在、ログは 13,041,644 Byte の 2 個の VLF で構成がされています。
# VLF は作成 / 拡張時は最小で 4 個になるのですが、ファイルの圧縮 (SHRINK FILE) をすることで最小で 2 個にできます。

データベースのプロパティからみた設定はこのようになっています。

それでは、データを追加 (INSERT) して、ログファイルの自動拡張を見ていきたいと思います。

このグラフはパフォーマンスモニタで、[Log File(s) Used (KB)] と、[Log Growths] を取得したものになります。
# Log Growths に関しては SQL Server のサービスが最後に起動してからの値だったはずなので、0 からは始まっていません。

ログファイルの現在のサイズと使用状況がこちらになります。

25MB だったものが、925MB になっています。
今回は、100MB 単位で自動拡張をするように設定していますので、 9 回の拡張が発生したことになります。
上のグラフでも自動拡張の発生回数は 4 → 13 になっていますので回数は合っていますね。

仮想ログの状態も見てみたいと思います。

この下にもデータは続いており、全部で 74 個の仮想ログがあります。
100MB の拡張だと一度の拡張に対して仮想ログが 8 個作成されます。
そのため
8 個 × 拡張 9 回 = 72 個 + 初期の 2 個 = 74 個
の仮想ログが作成されたことになります。

 

自動拡張に関しては、SQL Server のデフォルト トレースにも出力がされます。
デフォルト トレースは SQL Server をインストールしたインスタンスのディレクトリの [LOG] フォルダに出力がされます。
私が今回使用している環境では以下のディレクトリになります。
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLLog]

デフォルト トレースは SQLTrace の .trc ファイルですので [SQL Server Profiler] で開くか [fn_trace_gettable ] を使用してクエリで情報を取得することができます。

今回はfn_trace_gettable を使用して情報を取得したいと思います。

DECLARE @filename varchar(500) SELECT @filename = path FROM sys.traces WHERE is_default = 1 SELECT @filename SELECT     convert(int, EventClass) as EventClass,     DatabaseName,     Filename,     (Duration/1000) as Duration,     StartTime,     EndTime,     (IntegerData*8.0/1024) as ChangeInSize FROM     sys.fn_trace_gettable(@filename, default) WHERE     EventClass >=  92     AND     EventClass <=  95     AND     DatabaseName = DB_NAME() ORDER BY     StartTime DESC

 

実行後の結果はこちらになります。

今回は、20:35 ～ 20:37 に実行していますので、デフォルト トレースにもこの時間帯のログは 9 件出力されています。

自動拡張を設定していない場合は、以下のようになります。

ログファイルの上限まで達したら拡張ができないため、上限に達した後の追加処理はエラーとなります。

 

自動拡張を設定しておくことで、当初想定していたログファイルでは足りなくなったときでも処理を継続することができるようになります。
セミナーではログの拡張時には [ログの書き込み待ち] が発生するため、拡張は発生させないように最初から適切なサイズを設定するというお話がありました。

続いてログの書き込み待ちについてみていきたいと思います。

■拡張時のログの書き込み待ち

自動拡張だけに限らずログの拡張が行われている際にはログの書き込みがブロックされ、拡張が終わるまで待ち状態となります。
[自動拡張のログファイルの使用状況] のグラフで拡張が発生している際に、ログファイルの使用状況が一瞬止まって入るのが確認できます。
# さほど負荷をかけていない検証なので、本当に一瞬ですが。

自動拡張ではログの書き込み待ちを確認するのが難しいので、データの追加を行っている最中に、10GB のファイル拡張を行って検証をしてみたいと思います。
# 自動チェックポイントの実行は無効にしています。

拡張をしないでデータの追加を行った場合は以下のようなグラフになります。
ログファイルの使用状況も停止することなく上がっており、ログの書き込み待ちに関しては発生していません。

データの追加中に10GB のログの拡張を行ったものがこちらになります。
ログファイルの使用状況が平らになっている部分が拡張を実行したタイミングになります。
ログの拡張中は使用状況の上がりも鈍り、書き込み待ちの秒数が発生しているのが確認できます。

それでは、この書き込み待ちを [WRITELOG] の待ち事象からも見てみたいと思います。
WRITELOG の待ち事象を取得するには以下のクエリを実行します。

SELECT * FROM sys.dm_os_wait_stats WHERE wait_type = ‘WRITELOG’

こちらが通常の追加を行う前後の待ち事象の状態になります。

処理を行っている間に
waiting_tasks_count = 501,013 ? 400,927 = 100,086
wait_time_ms = 306,407 ? 248,635 = 57,772
の WRITELOG に関しての待ち事象が発生していたのが確認できます。

それでは、拡張をした場合はどうなるでしょう。


waiting_tasks_count = 607,419 ? 507,039 = 100,380
wait_time_ms = 378,033 ? 310,391 = 67,642

というようにログの書き込み待ちによる待ち事象が増加していることが確認できます。

余談ですが、ファイルの瞬時初期化 (SQL Server のサービス起動アカウントに [ボリュームの保守タスクを実行] を付与) に関しては BOL に [ログ ファイルの初期化ではそのまま空にする必要がありますが～]  と書かれているように有効にしても効果は出ません。

waiting_tasks_count = 100,397 ? 13  = 100384
wait_time_ms = 67,068 ? 86 = 66,982

ログ管理に置いて自動拡張は保険として考え、運用中は発生しないようにすることでログの書き込み待ちを減らし、パフォーマンスの向上につなげることが可能となります。

セミナーでは、ログ管理のもしもの時の備えとして、[パフォーマンス条件警告] を使用したログの管理のお話がありました。

次の投稿では、パフォーマンス条件警告とその簡単な応用についてまとめてみたいと思います。