5

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?

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

1 Answer 1

Reset to default
8

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.

Improve this answer
1
  • Perfect! I found that the error was a try/catch set up incorrectly in a trigger. Thank you.
    – SomeGuy
    Commented Nov 21, 2012 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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