Is there anywhere I can look to find failed transactions in SQL server 2012?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
When you say "failed" transactions, what exactly do you mean?
If you want to see current transactions on the instance, you can utilize the
This information can also be pulled from
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
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.
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.