I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
Hi,
Transactional replication provides several different sync method to help alleviate locking issue on publication DB. You can take a look at @.sync_method in sp_addpublication (http://msdn2.microsoft.com/en-us/library/ms188738.aspx).
You can try with concurrent/concurrent_c. And if you are using SQL 2005 SP2 and you are using enterprise edition, you can also use "database snapshot/database snapshot character". Snapshot agent creates a DB snapshot on the publication DB and then generates replication snapshot based on the DB snapshot. So it significantly reduces the possiblities of deadlocking on the publication DB.
Peng
