使用Extended Events收集错误信息

扩展事件提供了一个轻量级的平台可以用来收集错误信息，方便DBA查看最近的数据库错误配合开发人员一起解决问题。

 

下面的例子我们创建extended event会话来获取错误208, 2812,and 4121。这三个错误分别对应：Invalid object name, Could not find stored procedure, and Cannotfind either column or the user-defined function or aggregate. 可以根据扩展事件sqlserver.error_reported。我们获取下面的详细信息：

 

sqlserver.session_id

sqlserver.sql_text

sqlserver.client_app_name

sqlserver.client_hostname

sqlserver.database_id

sqlserver.username

 

通过这些信息我们可以了解到是哪个应用出现的问题尽快解决，创建扩展事件会话使用下面的脚本：

 

 CREATEEVENT SESSION exErrorsON SERVER -- Session Name

ADD EVENT sqlserver.error_reported-- Event wewant to capture

(

ACTION -- What contents to capture

(

sqlserver.session_id

,sqlserver.sql_text

,sqlserver.client_app_name

,sqlserver.client_hostname

,sqlserver.database_id

,sqlserver.username

)

-- Somepredicate or filter (here it is object not found error number)

WHERE (error= 208

OR error = 2812

OR error = 4121

)

)

ADD TARGET package0.ring_buffer

WITH (max_dispatch_latency= 5seconds); -- Thetarget

GO

ALTER EVENTSESSION exErrors ONSERVER STATE = START

GO

 

查询错误信息的脚本：

 

WITH exErrors

AS (

SELECT CAST(target_data AS xml) AS SessionData

FROM sys.dm_xe_session_targetsst

INNER JOINsys.dm_xe_sessions sONs.address= st.event_session_address

WHERE name = 'exErrors'

)

SELECT

error.value('(@timestamp)[1]','datetime') as event_timestamp

,error.value('(data/value)[5]','varchar(max)') as [error_message]

,error.value('(data/value)[1]','int') as error

,error.value('(action/value)[3]','nvarchar(255)') AS client_app_name

,error.value('(action/value)[4]','nvarchar(255)') as client_hostname

,DB_NAME(error.value('(action/value)[5]','int')) AS database_name

,error.value('(action/value)[6]','nvarchar(128)') AS username

,error.value('(action/value)[2]','varchar(max)') as sql_text

,error.value('(action/value)[1]','int') as session_id

,error.value('(data/value)[4]','bit') as user_defined

FROM exErrors d

CROSS APPLY SessionData.nodes('//RingBufferTarget/event')AS t(error)

WHERE error.value('@name','nvarchar(128)') = 'error_reported';

 

结果如下：