'Expired subscription clean up' sql agent job is failing


  • 'Expired subscription clean up' sql agent job is failing

    Message is:

    DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

    Msg 20709, Level 16, State 1, Procedure sp_MScleanup_conflict, Line 66

    The merge process could not clean up the conflict table "[MSmerge_conflict_CtelAthData_Publication_SalesAreaRegionPrice]" for publication "CtelAthData_Publication".

    I can cure the ARITHABORT issue by adding a statement BEFORE the call to



    However, I am unable to cure problems with ANSI_NULLS and QUOTED_IDENTIFIER in this manner.   Sp_MScleanup_conflict, called from sp_expired_subscription_cleanup, is declared with these settings set to OFF, undoing my SET statements???

    The particular conflict table mentioned in the message, MSmerge_conflict_CtelAthData_Publication_SalesAreaRegionPrice, related to a replicated table that is defined with two calculated columns, one of which is persisted:

    CREATE TABLE [dbo].[SalesAreaRegionPrice](
     [SalesAreaRegionPrice_ID] [nvarchar](32) NOT NULL,
     [SalesArea_ID] [nvarchar](32) NULL,
     [ExchangeRateType_ID] [nvarchar](32) NOT NULL,
     [StartDate]  AS (dateadd(day,(1),dateadd(year,(-1),[EndDate]))) PERSISTED,
     [EndDate] [datetime] NOT NULL,
     [CurrencyCode] [nvarchar](8) NOT NULL,
     [PriceList_ID] [nvarchar](32) NULL,
     [IsBasePrice]  AS (case when [PriceList_ID] IS NULL then (-1) else (0) end),
     [SalesAreaRegionPrice_ID] ASC
    ) ON [PRIMARY]

    I suspect this may be the only table with calculated columns that we replicate....

    There is also the rowguid column that is added automatically by the system. If I check the table's properties then, under 'Replication/Table is replicated' the setting is FALSE.  However, if I check the 'Publication Properties' of 'CtelAthDate_Publication' then table [SalesAreaRegionPrice] is checked and ALL its columns are checked???  ALL columns, EXCEPT the persisted column 'StartDate', have a circled green asterisk besides them.  (What do the circled green asterisks mean???)

    We are using merge replication with SQL Server 2005 sp3.   We typically run with sql server 2000 (80) backward compatibility.  However, (temporarily) switching to sql server 2005 (90) does not fix the problem. 

    Can I use (merge) replication with calculated columns?   Should I untick the persisted column in the 'Publication Properties'?  Why are this table's (+ others) properties saying there is no replication?   Alas I did not set up the replication.  Am just trying to understand what is going on so I can fix it.


    Wednesday, March 23, 2011 11:13 AM

All replies

  • Hello,
    Thank you for your question.
    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
    Thank you for your understanding and support.
    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, March 25, 2011 8:39 AM
  • We recently migrated databases from SQL 2005 to SQL 2008 on a new server and are receiving the same error on the "sys.sp_expired_subscription_cleanup" job that was set up by Replication.  I also did not originally set up this replication and have only a vague idea of what's going on, however, the problem seems to have been created by moving to SQL 2008, so I'm hoping someone has an idea of the easiest/best way to correct this.
    Tuesday, March 29, 2011 6:38 PM
  • I know it has been a long time, but did you have any luck with this problem?

    I am facing the same problem, I've debugged the sp_expired_subscription_cleanup and found that it calls a stored procedure called <@publisher_db>.sys.sp_MSdrop_expired_mergesubscription, that SP I could not find!!?

    Wednesday, July 17, 2013 1:36 PM
  • Our problem occurred because the transaction log of a replication table was growing too large as it tried to delete 20 million replication records.  Here's a link to what worked for us.

    Wednesday, July 17, 2013 2:35 PM
  • guess my problem is different than yours, Thanks anyways.

    Thursday, July 18, 2013 8:02 AM
  • I was pretty busy yesterday and at first glance thought this was a replication problem we had last year, however, I see now it was a problem 2 years ago.  This was the fix for a recent problem, not the one you are refering to - sorry.  I did go back to my notes for that time, but did not find where I documented how we fixed this.  If I had to guess, I would say it was a permission problem or a parameter in the job that is no longer valid.  Maybe generating a CREATE script of the job and looking at that code may give you an idea.  If I happen to remember or come across the fix, I will post it here.
    Thursday, July 18, 2013 1:18 PM