search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.17k stars 289 forks source link

The current transaction cannot be committed and cannot support operations that write to the log file. #39

Closed p-doyle closed 3 years ago

p-doyle commented 3 years ago

I'm executing the store procedure periodically and writing it to a table in tempdb using:

EXEC tempdb.dbo.sp_WhoIsActive @get_plans = 1, @get_task_info = 2, @get_locks = 1, 
    @get_full_inner_text = 1, @get_transaction_info = 1, @format_output = 0, 
    @DESTINATION_TABLE = 'tempdb.dbo.whoisactive_table'

I've recently started getting this error about once an hour: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

A google search seems to indicate I should add a try/catch around the sp and then rollback if there is an error... is that something I should be expected to do when executing this? Or is there something else that might be going on?

For reference I am on SQL Server 2017 Standard Edition14.0.3281.6 (X64) on AWS RDS.

amachanic commented 3 years ago

There are a few TRY/CATCH blocks in the stored procedure, but nothing that's expected to doom your transaction like that. Are you getting a line number?

p-doyle commented 3 years ago

I'm executing this from python and this is the full error:

('42000', '[42000] [FreeTDS][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (3930) (SQLExecDirectW)')

Not sure if 3930 is the line number?

amachanic commented 3 years ago

3930 is the error number. Seems that FreeTDS is not returning all of the information. Are you able to run an Extended Events session and capture some more information? Here's a decent looking guide that just came up when I googled: https://www.sqlservercentral.com/blogs/capture-sql-server-reported-errors-using-extended-events

amachanic commented 3 years ago

Just to make sure, you don't have an external transaction of some sort, do you?

amachanic commented 3 years ago

And on that last point -- do you have autocommit enabled on your connection from Python? (If you don't, you should, at least for this case. I like it for pretty much everything, though.)

p-doyle commented 3 years ago

Ah that event session thing looks cool... unfortunately it does not seem to be available on RDS as far as I can tell :(

Not exactly sure what you mean by external transaction but I don't think so.

It wasn't. I enabled it and haven't gotten any errors in the last hour so hopefully that fixed it.

Thanks for the help!