March 13, 2008 at 10:24 am
For SQL Server 2000 SP4 Transactional Replication
I’m using continuous transactional replication on a database with 780 tables for a total of 133GB. It is being replicated to a reporting server. Some columns on a couple of the tables needed to be increased. I’m not very savvy regarding replication. I don’t know how to make those changes without stopping replication. So, I stopped replication, deleted the subscription & removed the tables from the publication. Once the table changes were made, I added the tables back into the publication, re-pushed a subscription and started the snapshot agent. The snapshot agent kept blowing up with:
I/O error while writing BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
I checked my snapshot location. It has 298GB free. I upped my BCPBatchSize to 500000. My QueryTimeout is 3000. MaxBCPThreads is 4.
So, I have two issues:
1.)What can I do to get my snapshot to run? I HAVE to have this working by Friday morning.
2.)How could I have made the table changes without stopping the replication?
March 13, 2008 at 1:19 pm
I would reduce the batchsize.
Any particular table where the snapshot halts?
March 13, 2008 at 1:25 pm
I eventually brought the BCPBatchSize back down to 100000. Then I started getting: 'General network error. Check your network documentation.'
It would retry and start all over again.
It generally stops around the same table.
March 13, 2008 at 1:52 pm
Does it help if you exclude that particular table from the replication and add it back later after checking for errors (DBCC CHECKTABLE)
March 13, 2008 at 1:57 pm
Unfortunately, I can't attempt the snapshot during the business day. Any hit on performance is unacceptable.
I think our game plan for tonight is to bounce the distribution server and split the one publication up into two. I'm really grasping at straws.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply