|
|
You're in the
sql server replication
group:MSrepl_commands clean up
sql server replication:
I got Peer-to-Peer Replication configured and running on SQL Server 2005 EE + SP2 on Windows 2003 R2 + SP1 platform. Total number of DB servers replicated 4. My question is how to I shrink the distribution DB once it starts to grow about 3GB per day? (Current distribution DB size is ~58GB) I've tried the DBCC shrinkdatabase, DBCC shrinkfile commands. But had no luck with it.. As a last restore I truncated the sys table 'MSrepl_commands' as it was the largest table (55 million rows) in the distribution DB. Prior to this I made sure that P2P replication was stopped and there was no activity on the DB servers. But the above truncation caused replication errors once replication was enabled. It was rolling back transactions, as it could not find the data in the subscriber. The standards SQL 2005 distribution db maintenance jobs are created and enabled. (2 hrs. transaction retention and 3 hrs history retention) job runs every 10 min. Any ideas how to shrink the distribution DB are welcome Thanking in advance HS
Basically I think you need to reinitialize your replication topology to try
to get going again. You might have to drop all subscriptions and redeploy them. Set your distribution retention to 0, and have your distribution clean up task run more frequently in the future. You might also want to ensure that your distribution agents are running continuously and keep your nodes online as much as possible. -- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "HS" <ckid@tpg.com.au> wrote in message news:470a2c55$1@dnews.tpgi.com.au... > Hi > > I got Peer-to-Peer Replication configured and running on SQL Server 2005 > EE + SP2 on Windows 2003 R2 + SP1 platform. > > Total number of DB servers replicated 4. > > My question is how to I shrink the distribution DB once it starts to grow > about 3GB per day? (Current distribution DB size is ~58GB) > > I've tried the DBCC shrinkdatabase, DBCC shrinkfile commands. But had no > luck with it.. > > As a last restore I truncated the sys table 'MSrepl_commands' as it was > the largest table (55 million rows) in the distribution DB. Prior to this > I made sure that P2P replication was stopped and there was no activity on > the DB servers. > > But the above truncation caused replication errors once replication was > enabled. It was rolling back transactions, as it could not find the data > in the subscriber. > > The standards SQL 2005 distribution db maintenance jobs are created and > enabled. (2 hrs. transaction retention and 3 hrs history retention) job > runs every 10 min. > > Any ideas how to shrink the distribution DB are welcome > > Thanking in advance > > HS >
Hi Hilary
Tnx for the quick response.. Is there another method to fix this issue without actually dropping P2P replication? the distribution agents run continuously, on all 4 servers all 4 nodes are online 24x7, and p2p tractional replication works fine even the distribution clean up job on all 4 server runs successfully. except on the primary server where the P2P replication topology was initiated, the distribution clean job doesn't actually cleanup the dist. db. the log for the job has the message " Execute as user: XXXXXusername. Removed 0 replicated transactions consisting of 0 statements in 5 seconds. (0 rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded." i'm not sure y the same job cleans up the dist. db on the other 3 servers, and not on the primary server. btw all our servers runs under local accounts, as this is an isolated tier (there is no domain controller in the environment)in the whole solution. sql server service, and replication service have their own local accounts with admin privileges. the same service account and password is used across all 4 servers Log Reader Agent and the Snapshot Agent is configured with same credentials. However the Snapshot agent was never started, as it was not required to configure P2P replication. The Log Reader Agent and all distribution agents run continuously. Any suggestions are welcome. tnx HS [quoted text, click to view] "Hilary Cotter" wrote:
> Basically I think you need to reinitialize your replication topology to try > to get going again. You might have to drop all subscriptions and redeploy > them. > > Set your distribution retention to 0, and have your distribution clean up > task run more frequently in the future. > > You might also want to ensure that your distribution agents are running > continuously and keep your nodes online as much as possible. > > -- > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "HS" <ckid@tpg.com.au> wrote in message news:470a2c55$1@dnews.tpgi.com.au...
Your problem is that your truncate has left your topology in an inconsistent
state. One or more nodes may have different data in them than the other ones. If you are ok with this and the problems which may crop up down the road. You may be able to use the continue on data consistency error profile to bypass some of these errors. -- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "HS" <HS@discussions.microsoft.com> wrote in message news:E4A3900F-5522-41D4-B310-2D8CAA4D8843@microsoft.com... > Hi Hilary > > Tnx for the quick response.. > > Is there another method to fix this issue without actually dropping P2P > replication? > > the distribution agents run continuously, on all 4 servers > > all 4 nodes are online 24x7, and p2p tractional replication works fine > > even the distribution clean up job on all 4 server runs successfully. > except > on the primary server where the P2P replication topology was initiated, > the > distribution clean job doesn't actually cleanup the dist. db. > the log for the job has the message " Execute as user: XXXXXusername. > Removed 0 replicated transactions consisting of 0 statements in 5 seconds. > (0 > rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded." > > i'm not sure y the same job cleans up the dist. db on the other 3 servers, > and not on the primary server. > > btw all our servers runs under local accounts, as this is an isolated tier > (there is no domain controller in the environment)in the whole solution. > sql > server service, and replication service have their own local accounts with > admin privileges. > > the same service account and password is used across all 4 servers > > Log Reader Agent and the Snapshot Agent is configured with same > credentials. However the Snapshot agent was never started, as it was not > required to configure P2P replication. The Log Reader Agent and all > distribution agents run continuously. > > Any suggestions are welcome. > > tnx > > HS > > > > > > > > "Hilary Cotter" wrote: > >> Basically I think you need to reinitialize your replication topology to >> try >> to get going again. You might have to drop all subscriptions and redeploy >> them. >> >> Set your distribution retention to 0, and have your distribution clean up >> task run more frequently in the future. >> >> You might also want to ensure that your distribution agents are running >> continuously and keep your nodes online as much as possible. >> >> -- >> RelevantNoise.com - dedicated to mining blogs for business intelligence. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "HS" <ckid@tpg.com.au> wrote in message >> news:470a2c55$1@dnews.tpgi.com.au... >> >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |