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.

Lately I've been seeing errors in the SQL Server Log.

Code:3930 Description:"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

Using SQL Profiler, how can I find the exact statement that is causing this error? Which column in SQL Profiler fills the "Message" column in the SQL Error log? Unfortunately, we are only getting this in production, so I will need to run a trace there with the lightest weight trace possible. What do you folks recommend to capture the offending code?

share|improve this question
2  
What version of SQL Server? The lightest weight trace possible would probably not be a trace at all but extended events. –  Martin Smith Nov 16 '12 at 17:26
    
This server is on SQL Server 2005. –  SomeGuy Nov 16 '12 at 17:41

1 Answer 1

up vote 4 down vote accepted

In Profiler, click "show all events" and go to the Errors and Warnings listing. You should be able to check Exception and User Error Message. The Exception class will show you the actual error and User Error Message will show you the message displayed (e.g., "Incorrect syntax near ..." or whatnot).

You mention a message going into the error log; there's also an ErrorLog event in the Error Message category, so it wouldn't hurt to include that as well.

You would need to include the SQL:BatchStarting and possibly SQL:BatchCompleted as well to get the command being run at that time.

You might also look at Remus Rusanu's answer to an earlier question of this nature to see if that might help solve the problem.

share|improve this answer
    
Perfect! I found that the error was a try/catch set up incorrectly in a trigger. Thank you. –  SomeGuy Nov 21 '12 at 13:33

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.