"Oldest unsent transaction" is climbing while "Unsent log" is zero.

Question

I'm seeing "Oldest unsent transaction" climbing for several hours while "Unsent log" is zero. Here is screenshot. Backup log on principal doesn't work - seems there is something unsent but if unsent log is zero - how can it be? I've found description of similar situation on this forum but without an answer - the issue had been resolved by itself somehow. Did anybody see similar situation? Can it be resolved without dropping mirroring?

I had to drop and re-initialize mirroring in order to resolve the issue - there was no space left for transaction log on principal (so probably I won't be able to answer requests to check something as in real time situation). Also I didn't try fn_dblog to try and find something because transaction log climbed to 160GB at the time I've seen it first time - the system is _very_ active.

SQL Server 2008 SP2 (10.0.4000.0), 64 bit, Enterprise Edition

Answer 1

ERRORLOG has something interesting?

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 2

Nothing at all. It was one of the first things I've checked. Something has been stuck between servers several of hours before (reason for unrestored log on the mirror), so one of the DBAs restarted SQL Server services on both instances ~2 hours before I started to look at the system. Unrestored log on mirror has been slowly reducing after it, so system has been working. But "Oldest unsent transaction" continued to climb.

Answer 3

This problem particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

Most of times - when connection re-established then problem will be resolved automatically.

 

similar problem we have couple of month back... resolved after drop and re-initialise mirroring. You might shrink the log file(if it is very large) before reconfigure it again, on production this should be done out of hours. 

one way to shrink log file would be after break the mirroring on principal change recovery model to simple and shrink the log file.....

 

---

http://uk.linkedin.com/in/ramjaddu

Answer 4

Nothing at all. It was one of the first things I've checked. Something has been stuck between servers several of hours before (reason for unrestored log on the mirror), so one of the DBAs restarted SQL Server services on both instances ~2 hours before I started to look at the system. Unrestored log on mirror has been slowly reducing after it, so system has been working. But "Oldest unsent transaction" continued to climb.

Can you try

ALTER DATABASE SET PARTNER SUSPEND
go
WAITFOR DELAY '00:05'
go
ALTER DATABASE SET PARTNER RESUME
go

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 5

As I've said, it isn't real-time situation; we've already dropped and re-initialized mirroring, now everything is working fine. But pausing mirroring actually changes mode from high safety to high performance - and it has been done anyway but didn't help.

Answer 6

Ah, so I'm not the only one who had this problem. Actually the system was in high safety mode; one of the DBAs changed it to high performance in order to make sure that we at least don't loose transactions on principal server. It didn't help to resolve the issue though.

Don't you think that it is bug? Probably I'll post it on Connect. The problem is - it is unreproducible. I'll be able only to provide links to my question, your answer and to similar situation I mentioned in initial question - but not to steps to reproduce the problem.

Answer 7

Ah, so I'm not the only one who had this problem. Actually the system was in high safety mode; one of the DBAs changed it to high performance in order to make sure that we at least don't loose transactions on principal server. It didn't help to resolve the issue though.

Don't you think that it is bug? Probably I'll post it on Connect. The problem is - it is unreproducible. I'll be able only to provide links to my question, your answer and to similar situation I mentioned in initial question - but not to steps to reproduce the problem.

Sure. Please go ahead and file connect item for tracking.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq