February 1, 2016 at 3:09 am
Running SELECT name, log_reuse_wait_desc FROM sys.databases shows that Replication is the cause of the log file not shrinking on the Publisher.
When I run DBCC OPENTRAN it returns:
Transaction information for database 'XXXXXXXX'.
Replicated Transaction Information:
Oldest distributed LSN : (3329:40558:20)
Oldest non-distributed LSN : (3329:43565:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Replication monitor does not report any issues with any of the Publications.
No results are returned when I run sp_repltrans.
Also running sp_replcmds returns no results
This seems to suggest to me that there are expired transactions in the log. I have been reading about sp_repldone. The problem is one article I read said you won't be able to restart the log reader until you re-initialise the subscriptions, however this article was written for SQL 2000, we are replicating from 2012 to 2008. Our database is 85GB in size and is replicated over a 10Mbit connection, so you can see my issue.
If I run the following will I have to re-initialise? Other articles I have read don't mention it.
EXEC sp_repldone @xactid = NULL,
@xact_segno = NULL, @numtrans = 0,
@time= 0, @reset = 1
A utility spreadsheet I set up shows the row counts between Publisher and Subscriber are spot on.
Thanks
Tim.
February 2, 2016 at 2:42 pm
If you run sp_repldone, you will mark all the transactions in the transaction log as replicated, and logreader will now ignore them. So, if there have been any transactions that needed to be replicated, then those would not get to the subscriber. So, my recommendation is that you should proceed cautiously unless you have a mechanism to sync the pub and subscriber following the execution of that command.
Ultimately it is better to figure out what transaction is holding up the log from shrinking. Have you inserted a tracer token to see if the logreader is actually pushing data to the distributor? Have you looked at the logreader agent in replication monitor to get the status of that?
Let me know what you find from the tracer token, and from the logreader details in replication monitor.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 3, 2016 at 2:47 am
David Benoit (2/2/2016)
If you run sp_repldone, you will mark all the transactions in the transaction log as replicated, and logreader will now ignore them. So, if there have been any transactions that needed to be replicated, then those would not get to the subscriber. So, my recommendation is that you should proceed cautiously unless you have a mechanism to sync the pub and subscriber following the execution of that command.Ultimately it is better to figure out what transaction is holding up the log from shrinking. Have you inserted a tracer token to see if the logreader is actually pushing data to the distributor? Have you looked at the logreader agent in replication monitor to get the status of that?
Let me know what you find from the tracer token, and from the logreader details in replication monitor.
The transactions are replicating fine and the Log Reader Agent is running. I just need to have confidence that if I run sp_repldone that it will not force me to reinitialise, as one article I read said would happen.
February 3, 2016 at 7:27 am
It does not force you to initialize, but it does invalidate any unreplicated transactions in the transaction log which will potentially put you in an out of sync condition.
In order to run sp_repldone you have to stop logreader, connect to the database in question in SSMS, execute the command, disconnect from that SSMS session, and then start logreader again.
I would recommend getting as much information about the transaction, and checking to see if there is some other way to troubleshoot what that is before doing the sp_repldone. Me being cautious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply