{"id":2653,"date":"2013-04-27T11:38:28","date_gmt":"2013-04-27T11:38:28","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2653"},"modified":"2013-04-28T03:34:43","modified_gmt":"2013-04-28T03:34:43","slug":"xevent%e5%bd%93sqlserver%e6%9c%89%e7%94%a8%e6%88%b7%e7%ba%a7%e9%94%99%e8%af%af%e6%97%b6%e8%ae%b0%e5%bd%95","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/04\/27\/xevent%e5%bd%93sqlserver%e6%9c%89%e7%94%a8%e6%88%b7%e7%ba%a7%e9%94%99%e8%af%af%e6%97%b6%e8%ae%b0%e5%bd%95\/","title":{"rendered":"XEVENT:\u5f53sqlserver\u6709\u7528\u6237\u7ea7\u9519\u8bef\u65f6\u8bb0\u5f55"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n\u7cfb\u7edf\u4fe1\u606f\u7ea7\u522b\uff1ahttp:\/\/blog.sqlauthority.com\/2007\/04\/25\/sql-server-error-messages-sysmessages-error-severity-level\/\r\n\r\n\u6d4b\u8bd5\uff1a\r\nBEGIN TRY\r\n    -- Generate a divide-by-zero error.\r\n    SELECT 1\/0;\r\nEND TRY\r\nBEGIN CATCH\r\n    SELECT ERROR_SEVERITY() AS ErrorSeverity;\r\nEND CATCH;\r\n\r\n\u5f53sqlserver\u6709\u7528\u6237\u7ea7\u9519\u8bef\u65f6\u8bb0\u5f55\r\nCREATE event session xEvents_Session on server\r\nadd event sqlserver.error_reported\r\n(\r\naction\r\n(\r\nsqlserver.session_id, -- SPID which raises the error\r\nsqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan\r\nsqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes\r\npackage0.callstack, -- Callstack for extended debugging purposes\r\nsqlserver.sql_text, -- T-SQL query which encountered the error\r\nsqlserver.username, -- Name of the user that reported the error\r\nsqlserver.client_app_name, -- Client application name\r\nsqlserver.client_hostname, -- Host which initiated the query\r\nsqlserver.database_name -- Database against which the query was being executed\r\n) \r\n WHERE severity &gt;= 11 AND Severity &lt;=16\r\n)\r\nadd target package0.ring_buffer\r\nwith (max_dispatch_latency=1seconds)\r\nGO\r\n-- start the tracing\r\nalter event session xEvents_Session on server state = start\r\n\r\n--alter event session xEvents_Session on server state = stop\r\n--drop event session xEvents_Session on SERVER\r\n\r\n\r\n\r\n--\u67e5\u770b\r\n\r\nSELECT \r\n    n.value(&#39;(event\/@name)[1]&#39;, &#39;varchar(50)&#39;) AS event_name,\r\n    n.value(&#39;(event\/@package)[1]&#39;, &#39;varchar(50)&#39;) AS package_name,\r\n    DATEADD(hh, \r\n            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), \r\n            n.value(&#39;(event\/@timestamp)[1]&#39;, &#39;datetime2&#39;)) AS [timestamp],  \r\nn.value(&#39;(event\/action[@name=&quot;database_name&quot;]\/value)[1]&#39;, &#39;nvarchar(128)&#39;) AS [database_name],\r\n    n.value(&#39;(event\/action[@name=&quot;sql_text&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [sql_text],\r\nn.value(&#39;(event\/data[@name=&quot;message&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [message],\r\nn.value(&#39;(event\/action[@name=&quot;username&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [username],\r\nn.value(&#39;(event\/action[@name=&quot;client_hostname&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [client_hostname],\r\nn.value(&#39;(event\/action[@name=&quot;client_app_name&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [client_app_name],\r\nn.value(&#39;(event\/action[@name=&quot;session_id&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [session_id],\r\nn.value(&#39;(event\/action[@name=&quot;plan_handle&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [plan_handle],\r\nn.value(&#39;(event\/action[@name=&quot;tsql_stack&quot;]\/value\/frames\/frame\/@handle)[1]&#39;, &#39;varchar(max)&#39;) AS [tsql_stack],\r\nn.value(&#39;(event\/action[@name=&quot;tsql_stack&quot;]\/value\/frames\/frame\/@offsetStart)[1]&#39;, &#39;int&#39;) AS [statement_start_offset],\r\nn.value(&#39;(event\/action[@name=&quot;tsql_stack&quot;]\/value\/frames\/frame\/@offsetEnd)[1]&#39;, &#39;int&#39;) AS [statement_end_offset],\r\nn.value(&#39;(event\/action[@name=&quot;callstack&quot;]\/value)[1]&#39;, &#39;nvarchar(max)&#39;) AS [callstack],\r\nn.value(&#39;(event\/data[@name=&quot;object_id&quot;]\/value)[1]&#39;, &#39;int&#39;) AS [object_id],\r\n    n.value(&#39;(event\/data[@name=&quot;object_type&quot;]\/value)[1]&#39;, &#39;nvarchar(128)&#39;) AS [object_type],\r\n    n.value(&#39;(event\/data[@name=&quot;duration&quot;]\/value)[1]&#39;, &#39;int&#39;) AS [duration],\r\n    n.value(&#39;(event\/data[@name=&quot;cpu&quot;]\/value)[1]&#39;, &#39;int&#39;) AS [cpu],\r\n    n.value(&#39;(event\/data[@name=&quot;reads&quot;]\/value)[1]&#39;, &#39;int&#39;) AS [reads],\r\n    n.value(&#39;(event\/data[@name=&quot;writes&quot;]\/value)[1]&#39;, &#39;int&#39;) AS [writes] into #1120\r\nFROM\r\n(    SELECT td.query(&#39;.&#39;) as n\r\n    FROM \r\n    (\r\n        SELECT CAST(target_data AS XML) as target_data\r\n        FROM sys.dm_xe_sessions AS s \r\n        JOIN sys.dm_xe_session_targets AS t \r\n            ON t.event_session_address = s.address\r\n        WHERE s.name = &#39;xEvents_Session&#39;\r\n          --AND t.target_name = &#39;ring_buffer&#39;\r\n    ) AS sub\r\n    CROSS APPLY target_data.nodes(&#39;RingBufferTarget\/event&#39;) AS q(td)\r\n) as TAB\r\n\r\n--\u67e5\u770b\u6709sql\u7684\r\nSELECT TIMESTAMP,database_name,[message],sql_text,username,client_hostname,client_app_name\r\nFROM #1120 a\r\nWHERE a.sql_text != &#39;&#39;\r\n\r\n\r\n--\u67e5\u770b\u5e94\u7528\u6267\u884c\u7684\r\nSELECT TIMESTAMP,database_name,[message],\r\nSUBSTRING(qt.text,a.statement_start_offset\/2+1,\r\n(case when a.statement_end_offset = -1\r\nthen DATALENGTH(qt.text) \r\nelse a.statement_end_offset end -a.statement_start_offset)\/2 + 1) sql_text,qt.text alltext,\r\nusername,client_hostname,client_app_name\r\nFROM #1120 a\r\nCROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt\r\nWHERE a.sql_text IS NULL AND tsql_stack != &#39;&#39; AND client_app_name = &#39;.Net SqlClient Data Provider&#39;\r\nORDER BY TIMESTAMP DESC\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u7cfb\u7edf\u4fe1\u606f\u7ea7\u522b\uff1ahttp:\/\/blog.sqlauthority.com\/2007\/04\/25\/sql-serv [&hellip;]<\/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-2653","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\/2653","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=2653"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2653\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2653"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}