October 27, 2008 at 11:39 am
Hi all,
I had an issue whereby i was attempting to apply a snapshot from the file system of server A (Distribution server) to my subscriber (Server B).
I marked the subscriber for reinitialisation, and then would start the distribution agent. After more or less 30 minutes every time, I would get the message 'Query Timeout Expired', and the snapshot application would restart.
anyone come across this before?
There is no router or anything between the 2 servers so it is a direct link. the snapshot file is 53gb and conatins image columns. this is what would happen:
10:25 Starting agent.
10:25 Initializing
10:55 The process is running and is waiting for a response from the server.
10:55 Query timeout expired
10:56 Initializing
10:26 The process is running and is waiting for a response from the server.
11:26 Query timeout expired
and so on.
i managed to get this from another error which showed a 1800 query timeout which matches my 30 minute problem:
2008-10-26 01:40:06.708 Connecting to Distributor 'Server A'
2008-10-26 01:40:06.942 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-packetsize 32768
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
where would i go about changing this value?
any help appreciated
October 28, 2008 at 1:55 pm
You can create a new distribution agent profile and change the timeout value. In Replication Monitor navigate to your publication so that you can see your subscriptions in the "All Subscriptions" tab in the right pane. Right click on your subscriber and choose "Agent Profile". In the window that opens click the "New..." button, then select the Default agent profile and click the "OK" button. Give your new profile a name, a description, and change the value for QueryTimeout to something higher that will allow for enough time to apply the snapshot. Click "OK", then make sure that the checkbox is selected for your new profile and click "OK" again. Stop and restart your distribution agent so it runs under the new profile.
Just curious - what kind of replication is this (merge, transactional, etc.)?
October 29, 2008 at 3:25 am
Hi Thanks for the reply.
This is transactional replication from Oracle onto a sql server distributor server, and then onto another sql server subscriber.
Am i right in assuming that the QueryTimeout value in the agent, is what caused my snapshot to timeout every time?
October 29, 2008 at 8:07 am
I believe that is correct. You might also want to tinker with the setting for BcpBatchSize to see if it helps. The default is 1,000 rows per transaction at the publisher. If 1,000 rows can't be inserted into your table within that 30 minutes I believe you'd see that error too. I'd run profiler to watch what's happening and see if you can find where things are breaking down. Is your subscriber in the same facility as the distributor that's pushing the snapshot to it?
October 29, 2008 at 8:55 am
Yes - distributor and subscriber are sitting together - direct link - not a router/switch timeout.
The way I got round this problem at the weekend was by manually BCPing the created snapshot file into the subscriber, zapping the msrepl_commands table and restarting the agent.
I have applied the snapshot directly into the subscriber successfully in the past with a similar size image table (~50gb) - which is all the more stranger really.
Thanks for the advice - appreciated
October 29, 2008 at 9:12 am
My next suggestion exactly what you've done - manually bcp the snapshot file in, but you shouldn't have to zap anything in MSrepl_commands. I've written a blog post about how to do this for large snapshots from one datacenter to the other, but you can just remove the parts about compressing\transferring the snapshot and it should work. You can read it here: http://kendalvandyke.blogspot.com/2008/10/reduce-transactional-replication.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply