Replication Performance

Hi,

I am creating 1000 records in a database through a stored procedure. These are replicated to a different database in the same server. The replication process is taking almost 2 hrs to transfer all the data. I have changed the querry time out value to 10000, which I think is high enough value and changed the commit batch size value to the no. of transactions (1000) in the agent profile. But there was no change. In between the replication gives a message saying 'the process is running and is waiting for one of the backend connections'. Is there any solution for this issue. Kindly suggest a solution to speed up the replication process. It is transactional replication.

Thanks,

Madduri

you are replicating to a database on the same server and it is taking 2 hrs to complete 1000 records. of course there is something terribly wrong in your setup. I have administered Transactional replication for 3 years and i have seen millions of records replicating within few minutes in same domain and different server. So tell me, is it immediate updating or queued updating. and post the full error message with severity and error message no. Explain full architecture.

Madhu

As Madhu mentioned it is really something wrong...
I am doing the replication coast to coast with few seconds latency.

First thing I will check the blocking on the server before doing anything.

Mohammed U.

Hi all,

Thank you for your replies. It is immediate updating. I dont find any errors in replication. It keeps running saying 'the process is running and is waiting for one of the backend connections' and 'delivering replicated transactions'.

I do not find any blockings too.

I have updated the statistics and had rebuild the indexes...still the same. Please suggest some solution.

Thanks.

The only thing I can suggest given all the unknowns is to create a simple test table and replicate it - assuming you can make it perform well with a simple case, make it more and more like your production case until you start running into problems - presumably, the solution will present itself

MDD

Is replication using default profile or user profile...Can you post profile info..

Mohammed U.

Hi Mohammed,

I have created a user profile which is being used as below.

Distribution Agent:

BcpBatchSize: 100000
CommitBatchsize: 1000
CommitBatchThreshold: 10000
HistoryVerboseLevel: 1
KeepAliveMessageInterval: 300
LoginTimeout: 15
MaxBcpThreads: 1
MaxDeliveredTransactions: 0
PollingInterval: 10
QueryTimeout: 60000
SkipErrors: No Value
TransactionsPerHistory: 100

Log Reader Agent:

HistoryVerboseLevel: 1
LoginTimeout: 15
PollingInterval: 10
QueryTimeout: 60000
ReadBatchSize: 1000

Thanks, Madduri