--以下xevent记录了通过sqlserver查询工具直接更新,删除表数据时的记录。
IF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'EventErrorMonitor' )
BEGIN
CREATE EVENT session EventErrorMonitor 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)
ALTER EVENT SESSION EventErrorMonitor on server state = START
END
ELSE
BEGIN
SELECT
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="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]
into #ErrorMonitor
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 = 'EventErrorMonitor'
--AND t.target_name = 'ring_buffer'
) AS sub
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as TAB
INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
SELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name
FROM #ErrorMonitor a
WHERE a.sql_text != '' AND client_app_name !='Microsoft SQL Server Management Studio - 查询'
INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
--查看应用执行的
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 #ErrorMonitor 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'
DROP TABLE #ErrorMonitor
--重启以清空
ALTER EVENT SESSION EventErrorMonitor ON SERVER STATE = STOP
ALTER EVENT SESSION EventErrorMonitor on server state = START
END