SQL Server Ring Buffer--Ring_buffer_exception trace exception

SQL Server Ring buffer--ring_buffer_exception trace Exception

Dynamic management view Sys.dm_os_ring_buffers makes it easier to locate problems in real time. The ring buffer contains a large number of events that occur on the server. Currently, I am encountering a lock request timeout problem. Based on the SQL Server Profiler trace capture, the discovery server receives a large amount of information such as:

Lock request time out period exceeded.

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

The following script gives the time range of an exception stored in a ring buffer, outputting a large number of occurrences of the exception.

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://s3.51cto.com/wyfs02/M02/54/1A/wKioL1R4Ip-A0rz4AAIdJYZV5Ts779.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://s3.51cto.com/wyfs02/M00/54/1A/wKioL1R4Ip_yGH0fAAA_b8BoxUE753.jpg "height=" "border=" 0 "/>

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1584028

SQL Server Ring Buffer--Ring_buffer_exception trace exception