Replication subscriber log continuously growing

Question

Replication subscriber log continuously growing

icester

Ten Centuries

Points: 1093
More actions
August 10, 2017 at 11:47 am

#338682

Hello SSC,
I have a replication process running which is having problems with the Subscriber's log file (continues to grow non-stop).
Setup/notes:
- Data does successfully flow from Publisher --> Distributor --> Subscriber
- The publisher transaction log is truncating as expected
- The subscriber's database is in Simple recovery mode
- Publisher (SQL Server 2008) --> Distribution (SQL Server 2014) --> Subscriber (SQL Server 2014)
- This setup, with the same instance/database, has worked in the past, it's just after our most recent rebuild of the Replication that this is happening
- I ran "dbcc opentran" from the Subscriber but it reported that there were no open transactions
Any ideas what might be causing the subscriber's transaction log to be growing?
Thanks for the help!
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

icester Ten Centuries Points: 1093 More actions · Answer 1

I might have the solution but I'm going to wait until tomorrow to see if it fully works. I'll post my findings here afterward.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

irk - Thursday, August 10, 2017 2:00 PM
I might have the solution but I'm going to wait until tomorrow to see if it fully works. I'll post my findings here afterward.

Please post back - I'd be interested in knowing what you found/did.
Thanks

Sue

icester Ten Centuries Points: 1093 More actions · Answer 3

The solution was to, in short, back up the Subscriber database. Since our log file was so large, and since we don't want to keep the backup of it, the trick was to do a backup to NUL.

We made a 6 step job that we can manually run if ever needed.
Step 1) Set database to SIMPLE recovery mode
Step 2) Set database to FULL recovery mode
Step 3) Back up database to NUL
Step 4) Back up log to NUL
Step 5) Shrink log file
Step 6) Set database back to SIMPLE recovery mode

Step 1)
ALTER DATABASE [database name] SET RECOVERY SIMPLE;
Step 2)
ALTER DATABASE [database name] SET RECOVERY FULL;
Step 3)
BACKUP DATABASE [database name] TO DISK='nul';
Step 4)
BACKUP LOG [database name] TO DISK='nul';
Step 5)
DBCC SHRINKFILE (N'[database log file name]', 2048);
Step 6)
ALTER DATABASE [database name] SET RECOVERY SIMPLE;

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

To me, your solution sounds more like a workaround to me. Or did running that once fix the log growth issue?

I'm just thinking that to me, your solution sounds like it is treating the symtpoms of the problem (large log), not actually solving it, but I could be wrong. I have just seen where systems get set up that treat the symptoms and everyone is happy until the new guy comes in, thinks that the band-aid fix is silly and turns it off only to kill the system and then the whole DBA team gets to spend the next bit finding a proper solution.

Now, if that is a permanent fix and you only need to run the scripts once, then that is a pretty cool fix. Although I am uncertain as to why it is fixed from that. Was it set to "FULL" recovery initially? if so, that would explain the giant log...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

icester Ten Centuries Points: 1093 More actions · Answer 5

This does resolve the problem, the log file is no longer growing. The database was initially set to the SIMPLE recovery mode.

Dan

Sue_H SSC Guru Points: 90860 More actions · Answer 6

irk - Monday, August 14, 2017 11:30 AM
This does resolve the problem, the log file is no longer growing. The database was initially set to the SIMPLE recovery mode.
Dan

Thanks for posting back. I think that if you would have done a manual checkpoint the results would likely be the same.
Backing up the log in full or bulked logged truncates the transaction log. Checkpoint in simple recovery truncates the transaction log. And of course truncate doesn't mean shrink.

Sue