I have a SQL servers in an availability group, set as synchronous commit.

Due to this setup, log backups only reclaim log space when a backup of the secondary has been processed.

I noticed my log space wasn't been re used on the primary, so I looked in sys.databases and that said log reuse waiting on active transaction. Therefore I ran dbcc opentran which says no open transactions. So I looked on the secondary, dbcc opentran returns a spid.

However I ran dbcc inputbuffer(spid) and that said the spid doesn't exist.

I look on the primary and that spid doesn't exist, in sleeping state and doesn't appear as an opentran. I kill the spid and suddenly it clears off the secondary and log space is reused.

I can someone help me understand why this happens?

  • 1
    I run dbcc opentran which says no open transactions. You have to run that in the context of a database. If sys.databases says its waiting on active transaction .. its true .. there has to be some transaction that is left open. – Kin Dec 6 at 14:49
  • This correct, I was running in the same context for the db in question. – BeginnerDBADan Dec 6 at 14:55
  • You said the spid does not exist - not sleeping or in open tran .. and then you say I kill the spid - what spid did you killed ? Something is not adding up ! – Kin Dec 6 at 15:47
  • Dbcc inputbuffer (spid) returned nothing on the secondary, even though dbcc opentran showed that spid as open. Sp_who2 did not show that spid either on the secondary. On the primary, dbcc opentran in that db context says no open transactions, sp_who2 shows the same spid number that opentran returned on the secondary as sleeping. I kill it on the primary suddenly, it disappears on the secondary and log is reused. It doesn't add up to me at all, that's what made me ask the question to see if anyone had a good reason why this can occur. – BeginnerDBADan Dec 6 at 16:35
  • You run Inputbuffer on primary or secondary? – eckes Dec 7 at 0:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.