In SQL 2008 R2 and before there are very few ways to identify transactions that have been rolled back. A server side trace capturing the SQLTransaction events is one way. You’d filter on EventSubClass = 2 to capture just the rollbacks. Or you could try reading the transaction log using fn_dblog() filtering on Operation = LOP_ABORT_XACT. Yet another way, which provides very limited data, is to periodically query the syscacheobjects sql column for any text which is like ‘%rollback%’. Each of these methods has their own issues and overheads. Ideally we’d like a light-weight solution.
Fortunately, in SQL Server 2012 extended events comes to the rescue. You can either use extended events to map to a trace definition for SQLTransaction events or you could roll your own event session. Here’s one possible roll your own solution:
-- Create the event session to catch rollbacks CREATE EVENT SESSION evtsRollback ON SERVER ADD EVENT sqlserver.sql_transaction ( ACTION (sqlserver.session_id,sqlserver.database_id,sqlserver.sql_text) -- Could add more actions WHERE transaction_state = 2 -- Rollbacks only AND sqlserver.database_id = 8 -- Just this one database ) ADD TARGET package0.ring_buffer; GO -- Start the sesssion ALTER EVENT SESSION evtsRollback ON SERVER STATE=START; -- Begin a transaction, make a change, then rollback the transaction -- Drop the event ALTER EVENT SESSION evtsRollback ON SERVER DROP EVENT sqlserver.sql_transaction; -- Query the results SELECT event.value('(event/@name)','varchar(50)') AS event, DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)','datetime2')) AS [timestamp], event.value('(event/action[@name="session_id"])','int') AS session_id, event.value('(event/action[@name="database_id"])','int') AS database_id, event.value('(event/data[@name="duration"])','bigint') AS duration_microseconds, event.value('(event/data[@name="transaction_id"])','bigint') AS transaction_id, event.value('(event/data[@name="transaction_state"]/text)','nvarchar(max)') AS transaction_state, event.value('(event/data[@name="transaction_type"]/text)','nvarchar(max)') AS transaction_type, event.value('(event/action[@name="sql_text"])','nvarchar(max)') AS sql_text FROM ( SELECT n.query('.') AS event FROM ( SELECT CAST(target_data as XML) AS target_data FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE s.name = 'evtsRollback' AND t.target_name = 'ring_buffer' ) AS s CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n) ) AS t; -- Tidy up DROP EVENT SESSION evtsRollback ON SERVER;
The results of the select statement for my simple test case were as follows:
One scenario in which you’d want to run such a diagnostic is when you have thousands of transactions a minute. For the vast majority of the week they all succeed but you encounter few occasions when the front end application deliberately rolls back a transaction, for example, when thresholds for acceptable levels of performance have been exceeded. Without such a light-weight diagnostic tool identifying infrequent rollbacks becomes problematic.