Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there anywhere I can look to find failed transactions in SQL server 2012?

share|improve this question

3 Answers 3

No, SQL Server does not maintain any history about transactions that were aborted / rolled back that is trivial to get to and doesn't introduce additional potential problems (as outlined in @ooutwire's answer). Or even transactions that have been committed.

You will have to perform your own logging within your error handling, or capture specific transaction-related events using server-side trace or Extended Events.

Trace:

enter image description here

Extended events:

enter image description here

share|improve this answer
    
Sure it does, they are in the log file and log backup files. –  ooutwire Jun 19 '13 at 14:31
    
@ooutwire and how do you get to those easily? And how do you get to them if they are no longer in the log? Those log records are transient at best. –  Aaron Bertrand Jun 19 '13 at 14:37
    
See my answer. The best way is to create frequent log backups. I would agree that such a solution is not ideal; but I see no reason why I would be searching all the time in prod for aborted transactions. If that is the desire, than a trace or XEvent would seem the prudent solution. –  ooutwire Jun 19 '13 at 14:40
2  
I saw your answer, of course. I said easily and also should have said reliably. :-) –  Aaron Bertrand Jun 19 '13 at 14:41
5  
I'm with @AaronBertrand on this one. With the trouble it'll take to comb through transaction logs (Note: not what they are intended for) you might as well just create a lightweight XE session for this troubleshooting. –  Thomas Stringer Jun 19 '13 at 14:45

When you say "failed" transactions, what exactly do you mean?

If you want to see current transactions on the instance, you can utilize the sys.dm_tran_active_transactions DMV.

Also, sys.dm_exec_sessions has the open_transaction_count that can give you this information by session. Below is a diagnostic query to pull all user processes that have open transactions:

select 
    s.session_id,
    s.login_name,
    s.open_transaction_count,
    st.text as most_recent_sql_text
from sys.dm_exec_sessions s
inner join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
where s.is_user_process = 1
and s.open_transaction_count > 0;

This information can also be pulled from sys.dm_tran_session_transactions:

select
    session_id,
    is_user_transaction,
    open_transaction_count
from sys.dm_tran_session_transactions;

If you want to capture when transactions were rolled back (assuming so much by your desire for "failed" transactions) you can capture the Extended Events rollback_tran_completed event. If you're looking for an "everything" view of transactions, you can capture the sql_transaction event, which as defined by SQL Server is

Occurs when a SQL Server transaction begins, completes, rolls back or executes a savepoint. Use this event to monitor transaction behavior when troubleshooting applications, triggers or stored procedures.

share|improve this answer

You can use fn_dblog() and find the transaction IDs for aborted transactions as well as a host of other useful information.

SELECT * 
FROM fn_dblog (NULL, NULL)
WHERE Operation = 'LOP_ABORT_XACT';
GO

It scans all transaction log in the active portion of the log. This can be over-ridden using trace flag 2537, which will allow you to go back as far as possible to the start of the oldest "un-reused" VLF. Be careful when using this function, since it scans the log randomly and the log cannot change when the scan is happening; so, you may see log growth.

You can also use fn_dump_dblog against a log backup file.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.