SQLSERVER XEvent的应用

--以下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