{"id":2651,"date":"2013-04-27T11:35:20","date_gmt":"2013-04-27T11:35:20","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2651"},"modified":"2013-04-28T06:31:09","modified_gmt":"2013-04-28T06:31:09","slug":"xevent%ef%bc%9a%e8%ae%b0%e5%bd%95%e5%af%b9%e8%a1%a8%e7%9a%84%e6%9b%b4%e6%96%b0%e5%88%a0%e9%99%a4","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/04\/27\/xevent%ef%bc%9a%e8%ae%b0%e5%bd%95%e5%af%b9%e8%a1%a8%e7%9a%84%e6%9b%b4%e6%96%b0%e5%88%a0%e9%99%a4\/","title":{"rendered":"XEVENT\uff1a\u8bb0\u5f55\u5bf9\u8868\u7684\u66f4\u65b0\u5220\u9664"},"content":{"rendered":"
\r\n--\u4ee5\u4e0bxevent\u8bb0\u5f55\u4e86\u901a\u8fc7sqlserver\u67e5\u8be2\u5de5\u5177\u76f4\u63a5\u66f4\u65b0\uff0c\u5220\u9664\u8868\u6570\u636e\u65f6\u7684\u8bb0\u5f55\u3002\r\nIF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'EventErrorMonitor' )\r\nBEGIN \r\n\tCREATE EVENT session EventErrorMonitor on server\r\n\tADD EVENT sqlserver.error_reported\r\n\t(\r\n\tACTION\r\n\t(\r\n\tsqlserver.session_id, -- SPID which raises the error\r\n\tsqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan\r\n\tsqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes\r\n\tpackage0.callstack, -- Callstack for extended debugging purposes\r\n\tsqlserver.sql_text, -- T-SQL query which encountered the error\r\n\tsqlserver.username, -- Name of the user that reported the error\r\n\tsqlserver.client_app_name, -- Client application name\r\n\tsqlserver.client_hostname, -- Host which initiated the query\r\n\tsqlserver.database_name -- Database against which the query was being executed\r\n\t) \r\n\t WHERE severity >= 11 AND Severity <=16\r\n\t)\r\n\tADD TARGET package0.ring_buffer\r\n\tWITH (max_dispatch_latency=1seconds)\r\n\r\n\tALTER EVENT SESSION EventErrorMonitor on server state = START\r\n  \r\nEND \r\nELSE\r\nBEGIN\r\n\r\n\tSELECT \r\n\t\t DATEADD(hh, \r\n\t\t\t\tDATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), \r\n\t\t\t\tn.value('(event\/@timestamp)[1]', 'datetime2')) AS [timestamp],  \r\n\t\tn.value('(event\/action[@name="database_name"]\/value)[1]', 'nvarchar(128)') AS [database_name],\r\n\t\tn.value('(event\/action[@name="sql_text"]\/value)[1]', 'nvarchar(max)') AS [sql_text],\r\n\t\tn.value('(event\/data[@name="message"]\/value)[1]', 'nvarchar(max)') AS [message],\r\n\t\tn.value('(event\/action[@name="username"]\/value)[1]', 'nvarchar(max)') AS [username],\r\n\t\tn.value('(event\/action[@name="client_hostname"]\/value)[1]', 'nvarchar(max)') AS [client_hostname],\r\n\t\tn.value('(event\/action[@name="client_app_name"]\/value)[1]', 'nvarchar(max)') AS [client_app_name],\r\n\t\tn.value('(event\/action[@name="tsql_stack"]\/value\/frames\/frame\/@handle)[1]', 'varchar(max)') AS [tsql_stack],\r\n\t\tn.value('(event\/action[@name="tsql_stack"]\/value\/frames\/frame\/@offsetStart)[1]', 'int') AS [statement_start_offset],\r\n\t\tn.value('(event\/action[@name="tsql_stack"]\/value\/frames\/frame\/@offsetEnd)[1]', 'int') AS [statement_end_offset]\r\n\tinto #ErrorMonitor\r\n\tFROM\r\n\t(    SELECT td.query('.') as n\r\n\t\tFROM \r\n\t\t(\r\n\t\t\tSELECT CAST(target_data AS XML) as target_data\r\n\t\t\tFROM sys.dm_xe_sessions AS s \r\n\t\t\tJOIN sys.dm_xe_session_targets AS t \r\n\t\t\t\tON t.event_session_address = s.address\r\n\t\t\tWHERE s.name = 'EventErrorMonitor'\r\n\t\t\t  --AND t.target_name = 'ring_buffer'\r\n\t\t) AS sub\r\n\t\tCROSS APPLY target_data.nodes('RingBufferTarget\/event') AS q(td)\r\n\t) as TAB\r\n  \r\n\tINSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)\r\n\tSELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name\r\n\tFROM #ErrorMonitor a\r\n\tWHERE a.sql_text != '' AND client_app_name !='Microsoft SQL Server Management Studio - \u67e5\u8be2'\r\n\r\n\tINSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)\r\n\t--\u67e5\u770b\u5e94\u7528\u6267\u884c\u7684\r\n\tSELECT  TIMESTAMP,database_name,[message],\r\n\tSUBSTRING(qt.text,a.statement_start_offset\/2+1,\r\n\t\t\t\t(case when a.statement_end_offset = -1\r\n\t\t\t\tthen DATALENGTH(qt.text) \r\n\t\t\t\telse a.statement_end_offset end -a.statement_start_offset)\/2 + 1) sql_text,qt.text alltext,\r\n\tusername,client_hostname,client_app_name\r\n\tFROM #ErrorMonitor a\r\n\tCROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt\r\n\tWHERE a.sql_text IS NULL AND tsql_stack != '' AND client_app_name = '.Net SqlClient Data Provider'\r\n\r\n\tDROP TABLE #ErrorMonitor\r\n\r\n\t--\u91cd\u542f\u4ee5\u6e05\u7a7a\r\n\tALTER EVENT SESSION EventErrorMonitor ON SERVER STATE = STOP\r\n\tALTER EVENT SESSION EventErrorMonitor on server state = START\r\nEND \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
–\u4ee5\u4e0bxevent\u8bb0\u5f55\u4e86\u901a\u8fc7sqlserver\u67e5\u8be2\u5de5\u5177\u76f4\u63a5\u66f4\u65b0\uff0c\u5220\u9664\u8868\u6570\u636e\u65f6\u7684\u8bb0\u5f55\u3002 IF NOT EXIS […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2651","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2651"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2651\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}