0

Shrinking my database log file does not work. After 2 and half week of search i finally ended up on the following query :

SELECT name, log_reuse_wait_desc FROM sys.databases

My specific database does show REPLICATION as the cause. How does someone shrink the LDF to reasonable size ?

What i have :

  • MDF ~13 GB
  • LDF ~1.8 TB (non sense)
  • Full recovery mode
  • Full backup every morning 5 am
  • Transaction backup every 15 min

I have 1 replication for 2 table of 0.24 mb and 1 of 0.14 mb so not even 500kb total. Transaction every 15 min total size over a day average 1 gb so i do not see any reason for log to be bigger than DB size in that case.

Shrink file doesn't work and i am getting close to major problem that my 2 days backup with current log uses 5.9 TB out of 6 TB for a 13 GB actual DB size.

2
  • Are you using Replication or Change Data Capture (CDC)?
    – AMtwo
    Commented Nov 1, 2022 at 22:59
  • @AMtwo I do not know what Change Data Capture is. All i know is i made a simple Replication and it shows in the Replication tree in SSMS and i have the subscription that shows my 2 tables being replicated on one of my other SQL servers into a specific database on that other server
    – Franck
    Commented Nov 2, 2022 at 12:23

1 Answer 1

0

We had to assume MSSQL log file are a broken system at this point and had to act before loosing anything. No help from Microsoft support could figure this out. Our current working solution was switching the recovery model to simple and change the backup method.

From : Full recovery with 1 full backup per day and 1 transaction backup every 15 minutes

To : Simple recovery with 1 full backup every 15 minutes.

We went from 5.9 tb used to 2.88 tb constant. In the futur we plan to get rid if that replication that seems to be what's broken in MSSQL and simply run a service that pull the data from one server and insert into the other. This should reduce the storage used to about 40 gb total.

A gain of 14470 % space

5
  • Something is really wrong with your database. No 13 GB Data file should have a correlating Log file anywhere near 1 TB+. I would be inclined to suggest creating a new database, and using a migration tool to copy the old database over. Does restoring a backup of your current database result in a huge Log file still?
    – J.D.
    Commented Nov 11, 2022 at 0:28
  • Restoring result in the same issue. The only thing that fixed the issue is deleting the replication. the made the log stops growing and we were able to shrunk the log to 1.2 gb which is much more realistic. So we know the issue is tied to replication but we need it. Right now i have a second version running on another server with a newly made replication running to a fourth server and we are experiencing a crazy growth in size. LDF grow by about 250 mb per day despite having no transaction done on that database. So we know for sure that replication is a broken tool at least in our version.
    – Franck
    Commented Nov 11, 2022 at 12:07
  • That's very weird. I wonder if it's something particular to your data. Do you have any unusual data types like BLOB, IMAGE, TEXT, etc, that are being replicated? When you initialize a new snapshot, how big are the actual replication files it generates on disk (in the Repl folder)? Are you replicating anything besides Tables, such as Indexed Views?
    – J.D.
    Commented Nov 11, 2022 at 13:39
  • It all simple datatype like couple of varchar (below 200), bit, int, datetime and float. The only things checked in the replication is the tables. That's all that we can see that is checked. The files are about 200kb each. Anyhow we will probably code our own replication tool that will simply delete from target server all records and insert from the source and it will do the same process and will be working 100% without that stupid overhead.
    – Franck
    Commented Nov 11, 2022 at 14:07
  • I mean I've been using Replication for a decade and never have I encountered whatever weird issue you're facing. Something is definitely uniquely wrong with either your data, database, server, or a very weird bug in the specific version of your SQL Server instance. You're using Transactional Replication, I assume? Are you on the latest patch of SQL Server 2016? It seems like you have a tiny amount of data you're trying to replicate, so I guess you could just as easily roll your own synchronization method. But your issue is surely unique.
    – J.D.
    Commented Nov 11, 2022 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.