-1

Have a 3 node SQL AG. Flip primary form node 1 and 2 every 3 weeks. node 3 is used for Readable secondary for reporting and the such.

The issue is the log file for the database is now over 500GB and no amount of Full backups and transaction log backups frees up the Data space allocated it's like 98% of the log file.

SELECT name, log_reuse_wait_desc FROM sys.databases;

shows:

log_reuse_wait_desc
REPLICATION

How do I get the log files to get down to much smaller size?

The only other thing that seems odd to me is that the AG dashboard shows the seeding mode is manual for the 1st two nodes and automatic for the 3rd.

J. Mini
  • 109
  • 2
  • 15
SeanFromChicago
  • 1
  • select * from sys.dm_tran_database_transactions – SeanFromChicago Mar 11 '24 at 22:44
  • I have 6 results but I have no idea what they mean – SeanFromChicago Mar 11 '24 at 22:44
  • I suspect an old database replication job that used to copy data from an SQL instance to an Azure SQL DB is maybe part of the mystery. That's at least maybe why the log_reuse_wait_desc is REPLICATION. and yes I read the link J.Mini posted the other day.. it was helpful but not enough to find resolution. That's actually why I posted here. – SeanFromChicago Mar 11 '24 at 22:46
  • I am going to figure out how to rip this replication out of the SQL AG: – SeanFromChicago Mar 11 '24 at 22:46
  • To manually remove a replication, you can follow these steps:
    1. Drop all subscriptions that are configured for the replication.
    2. Drop all publications that are configured for the replication.
    3. Drop the distributor that is configured for the replication.

    The system stored procedures for each type of replication are listed in below MS article. Use the appropriate stored procedures, depending on the type of replication that you want to remove. https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/replication/manually-remove-replication

     – SeanFromChicago Mar 11 '24 at 22:50
  • So the space doesn't seem to be growing after turning off the log file agent for the replication setup. So the unchecked growth seems to be stopped. I still need to figure out the process to remove everything from above. Wish there was an easier method. – SeanFromChicago Mar 12 '24 at 02:44

0 Answers0