0

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.

1
  • It would be a safer option, IMHO, to restart the server, without doing any other actions first. Some checks are done when going down,and/or on starting, so this might solve it already.
    – Luuk
    Nov 7, 2023 at 17:10

1 Answer 1

3

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.

2
  • "S" being system makes sense, I hadn't seen that previously. I checked the sql logs, and the database is still being recovered from the startup. I believe this is why the transaction start time predates the server start time, because its rolling a transaction forward. I have to look and see why recovery is taking so long, but thats a different issue. I should have checked the logs first, vs just looking at active transaction information.... Time for more coffee.
    – Patrick
    Nov 7, 2023 at 17:28
  • O, you are using, the weird, dateformat with month at the start..... 🤔😢
    – Luuk
    Nov 7, 2023 at 17:35

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.