sys.dm_os_ring_buffers: You can use the undocumented Ring Buffer DMV to troubleshoot the below issues.
- Security Exceptions
- Exception raised at SQL Operating System level
- Connection Dropped By the Server
- System Resource Utilization
- Memory Pressure
- CLR Integration Scheduler State
- Extended Events Subsystems State
Execute the below query to get the distinct ring buffer type.
select distinct ring_buffer_type from sys.dm_os_ring_buffers
- RING_BUFFER_RESOURCE_MONITOR
- RING_BUFFER_SCHEDULER_MONITOR
- RING_BUFFER_MEMORY_BROKER
- RING_BUFFER_SECURITY_ERROR
- RING_BUFFER_XE_BUFFER_STATE
- RING_BUFFER_SCHEDULER
- RING_BUFFER_CONNECTIVITY
- RING_BUFFER_EXCEPTION
- RING_BUFFER_XE_LOG
Check below script as example to troubleshoot the Security Issue using ring buffer. You can change the ring buffer type in below script to troubleshoot the different issues.
-- Check the Ring Buffer in SQL Server 2008 SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_WARNINGS ON SET ANSI_PADDING ON SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time, dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time], ST.* , sys.ms_ticks AS [Current Time] FROM (SELECT RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode], RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName], RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName], RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID], RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id], RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST CROSS JOIN sys.dm_os_sys_info sys ORDER BY ST.[RecordTime] ASC -- Script to Check the Ring Buffer in SQL Server 2005 SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_WARNINGS ON SET ANSI_PADDING ON SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - ST.[RecordTime]), GETDATE()) AS NotificationTime, ST.* , sys.ms_ticks AS [CurrentTime] FROM (SELECT RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode], RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName], RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName], RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID], RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id], RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST CROSS JOIN sys.dm_os_sys_info sys ORDER BY ST.[RecordTime] ASC
From the output we can see the hexadecimal error code 0x6FD. You have to convert these error code into decimal value, which will be 0x6FD = 1789
Check the above decimal error codes using the NET HELPMSG command, which will give you more information on the issue.