Always on DB - Log huge and no free space

  • 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?



  • What is the log_reuse_wait_desc for the database?

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


  • 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.



  • No problem. Glad you got it resolved!



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

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