Sql-server – SQL Server 2005 to 2012 Replication, cannot access replicated data

replicationsql serversql-server-2005sql-server-2012

I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes several minutes after that I got error. It looks like table is locked.

My Observations

  • Replication monitor shows everything is OK.
  • Distribution service is running on MS SQL Server 2005.
  • I see replication files created on network share.

Why can I not access the table ?

Best Answer

This is covered in Frequently Asked Questions for Replication Administrators:

Are tables locked during snapshot generation?

The length of time that the locks are taken depends on the type of replication used:

For merge publications, the Snapshot Agent does not take any locks.

For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.

For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

Related Question