Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hoogabooga
Starting Member
2 Posts |
 Posted - 2011-05-03 : 15:07:46
|
| I've been using Merge Replication for years and all of a sudden, I am unable to create a snapshot. We made a number of changes to the underlying data (and reinitialized all 50 subscriptions) and now I am consistenly getting the following error messages after about 30 minutes. - [0%] The replication agent had encountered an exception. - Source: Replication - Exception Type: Microsoft.SqlServer.Replication.SqlCommandTimeoutException - Exception Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. - Message Code: -2Typically the snapshot takes a minute and a half. I'd really appreciate some input on this if someone can help.Thanks,Doug. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-05-04 : 03:52:35
|
| Does the SQL Agent still have access to the snapshot directory? Are any files created in the directory?Did you reinitialise the Snapshot agent and create a new snapshot (I know it sounds silly, but sometimes people don't)?Have you checked for locking contention? sp_locks will help there.. |
 |
|
|
hoogabooga
Starting Member
2 Posts |
Posted - 2011-05-04 : 09:50:16
|
| Thanks for your reply. To your questions, I've confirmed that the SQL Agent still has read/write access to the network share. The old snapshot files are there are there and nothing else. That is what I was expecting.When I reinitialized, I did flag for a new snapshot to be created. I've also tried running the snapshot several times after that with the same results as above.Locking contentions. There appear to be a HUGE number of locks against this process 2.5 million after 15 minutes of operation (holy crap!) mostly against the MSmerge_past_partition_mappings table. I think you may have located the problem, but I'm not sure what to do from here! |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-05-04 : 11:16:40
|
| You may need to increase the timeout. To do this, create a new agent profile with a long timeout value (just make it as long as you think sounds sensible. It sounds like you have a large table and this is not replying fast enough to SQL, therefore getting the timeout message. |
|
|
|
|
|
|
Merge publication - can't create snapshot