shrinking files in an availability group

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  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

• Ant-Green

  SSC Guru

  Points: 113536

  More actions

  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.

• Andrey

  SSC Eights!

  Points: 840

  More actions

  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'

   

• Andrey

  SSC Eights!

  Points: 840

  More actions

  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

   

   

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  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

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  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