July 25, 2012 at 2:42 am
Thanks for sharing your thoughts Basit!
However, I would say I am not impressed with the solution provided to shrink the log file. As the article stats to gracefully shrink the log file, I think this is not the best solution. I would have liked much to see some troubleshooting tips on the cause of the issue (log reader issue).
As per the article we need to follow these steps:
1. Take the full backup if we need restore later
2. Run the script to stop the replication
3. Backup log with truncate_only
4. Flush the article cache
5. Truncate the table msrepl_commands
6. Reinitialize all subscriptions with generating a new snapshot
From step 3 to step 6 are all the steps that I would not like to do as a good solution. Reinitializing all the subscriptions with generating a new snapshot is somehow equivalent to configure the whole replication again. If someone has a database with size 5-10 GB then this may be feasible. But when you have table size in 40-50 GB this is going to take much more time. Also, even if once I invested my time to do this reinitializing, I would not be sure whether the same problem will not arise again.
So, I would rather analyze the issue with log reader because may be that is less time taking than the steps given in the article.
July 25, 2012 at 2:43 am
Thanks Phil, Your comments taken.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJuly 25, 2012 at 2:59 am
Divine Flame (7/25/2012)
Thanks for sharing your thoughts Basit!However, I would say I am not impressed with the solution provided to shrink the log file. As the article stats to gracefully shrink the log file, I think this is not the best solution. I would have liked much to see some troubleshooting tips on the cause of the issue (log reader issue).
As per the article we need to follow these steps:
1. Take the full backup if we need restore later
2. Run the script to stop the replication
3. Backup log with truncate_only
4. Flush the article cache
5. Truncate the table msrepl_commands
6. Reinitialize all subscriptions with generating a new snapshot
From step 3 to step 6 are all the steps that I would not like to do as a good solution. Reinitializing all the subscriptions with generating a new snapshot is somehow equivalent to configure the whole replication again. If someone has a database with size 5-10 GB then this may be feasible. But when you have table size in 40-50 GB this is going to take much more time. Also, even if once I invested my time to do this reinitializing, I would not be sure whether the same problem will not arise again.
So, I would rather analyze the issue with log reader because may be that is less time taking than the steps given in the article.
As I said, I did analyze the issue with log reader. If you actually read my forum posts, I did restarted the log reader job few times. I could not find any errors in error log or replication monitor on why this is happening. We even rebooted the server but it still not reading the transactions. I agree, reinitializing the replication with a snapshot is not a good choice for large database publication but this option is the right one in our case as the there appears to be no other way to fix the issue. I could not find anything in BOL or third party websites regarding why Logreader is not reading these transactions.
I also did some post resolusion testing on my test instances from the backups I have taken prior to perform this process. But every thing appears to work in Test.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJuly 25, 2012 at 7:36 am
Basit, First of all, You dont need to do suggest following things in your article.
1. truncate ms_replcmds table
2. stop & start replication jobs
You can just run below commands to re-initialize the replication.
1. run sp_repldone command
2. run sp_replflush
3. truncate transaction log - optional. But if you need the space immediately you can perform this.
4. Reinitialize replication through replication monitor
About the issue that you are facing, log reader is not replicating commands, try with which account these jobs are running and make sure that those accounts are not locked and you have given latest password of those accounts.
-Akbar
July 25, 2012 at 8:46 am
Well, I disagree with what you are suggesting (see below for my responses):
akbar401 (7/25/2012)
Basit, First of all, You dont need to do suggest following things in your article.1. truncate ms_replcmds table
2. stop & start replication jobs
FYI - You will get the following error, if you execute sp_repldone without stopping replication jobs:
Msg 18752, Level 16, State 1, Procedure sp_repldone, Line 1
Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.
Also you need to ensure that ms_replcmds table is empty before reinitializing the replication with snapshot otherwise you will hit consistency errors.
akbar401 (7/25/2012)
You can just run below commands to re-initialize the replication.1. run sp_repldone command
2. run sp_replflush
3. truncate transaction log - optional. But if you need the space immediately you can perform this.
4. Reinitialize replication through replication monitor
About the issue that you are facing, log reader is not replicating commands, try with which account these jobs are running and make sure that those accounts are not locked and you have given latest password of those accounts.
The sevice accounts are not locked and the services are using correct passwords because its started working after reinitialization.
I hope you understand the topic and problem well now.
😀
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJuly 26, 2012 at 3:12 pm
As mentioned in previous posts the methods described in the article are quite drastic and the article IMHO should caution readers and describe possible unintended side affects. "Resetting" replication can cause big downtime on the subscriber for large publications while they are reinitialized.
There is no reason to run the BACKUP LOG WITH TRUNCATE ONLY (for instances prior to SQL 2008). If the database is in full or bulk recovery models then run a normal BACKUP LOG command. If it is in simple then manually run a CHECKPOINT.
I question the need to truncate the MSrepl_commands table. How does this help truncate the transaction log of the published database? Won't the clean-up job take care of this? Won't this cause publications from other databases or databases on other instances that are using the same distribution database to become out of synch if there are pending replicated transactions?
July 26, 2012 at 3:19 pm
Actually you need to run a full backup AFTER truncating the transaction log in order for the tlog backups to run because you have broken the log chain.
July 26, 2012 at 3:34 pm
Hi Doug,
All your questions are already answered in my previous posts of this topic forum.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comNovember 21, 2013 at 12:57 am
I am sort of a novice with transactional replication.
I have an SQL 2008 SP1 on Windows 2008 R2 but, I am trying to follow your instructions to clear out the existing replication and reinitialize subscriptions. So, I want to make sure after you run sp_repldone then the statement below it stated "This command will stop database replication until the database is unpublished and republished, which means it will prevent SQL Server from replicating the published database." Forgive me for my ignorance but, what does "unpublish" and "republished" mean? Do I need to disable publishing which will drop publication and distribution or what exactly? please help.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply