Post reply

Unsent Log on database mirror seems stuck at 112KB

  • May 30, 2012 at 8:08 am

    #274722

    Hi,

    We have a 2 node cluster, running Window 2003 R2 SP2 and SQL Server 2005 SP3 Enterprise Edition (x64).

    At 23:15 on 27/05/2012 (GMT), we had a 10 second mirroring timeout. Since then, the 'unsent log' has never gone below 112KB.

    The unsent log goes up quite happily as transactions are processed on the principal, as does the send rate. But, once it appears to go idle again, it doesn't seem to be able to clear this mysterious 112KB that's stuck on the principal.

    Also, when the unsent log is idling at 112KB, the Time To Send value is set to infinite. Prior to the mirroring timeout, the unsent log always went down to 0KB.

    The only error in the SQL Server error log is for the mirroring timeout.

    Any ideas?

    Thanks in advance.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • May 31, 2012 at 3:48 am

    #1495078

    Can nobody help?

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 1, 2012 at 8:00 am

    #1495874

    We are talking about the database transaction log, right? I assume you have been running a transaction log backup and the log doesn't shrink? If your log won't shrink check if there are any open transactions

    DBCC OPENTRAN

    If you can cancel them this may help. If there are none try executing

    DBCC FREEPROCCACHE

    GO

    DBCC DROPCLEANBUFFERS

    go

    DBCC FREESYSTEMCACHE ('ALL')

    GO

    DBCC FREESESSIONCACHE

    GO

    Make sure you are ok with the impact of these statements.

    Then try to shrink the log again. If it still doesn't shrink there are some other code snippets on this site to shrink the active portion of the log. Ty a search on tis site. I have some code as well. If you do this you will likley need to also do another full backup after the shrink.

    Francis

  • June 2, 2012 at 7:43 pm

    #1496238

    Don't shrink the log!!!

    Backups do not shrink the log (unless you have committed the unforgivable sin of enabling auto-shrink)!

    Don't free the various caches either. They are unrelated and will cause a performance hit.

    I think that looked for an open transaction is the right track though. Look for a transaction stuck in rollback or just stuck.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • June 6, 2012 at 3:22 am

    #1497487

    Thanks all for your responses. This has now been resolved.

    I stopped both endpoints (principle and mirror) and restarted and the unsent log now goes down to 0KB.

    The article I followed can be seen here.

    Thanks again.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply