SQL Server Snapshot Agent stops bulk copying data when 33% done

Question

SQL Server Snapshot Agent stops bulk copying data when 33% done

PAPERPKADS

SSC Veteran

Points: 259
More actions
December 10, 2014 at 2:33 am

#292710

Configured transaction replication for a server. but snapshot agent stops bulk copying data when 33 % done.
On subscriber when viewed through monitor it shows following error.
The replication agent has not logged a progress message in 5 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.
While checked MSrepl_errors on publisher it shows the error
Server , Level 13, State 56, Procedure , Line 2 Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Googled deadlocks , restarted agent servives, droped and recreated publisher..... but no success... some one help please...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

quackhandle1975 SSChampion Points: 11055 More actions · Answer 1

What is the size of the snapshot you are generating? Replication can be immensely temperamental when it comes to generating a snapshot of over 2GB (I think, is it still 2GB?)

The fact you have a deadlock error may indicate that a user has tried to run a DML statement against the publication database during the snapshot but I'd go with the snapshot being too large.

For VLDBs I have followed the following process:

1.) Stop all users accessing the publication database

2.) copy the database to the subscriber location, restore as subscription database

3.) configure replication but DO NOT GENERATE SNAPSHOT or anywhere it says "reinitialize". - (generally better to configure replication via scripts)

4.) do a check transactions are replicating across (see Kendra Little article for Canary Tables)

5.) allow users back on to publication database

you could try Paul Ibison's excellent site http://www.replicationanswers.com

IIRC, there is also an article on SSC from Klaus Ashenbrenner where you can generate the subscription db from a backup file.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

PAPERPKADS SSC Veteran Points: 259 More actions · Answer 2

Thank you for replying...

Size of snapshot to be delivered is about 1gb.

I did steps u mentioned. but error still persists....

In eve problem was solved... infact once again not exact error was generated SQLServer...

When i watched bulk copying process deeply i came to know that every time copying stopped on a specific replicated table.. i dropped that table and replication started again fine....

Now problem is that specific table is my transaction table .... what could be problem with that table????

It has indexes and triggers too...