Always on DB - Log huge and no free space

  • NicHopper

    SSCrazy Eights

    Points: 9034

    Hi,

    So I've got an Always on database that is 1.4TB in size, the Always on is working fine but the log file just keeps on growing, as of now is at 683GB in size with less than .1% free.

    We have regular log backups and I need to find out whats using all this space, I've used DBCC LOGINFO and it shows me the log has 11208 units, all have a status of 2.

    I've checked for open transactions and there are none, any idea's on how I can bring this log file under control?

    Thanks,

    Nic

  • Jacob Wilkins

    One Orange Chip

    Points: 27886

    What is the log_reuse_wait_desc for the database?

    SELECT log_reuse_wait_desc
    FROM sys.databases
    WHERE [name]='your database name';

    Cheers!

  • NicHopper

    SSCrazy Eights

    Points: 9034

    Thanks for that, I was just about to post back to say, nailed it, its the Always on.

    SSMS was showing the db as synchronised but whilst the secondary was sent the data changes it never applied them, it just sat there. Windows was complaining about memory so we restarted the secondary machine and then paused and resumed the always on and its now working its way through 700GB of log changes, this may take a while.

    Your query proves this as the result is AVAILABILITY_REPLICA, a useful one for the future, thanks again.

     

    Nic

  • Jacob Wilkins

    One Orange Chip

    Points: 27886

    No problem. Glad you got it resolved!

     

     

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

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