Sql-server – Production server Log file full due to replication

sql server

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.

Best Answer

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).
Related Question