Sql Server Open Transactions

Question

I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. Looking at sys.databases, log reuse desc was active_transaction. No big deal, I figured I could take a look an find the session running the transaction against the database, and figure out next steps. This is a dev database, so I assumed a developer left a transaction open, and I would just need to reach out and have them close the transaction.

Now to the odd stuff. I used DBCC OPENTRAN to find the oldest open transaction in the database, there are two oddities in the output.

1. The session ID is listed as 53s, I have never seen a session id with an letter,
2. The transaction start time is from 11/3/2023, but the server was restarted on 11/6/2023.

I think I can put the database into single user mode with rollback immediate, and then back into multi_user mode, and this will deal with the issue. I am holding off on that for now, because I don't understand what happened, or how and the hell things ended up this way.

Answer 1

The session ID is listed as 53s, I have never seen a session id with an letter

The "s" denotes that this is a system thread/spid. You'll need to check what this spid is doing via the usual means. If it's a system thread then it's most likely doing something system related/internal.

The transaction start time is from 11/3/2023, but the server was restarted on 11/6/2023.

This is highly suspect, I'm not saying you're wrong, I'm just saying it seems quite odd. Since we don't currently know what it's doing it's hard to say anything further.

I think I can put the database into single user mode with rollback immediate, and then back into multi_user mode, and this will deal with the issue.

That may or may not be helpful, again, since we don't know what that spid is doing. We'd need more information.