Post reply

Always On log file Full

  • October 24, 2016 at 9:28 am

    #331282

    Hi All,

    We have our environment configured with Always On on 2 node with synchronous commit. Recently we had log fill full issue and the primary did not respond. We have a separate mountpoint for all the ldf's of the instance and we noticed that the mountpoint has maxed due to one ldf file growing very large. We have resolved the issue, however we trying to find out what caused log file to grow huge. So far did not have any luck.

    Looking for experts guidance and suggestions.

    Thanks,

  • October 24, 2016 at 3:16 pm

    #1908486

    Are you taking full and transaction log backups on a regular basis?

  • October 24, 2016 at 11:01 pm

    #1908505

    Yes we do.

  • October 25, 2016 at 2:12 am

    #1908519

    The main question is: what were the actions executed on the database during the growth of the LOG file? Active transactions prevent a log-backup from releasing space inside the LOG file before the point where the transaction was started.

    So if you have a long running query (like BEGIN TRAN without COMMIT or ROLLBACK) that is still active a LOG backup will not release the space inside the log. The LOG will continue to grow to also log the actions of all other active transactions. A log will also grow when there is a transaction that modifies a large amount of data (like a massive DELETE/UPDATE, but also INDEX maintenance).

    If I recall correctly a LOG (on the primary) will also not release the space if the secondary node in an AlwaysOn configuration is not synchronized.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 25, 2016 at 4:53 am

    #1908550

    I suspect your last point to be the reason. Because we found that the replica was not synchronizing at the time of failure and hence log file might have become huge. But is there a way we can find out why the replica went out of sync ?

  • October 25, 2016 at 5:23 am

    #1908555

    harikumar.mindi (10/25/2016)

    But is there a way we can find out why the replica went out of sync ?

    Out of the blue it is impossible to tell. Start by looking in the SQL Error log and the Windows Event log (both on secondary and primary node) for clues...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

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