Identifying rolled back transactions

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)[1]','varchar(50)') AS event,
DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)[1]','datetime2')) AS [timestamp],
event.value('(event/action[@name="session_id"])[1]','int') AS session_id,
event.value('(event/action[@name="database_id"])[1]','int') AS database_id,
event.value('(event/data[@name="duration"])[1]','bigint') AS duration_microseconds,
event.value('(event/data[@name="transaction_id"])[1]','bigint') AS transaction_id,
event.value('(event/data[@name="transaction_state"]/text)[1]','nvarchar(max)') AS transaction_state,
event.value('(event/data[@name="transaction_type"]/text)[1]','nvarchar(max)') AS transaction_type,
event.value('(event/action[@name="sql_text"])[1]','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.

 

 

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: