0

I am desiging a log system for my program and I need to know when any transaction in my system commits or rollsback. Since I open my connection from one place and also begin transactions from one place I have 'BEGIN TRANSACTION' covered but rollback I cannot get.

Mind you that I am in full control of opening and comminting transaction and I could easily log it there but I don't think this is good design since from that point onward programmer would have to worry about logging each commit or rollback!

Besides implementing my own IConnection and IDbTransaction what would be the best solution?

Robert
  • 2,407
  • 1
  • 24
  • 35

1 Answers1

1

Use Extended Events to log everything. The following script logs Rollback but you can add Begin Transaction events or anything else:

CREATE EVENT SESSION [rollback] ON SERVER 
ADD EVENT sqlserver.rollback_tran_completed,
ADD EVENT sqlserver.rollback_tran_starting 
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\rollback.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

Then you can query the results with:

SELECT * FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\rollback.xel', null, null, null)
cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • I will update my answer. I am looking for C# solution. I do not wish to log this on database. – Robert Mar 28 '17 at 08:23