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 "/>