Question

Real John on Thu, 19 Sep 2013 13:28:57


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.

 The schema changes the SPs do are 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
subscription   
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


Sponsored



Replies

Ed at Hammar Technology on Thu, 19 Sep 2013 13:47:04


The option replicate_ddl is set to 1 for your publication.  If your snapshot doesn't include the constraint you are attempting to change at the subscriber then T-SQL such as:

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

Will fail.

If the constraint Constr1 doesn't exist at the subscriber then an attempt to change it will fail because you have told SQL Server to replicate DDL statements.

Real John on Thu, 19 Sep 2013 14:23:24


But in that case the stored procedure would fail all the time, wouldn't it? And it doesn't. Just sometimes, randomly.

Ed at Hammar Technology on Thu, 19 Sep 2013 15:55:37


Are you making DDL changes for each and every DML statement on your publisher?

If so then the problem should appear consistently.

If not then the problem would be sporadic.

It's kind of hard for me to believe that you are making DDL changes for each and every DML statement and if you are then you probably need to take a good long look at what you are doing and why you are doing it because something is probably very wrong.

Pradeep_DBA on Thu, 19 Sep 2013 16:47:43


Have u applied latest service pack?

If not install latest SP.

http://support.microsoft.com/kb/834453/en-us

Real John on Fri, 20 Sep 2013 09:00:07


Both publisher and subscriber are on latest SP.

Real John on Fri, 20 Sep 2013 09:31:19


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 the ALTER TABLE NOCHECK + INSERT + ALTER TABLE CHECK combo. It fails about 5 times out of 50. That's why I say it's sporadic.

I know it sounds confusing but the situation is that some SPs fail on some occasions. If it was consistent I would have very good chances of solving this. But after 3 days of investigation I decided to ask the community for help.

DecleorMX on Thu, 11 Dec 2014 01:32:30


Did you fixed ? We meet the same exception on SQLServer 2008.

DecleorMX on Mon, 15 Dec 2014 03:48:15


Hi John :

Did you fixed it ? And can you tell me your solution, I meet the same exception . Thank you .