SQLServer ring buffer (RingBuffer) -- RING_BUFFER_EXCEPTION trace

Source: Internet
Author: User
SQLServer ring buffer (RingBuffer) -- RING_BUFFER_EXCEPTION tracking Abnormal dynamic management view sys. dm_ OS _ring_buffers makes it easier to locate problems in real time. The circular buffer contains a large number of events on the server. Currently, I am experiencing lock request timeout. Based on SQLServerProfiler trace capture and Discovery

SQL Server Ring Buffer -- RING_BUFFER_EXCEPTION tracking Abnormal dynamic management view sys. dm_ OS _ring_buffers makes it easier to locate problems in real time. The circular buffer contains a large number of events on the server. Currently, I am experiencing lock request timeout. Based on SQL Server Profiler trace capture, found

SQL Server Ring Buffer -- RING_BUFFER_EXCEPTION tracing exception

The dynamic management view sys. dm_ OS _ring_buffers makes it easier to locate problems in real time. The circular buffer contains a large number of events on the server. Currently, I am experiencing lock request timeout. According to the SQL Server Profiler trace capture, the Server receives a large amount of information as follows:

Lock request time out period exceeded.

We found the statement and modified it to prevent the request from timeout. Now the Server is being monitored. I don't want to run SQL Server Profiler to track the generation of this message. Therefore, I want to use the dynamic management view of the circular buffer to monitor whether further lock request timeout occurs on the server. This makes it easier to monitor instances.

The following script provides a time range for exceptions stored in the ring buffer and outputs a large number of exceptions.

For SQL Server 2005:

DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINTSELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_infoselect @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTimeSELECT record_id,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,y.Error,UserDefined,b.description as NormalizedTextFROM (SELECTrecord.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/Exception/Error)[1]', 'int') AS Error,record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMPFROM (SELECT TIMESTAMP, CONVERT(XML, record) AS recordFROM sys.dm_os_ring_buffersWHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'AND record LIKE '% %') AS x) AS yINNER JOIN sys.sysmessages bon y.Error = b.errorWHERE b.msglangid = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitorORDER BY record_id DESC

For SQL Server 2008:

DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINTSELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_infoselect @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTimeSELECT record_id,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,Error,UserDefined,text as NormalizedTextFROM (SELECTrecord.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/Exception/Error)[1]', 'int') AS Error,record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMPFROM (SELECT TIMESTAMP, CONVERT(XML, record) AS recordFROM sys.dm_os_ring_buffersWHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'AND record LIKE '% %') AS x) AS yINNER JOIN sys.messages bon y.Error = b.message_idWHERE b.language_id = 1033 and y.Error = 1222 -- Change the message number to the message number that you want to monitorORDER BY record_id DESC

650) this. width = 650; "title =" clip_image001 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image001 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121914IV-0.jpg "height =" 243 "border =" 0 "/>

650) this. width = 650; "title =" clip_image002 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image002 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121914K60-1.jpg "height =" 70 "border =" 0 "/>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.