SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database で Azure ストレージに保存した拡張イベントのログをクエリで検索してみる

leave a comment

SQL Database の拡張イベントを永続化する場合、Azure ストレージに保存することになりますが、Azure ストレージの保存したログに関しては直接クエリで確認することができます。

クエリの実行に、CPU を結構喰うので、ローカルにダウンロードしてからの方がよいかと思いますが。

詳細については SQL Database の拡張イベントのためのイベント ファイル ターゲット コード を読むとよいかと

拡張イベントの保存先として使用しているストレージの情報については、以下のクエリで確認ができます。

-- 拡張イベントの情報の取得 
SELECT
	name,
	target_name,
	target.value('(/EventFileTarget/File/@name)[1]', 'varchar(500)') AS xEventFile
FROM
(
SELECT
	name,
	target_name, 
	CAST(target_data AS XML) AS target
FROM 
	sys.dm_xe_database_session_targets st
	LEFT JOIN
	sys.dm_xe_database_sessions s
	ON
	s.address = st.event_session_address
WHERE
	target_name = 'event_file'
) AS T

image

ストレージの場所が特定できたら以下のクエリで拡張イベントの内容を確認することができます。

-- 直接クエリ版
DECLARE @xEventFile varchar(500) = 'https://<ストレージアカウント>.blob.core.windows.net/<コンテナー>/<トレースファイル>'

SELECT
	object_name,
	target.value('(/event/@timestamp)[1]', 'datetime2') AS timestamp,
	target.query('/event/data[@name="error_number"]').value('.', 'int') AS error_number,
	target.query('/event/data[@name="message"]').value('.', 'nvarchar(max)') AS error_number,
	target.query('/event/action[@name="sql_text"]').value('.', 'nvarchar(max)') AS error_number
FROM(
	SELECT
		*,
		CAST(event_data AS XML) AS target
	FROM
		sys.fn_xe_file_target_read_file
			(@xEventFile, null, null, null)
	WHERE
		object_name = 'error_reported'
		AND
		CAST(event_data AS XML).query('/event/data[@name="error_number"]').value('.', 'int')  NOT IN(102, 156, 207, 2528, 7955)
) AS T

-- 一時テーブル版
DECLARE @xEventFile varchar(500) = 'https://<ストレージアカウント>.blob.core.windows.net/<コンテナー>/<トレースファイル>'

DROP TABLE IF EXISTS #xmldata
CREATE TABLE #xmldata (C1 int IDENTITY PRIMARY KEY,object_name varchar(255), xml_data XML)

INSERT INTO #xmldata (object_name, xml_data)
SELECT
	object_name,
	CAST(event_data AS XML) AS target
FROM
	sys.fn_xe_file_target_read_file
		(@xEventFile, null, null, null)

CREATE PRIMARY XML INDEX idx_xml on #xmldata (xml_data)

SELECT
	object_name,
	xml_data.value('(/event/@timestamp)[1]', 'datetime2') AS timestamp,
	xml_data.query('/event/data[@name="error_number"]').value('.', 'int') AS error_number,
	xml_data.query('/event/data[@name="message"]').value('.', 'nvarchar(max)') AS error_number,
	xml_data.query('/event/action[@name="sql_text"]').value('.', 'nvarchar(max)') AS error_number
FROM
	#xmldata
WHERE
	object_name = 'error_reported'
	AND
	xml_data.query('/event/data[@name="error_number"]').value('.', 'int') NOT IN(102, 156, 207, 2528, 7955)

	

トレースファイルを指定する際に「.xel」まで指定すると該当のファイルのみ、トレースファイルの先頭のみを指定した場合 (上記の画像の例だと「Basic_Trace」) はストレージアカウント内の該当のファイルをまとめて検索することができます。

Written by masayuki.ozawa

2月 12th, 2017 at 9:41 pm

Posted in SQL Database

Tagged with

Leave a Reply

*