Database with replication and mirroring stuck in LOG_BACKUP after log file shrink

by andrews   Last Updated September 19, 2017 15:06 PM

Need an advice from SQL Server Experts. I'm just a developer, not a DBA, so pardon my ignorance.

My environment consists of 2 equal instances (Node1 and Node2) of SQL Server 2008 R2 x64 (10.50.4339.0). I have 2 databases: DB1 and DB2 with Full recovery model. Both databases participate in replication between each other within single SQL instance and also both of them get mirrored from Node1 to Node2. These are not production databases and I did take full DB backups before doing any changes.

However, I have ended up in trouble: the log file is not growing for both DB1 and DB2 at Principal and the execution of:

select log_reuse_wait_desc, name, * from sys.databases where name='DB1' or name='DB2'

shows that both DB1 and DB2 have `log_reuse_wait_desc='LOG_BACKUP' even though I did run log backup manually.

Here is the sequence of events which caused the current state:

  1. I needed to update DB1 and DB2 with year-worth set of changes which involved running many structure and data change scripts. Some of the data scripts were quite heavy, truncating tables and containing 70K+ INSERT calls. So, I've started applying changes at Principal (Node1) one by one in little chunks.
  2. Node1 already had limited disk space available at partition with DB1 and DB2 data and log files.
  3. When applying changes several times I've received log file full... message. So I've stopped and shrunk the log file of both DB1 and DB2 to 0Mb to free up some disk space to be able to finish the data/structure update task.
  4. The log file of both DBs did get shrunk to minimal allowed however after I have continued with data changes I've got the following message:

Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'DB2' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Sometime during running of data/structure change scripts the free disk space at relevant partition dropped to a few KBs and SQL Server sent out warning emails.

I did try to research how to get log_reuse_wait_desc column to show NOTHING. However, the suggested solutions prompted putting DB1 and DB2 into simple recovery mode or detach/re-attach them, something I cannot do because of the replication and mirroring.

I have also followed the advice from Paul S. Randal and have run log backup manually:

 BACKUP LOG DB2 TO DISK = 'z:\DB2.TRN'

which completed successfully, however the log_reuse_wait_desc still shows LOG_BACKUP and the log file is not growing.

Is there any way to fix the current DB1 and DB2 without disabling replication/mirroring followed by a restore from backup?

Any help is greatly appreciated.



Related Questions



Mirroring and Replication

Updated November 14, 2017 08:06 AM


SQL Server replication for off site copy

Updated March 28, 2018 11:06 AM

Having DB Mirroring and Replication together

Updated May 13, 2015 12:40 PM