3

I'm getting the error below, log file full due to replication
I have hot dbcc sqlperf(logspace) it is showing 100 and it is in Simple recovery model.
I am not able to shrink it.
I am not able to add space.
I am change the recovery model to Full.

Error: The transaction log for database XXXXX is full due to 'REPLICATION'.

I have tried all the possible ways please help, in this regards.

Improve this question

2 Answers 2

Reset to default
7

Per the error message, it is holding the data in the log because your database is configured to replicate those changes (via transactional replication) to another database, and for some reason the log reader job hasn't run recently or is experiencing an error.

If you don't know anything about replication, don't touch it, and talk to whoever in your company set this up.

If that person is supposed to be you, I can't teach a whole replication course in a SE comment, but in general your solution will fall into one of the following categories:

  • Fix replication (and let it run). Enable and run the "log reader" replication job, this should be set up to run continuously. This may take a while to clear while it works through all the records, but is the "correct" method. Hopefully the only problem is that the log reader job needs restarted; if it doesn't work or errors out, you have a bigger problem.
  • Re-initialize replication (this will clear the log immediately, but you will need to push a full snapshot to get replication working again). This is a quick fix to get your publisher database working again, but will leave your subscriber databases orphaned (static) until you push a new full snapshot. This might break your reporting server or wherever you are replicating to, and pushing a new snapshot could take a very long time, depending on the size of your database. Don't do this if you don't know what you're doing, or if you don't understand the business impact (and get approval from the stakeholders in the business).
  • Remove replication. If you are positive that replication shouldn't be set up on this box, then remove replication altogether and the log will clear. Again, don't do this if you don't know what you're doing, or if you don't know what server you are replicating to, or if you don't understand the business impact (and get approval from the stakeholders in the business).
Improve this answer
2
  • 2
    I had the same error message "Error: The transaction log for database XXXXX is full due to 'REPLICATION'." When trying to remove the replication, create backup or change the recovery model. Had to create a new logfile first, then I could remove the replication.
    – Andreas
    Aug 9, 2021 at 20:36
  • @Andreas yep, if the log is full, you can't do much with a database. If you can't expand the size of your disk (like you can in many virtualized environments) then adding a second logfile on another disk might be your only option.
    – BradC
    Aug 9, 2021 at 21:33
1

Another way (temoprary solution, not fix) to get database working is to add another log file.

Add more transaction log files. As a general strategy for managing the size of the log, this is not good. Extra log files have zero effect on performance (the myth that SQL Server writes in parallel to the log files is just that – a myth) and make management more tricky. However, if your log fills up for some out-of-the-ordinary reason, then adding another log file may be the only way to allow the database to keep running. If you have to do this though, make sure that you remove the extra log file(s) when you can to keep log management uncomplicated. source

Improve this answer
4

Your Answer

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

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