Can starting a snapshot cause blocking?

Question

Post reply

Can starting a snapshot cause blocking?

Robert W Marda

SSChampion

Points: 13413
More actions
April 21, 2006 at 7:42 am

#96940

I don't know a lot about replication. When you start a snapshot can it cause blocking on the table it is currently copying?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Viewing 9 posts - 1 through 8 (of 8 total)

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

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

Yes, it certianly can.......but there are ways around it.....look at the options for creating the snapshot.....

Robert W Marda SSChampion Points: 13413 More actions · Answer 2

Thanks!

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Joey Cheung SSC Enthusiast Points: 148 More actions · Answer 3

There is an option in the snapshot generation that 'Do not lock table'. Not sure if it work.

Does anyone use his option? and does it work? Checking this option, SQLServer forces a complete re-initializing of all subscribers (It ask for comfirmation first). So , execrise caution when usingt his option.

Another question, while snapshot is generating will it lock one table at a time or will it lock all tables until it finishes?

Thanks !!

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

One at a time, as the snapshots for each table are generated...

Krishna Mohan Kasi Mr or Mrs. 500 Points: 593 More actions · Answer 5

Well what i would recommend you is to look into the option of concurrent snapshot generation for your problem in reference to blocking.

This would really help u

Graham Williams SSC Enthusiast Points: 144 More actions · Answer 6

The option to do the non locking snapshots as has been suggested helps as it means that while the main bit of the copy is working the table is available for reading and updating at the publisher. However, you need to be aware that when the initial scripts for creating the tables are generated it will still attempt a schema lock and if you have a heavy transaction environment with proc recompiling, etc you can end up with blocking and deadlocks at schema level - in my experience it's normally the transactions that get rolled back. I still have to do this work at the quietest possible times even with the snapshot non locking option because of this.

Another problem with the shared snap shots happens if the tables are very large. Any updates that occur while the snapshot is in progress are applied to the snapshot after it has been copied at the subscriber using the generated repl procs. Unfortunately it does this before running the scripts to create the indexes so results in tables scans for each update. On very large tables this may cause your snapshot to time out when being applied at the subscriber. Getting round this can be tricky.

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 7

If you're using transactional replication on SQL Server 2005, there is an alternative option that'll avoid the problems you face - you can initialize a subscriber using a backup of the production database. This is not the same as a nosync initialization in SQL Server 2000 - the transaction_timestamp value of the publisher is reused. in other words, you don't need to prevent access to the publisher during the restore of the backup and setting up of the subscriber as concurrent transactions will be applied when you use the distribution agent.

Cheers,

Paul Ibison SQL Server MVP, http://www.replicationanswers.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Graham Williams SSC Enthusiast Points: 144 More actions · Answer 8

Paul - Thanks for that info. Sounds like the answer to the probs I've been having to work around for the last few years. Adds a bit more weight for a 2005 migration!

Cheers

Graham