XEVENT:当sqlserver有用户级错误时记录

 

 

系统信息级别：http://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/

测试：
BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

当sqlserver有用户级错误时记录
CREATE event session xEvents_Session on server
add event sqlserver.error_reported
(
action
(
sqlserver.session_id, -- SPID which raises the error
sqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan
sqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes
package0.callstack, -- Callstack for extended debugging purposes
sqlserver.sql_text, -- T-SQL query which encountered the error
sqlserver.username, -- Name of the user that reported the error
sqlserver.client_app_name, -- Client application name
sqlserver.client_hostname, -- Host which initiated the query
sqlserver.database_name -- Database against which the query was being executed
)
 WHERE severity >= 11 AND Severity <=16
)
add target package0.ring_buffer
with (max_dispatch_latency=1seconds)
GO
-- start the tracing
alter event session xEvents_Session on server state = start

--alter event session xEvents_Session on server state = stop
--drop event session xEvents_Session on SERVER

--查看

SELECT
    n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh,
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name],
    n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
n.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],
n.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username],
n.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [client_app_name],
n.value('(event/action[@name="session_id"]/value)[1]', 'nvarchar(max)') AS [session_id],
n.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') AS [plan_handle],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset],
n.value('(event/action[@name="callstack"]/value)[1]', 'nvarchar(max)') AS [callstack],
n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
    n.value('(event/data[@name="object_type"]/value)[1]', 'nvarchar(128)') AS [object_type],
    n.value('(event/data[@name="duration"]/value)[1]', 'int') AS [duration],
    n.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
    n.value('(event/data[@name="reads"]/value)[1]', 'int') AS [reads],
    n.value('(event/data[@name="writes"]/value)[1]', 'int') AS [writes] into #1120
FROM
(    SELECT td.query('.') as n
    FROM
    (
        SELECT CAST(target_data AS XML) as target_data
        FROM sys.dm_xe_sessions AS s
        JOIN sys.dm_xe_session_targets AS t
            ON t.event_session_address = s.address
        WHERE s.name = 'xEvents_Session'
          --AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as TAB

--查看有sql的
SELECT TIMESTAMP,database_name,[message],sql_text,username,client_hostname,client_app_name
FROM #1120 a
WHERE a.sql_text != ''

--查看应用执行的
SELECT TIMESTAMP,database_name,[message],
SUBSTRING(qt.text,a.statement_start_offset/2+1,
(case when a.statement_end_offset = -1
then DATALENGTH(qt.text)
else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,
username,client_hostname,client_app_name
FROM #1120 a
CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt
WHERE a.sql_text IS NULL AND tsql_stack != '' AND client_app_name = '.Net SqlClient Data Provider'
ORDER BY TIMESTAMP DESC