Post reply

Always on DB - Log huge and no free space

  • March 26, 2020 at 3:42 pm

    Go to Answer

    #3737221

    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

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 26, 2020 at 4:34 pm

    Answer

    #3737710

    What is the log_reuse_wait_desc for the database?

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

    Cheers!

  • March 26, 2020 at 4:40 pm

    #3737832

    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

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 26, 2020 at 4:46 pm

    #3737835

    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