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

    Question

  • 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

    Thursday, September 19, 2013 1:28 PM

All replies

  • 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.

    Thursday, September 19, 2013 1:47 PM
  • But in that case the stored procedure would fail all the time, wouldn't it? And it doesn't. Just sometimes, randomly.
    Thursday, September 19, 2013 2:23 PM
  • 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.

    Thursday, September 19, 2013 3:55 PM
  • Have u applied latest service pack?

    If not install latest SP.

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

    Thursday, September 19, 2013 4:47 PM
  • Both publisher and subscriber are on latest SP.

    Friday, September 20, 2013 9:00 AM
  • 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.

    Friday, September 20, 2013 9:31 AM