Post reply

shrinking files in an availability group

  • March 16, 2020 at 11:26 am

    #3734657

    Hi guys

    this is just a test database, so no urgency

    I have a database that has about 8Mb (yes a tiny database)  but the log was allowed to grow to 74GB (with 99% free space) - there are no open transactions , it got restored to our new 2019 server and put into an availability group

    so, I've run a checkpoint on the database, a dbcc opentran()  and then a shrinkfile on the log . the log won't shrink

    normally I can fix this by swapping to simple recovery model and then fixing it,  - put it back into full and start a backup (the backup is just for DR)

    But being in an AG I cannot change the recovery mode.

    any hints as to how to get that stupid log to shrink are more than welcome

     

    MVDBA

  • March 16, 2020 at 12:46 pm

    #3734689

    Whats the status of log_wait_reuse_desc in sys.databases?

     

    May be a case that you need to run a few log backups to get it to be able to checkpoint and shrink.

  • March 16, 2020 at 12:47 pm

    #3734690

     

    Mike, what is log_reuse_wait_desc  for this database in sys.databases?

     

    select name, log_reuse_wait_desc from sys.databases where name=N'yoursmalldb'

     

  • March 16, 2020 at 12:51 pm

    #3734692

    Ant-Green wrote:

    May be a case that you need to run a few log backups to get it to be able to checkpoint and shrink.

    it could be replication also ...

    if it is, then  the proc  sp_removedbreplication  should help

     

     

  • March 16, 2020 at 1:39 pm

    #3734723

    Ant-Green wrote:

    Whats the status of log_wait_reuse_desc in sys.databases?

    May be a case that you need to run a few log backups to get it to be able to checkpoint and shrink.

    the log_wait_resuse_desc is saying LOG_BACKUP

     

    MVDBA

  • March 16, 2020 at 1:46 pm

    #3734725

    fixed it perfectly

    thank you.

    even though the database had come from a full backup it did not want to shrink until it had a t log backup of an empty log file.

    good shout on that one

    MVDBA

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

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