Count number of rollbacks in SQL Sever 2008

Question

I am experimenting with the SET-OPTION XACT_ABORT ON as I am seeing a lot of sleeping sessions holding an open transaction causing problems in the application.

Is there a way to measure if flipping the option has an effect or not? I am thinking about something like number of rollbacks per day. How could I collect these?

I am on SQL Server 2008 SP4.

Answer 1

Considering your database is in Full Recovery Model.You can combine all the transaction log backups taken per day and Query it...

SELECT 
CASE 
WHEN OPERATION='LOP_ABORT_XACT' THEN 'COMMITS'
ELSE 'ROLLBACKS' END AS 'OPERATIONS'
,COUNT(*) AS CNT
FROM FN_DBLOG(NULL,NULL) WHERE OPERATION IN ('LOP_COMMIT_XACT','LOP_ABORT_XACT')
GROUP BY OPERATION

I did some tests using some sample data..

Begin tran test1
insert into t1
select 3
rollback

Output:

Operations  cnt
Commits     3
RollBacks   4

Update as per comments:
Reading Transaction log Backup can be expensive ,I recommend you do this on backups taken and not on Active log ..doing this on active Tlog can have below effects

1.since this does a Log Scan,Transaction log truncation will be prevented
2.Huge IO load on server depending on Your log backup Size,since the output of ::fn_dblog can easily go into millions of rows

References:

http://rusanu.com/2014/03/10/how-to-read-and-interpret-the-sql-server-log/

https://blogs.msdn.microsoft.com/dfurman/2009/11/05/reading-database-transaction-log-with-fn_dump_dblog/