x

SQL Server Transactional Replication - Schema change failed on object - Snapshot agent is not running

Greetings, this is my first question posted to SSC, please have mercy! :)

We have Transactional Replication running from SQL Server 2005 to 2012 (both standard editions). The distributor sits with the subscriber on the SQL2012 machine. Since we fired it up we have been getting sporadic failures of data import SPs. By sporadic I mean cca. 1 failure in 20-30 executions. The error message is:

 Schema change failed on object '[dbo].[TableA]'. Possibly due to active snapshot or other schema change activity.

But...

Due to active snapshot? - We don't use the Snapshot Agent; it is disabled.

Other schema change activity? - We are getting these failures since we started using replication and we are doing just as much schema changes at the moment as we had been doing before replication was started.

We have thousands of SPs and 99% of them don't do any DDL. Then there's sp_doSomething which is one in the 1%. It runs about 50 times a day and does something along these lines:

 ALTER TABLE dbo.TableA NOCHECK CONSTRAINT Constr1
 --insert some stuff etc. 
 ALTER TABLE dbo.TableA CHECK CONSTRAINT Constr1

or

 ALTER TABLE dbo.TableA DISABLE TRIGGER ALL

Triggers, check constraints are NOT replicatied. The question is then: Why are we getting this?

The parameters of the publication for your reference:

 pubid   1
 restricted  0
 status  1
 task    1
 replication frequency   0
 synchronization method  3
 immediate_sync  0
 enabled_for_internet    0
 allow_push  1
 allow_pull  0
 allow_anonymous 0
 independent_agent   1
 immediate_sync_ready    0
 allow_sync_tran 0
 autogen_sync_procs  0
 snapshot_jobid  0xFFFF
 retention   0
 has 1
 allow_queued_tran   0
 snapshot_in_defaultfolder   1
 alt_snapshot_folder NULL
 pre_snapshot_script NULL
 post_snapshot_script    NULL
 compress_snapshot   0
 ftp_address NULL
 ftp_port    21
 ftp_subdirectory    NULL
 ftp_login   anonymous
 allow_dts   0
 allow_subscription_copy 0
 centralized_conflicts   NULL
 conflict_retention  14
 conflict_policy NULL
 queue_type  NULL
 backward_comp_level 40
 publish_to_AD   0
 allow_initialize_from_backup    0
 replicate_ddl   1
 enabled_for_p2p 0
 publish_local_changes_only  0
 enabled_for_het_sub 0

Both publisher and subscriber are on latest SP.

Any suggestion is welcome!

more ▼

asked Sep 20, 2013 at 09:38 AM in Default

avatar image

RealJohn
0 1 1 1

Are you replicating stored procedure execution? - http://technet.microsoft.com/en-us/library/ms152754(v=sql.90).aspx

Another thought: The Making Schema Changes on Publication Databases article on Technet indicates that schema changes are propogated to subscribers by default. "If the schema change references objects or constraints existing on the Publisher but not on the Subscriber, the schema change will succeed on the Publisher but will fail on the Subscriber." - http://technet.microsoft.com/en-us/library/ms151870(v=sql.90).aspx

Sep 20, 2013 at 01:41 PM KenJ

Thanks for your comment KenJ! We are not replicating SP execution. They don't even exist on the subscriber.

The fact that some schema changes fail on the subscriber is OK with me, it's planned; there's no point for us to check constraints or have triggers on a read-only subscriber.

I don't know if I'm stating the obvious or not but I thought I'd mention that we get these failures on the publisher (live OLTP DB).

Sep 20, 2013 at 01:54 PM RealJohn
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2024
x385
x378
x43
x35

asked: Sep 20, 2013 at 09:38 AM

Seen: 2426 times

Last Updated: Sep 20, 2013 at 01:54 PM

Copyright 2017 Redgate Software. Privacy Policy