Merge Replication: Expired Subscription Clean Up Job / sp_expired_subscription_cleanup / sp_MScleanup_conflict fails with error Msg 1934, Level 16, State 1 AND Msg 20709, Level 16, State 1, Procedure sp_MScleanup_conflict…??
Issue:
If you have a Merge Replication, you may encounter the below error message thrown by Expired Subscription Cleanup job (sp_expired_subscription_cleanup) with a mention of conflict table of an article for the publication as below:
Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings:
'ANSI_NULLS, QUOTED_IDENTIFIER'.
Verify that SET options are correct for use
with indexed views
and/or indexes on computed columns
and/or filtered indexes
and/or query notifications
Technorati Tags: replication
and/or XML data type methods and/or spatial index operations.
Msg 20709, Level 16, State 1, Procedure sp_MScleanup_conflict, Line 66
The merge process could not clean up the
conflict table "[MSmerge_conflict_<Article_Name> ]" for publication "<Publication_Name>".
Troubleshooting Steps:
Before we get into troubleshooting this issue, let’s understand what happen when a cleanup job runs. When a cleanup job runs it fires the stored procedure --> sp_expired_subscription_cleanup which calls --> sp_MScleanup_conflict --> which fires delete statement on conflict table.
As per the error message, delete is failing due to incorrect setting for 'ANSI_NULLS, QUOTED_IDENTIFIER. Error message suggests us to verify that these set options can be used for -
1. indexed views
2. and/or indexes on computed columns
3. and/or filtered indexes
4. and/or query notifications
5. and/or XML data type methods
6. and/or spatial index operations.
We validated that conflict table doesn’t have any of the above mentioned options. While checking the schema of conflict table we found that it has 2 computed columns which were persisted. We also checked the SET option for 'ANSI_NULLS, QUOTED_IDENTIFIER' in the sp_MScleanup_conflict and found that it has been created with below -
/****** Object: StoredProcedure [sys].[sp_MScleanup_conflict] Script Date: 11/5/2014 6:31:15 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF --> The set option in the execution context while creating the stored procedure is set to OFF
GO
So from above it appears that, when we have persisted computed column and if we try to do delete on that table when ANSI_NULLS and/or QUOTED_IDENTIFIER is turned OFF then we can get into this issue. To confirm this, we did below test (keeping replication out of the picture)
-- Create a test database
create database test_persist
-- Create a test table
use test_persist
CREATE TABLE test (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.test ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
--insert data into the test table
insert into test(FirstName, LastName) values('Shreya','M')
--Checked if computed column is persisted
SELECT is_persisted FROM sys.computed_columns
WHERE object_id = OBJECT_ID('test ')
Result:
is_persisted
1
--Performed a delete statement on the table:
USE [test_persist]
GO
SET QUOTED_IDENTIFIER OFF
GO
DELETE FROM [dbo].[test]
GO
--Ta..da..! Got the below error as expected:
Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
To reproduce this issue for Merge Replication we followed below steps and we were able to reproduce it:
· Created a merge replication with the above table ‘test’ as article.
· To intentionally expire the subscription so that cleanup job cleans up by deleting the data from conflict table:
· Changed the retention period of the publication to 1 minute.
· Disabled the Merge agent.
· After around 5 minutes ran the Expired Subscription Cleanup job and got the following error message in the job history:
Date 11/5/2014 5:56:09 AM
Log Job History (Expired subscription clean up)
Step ID 1
Server SHREYA-SERVER\SQL2012
Job Name Expired subscription clean up
Step Name Run agent.
Duration 00:00:01
Sql Severity 16
Sql Message ID 20709
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934) The merge process could not clean up the conflict table "[MSmerge_conflict_persist_pub_test]" for publication "persist_pub". [SQLSTATE 42000] (Error 20709). The step failed.
Unfortunately neither our documentation (about SET QUOTED_IDENTIFIER) nor the error message is explicit about DML operation on persisted column can cause above error.
Below is our documentation:
Concept of SET QUOTED_IDENTIFIER:
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).
Reference:
https://msdn.microsoft.com/en-us/library/ms174393.aspx
‘Persist’ property of computed column:
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it. For more information, see Creating Indexes on Computed Columns.
Reference:
https://technet.microsoft.com/en-us/library/ms191250(v=SQL.105).aspx
Summary:
To CREATE, UPDATE, INSERT, and DELETE statements on the tables with either of the above properties the SET QUOTED_IDENTIFIER is supposed to be ON.
In Merge replication, sp_MScleanup_conflict (where delete is fired for conflict table) is by design compiled with SET QUOTED_IDENTIFIER OFF . Hence the sp_expired_subscription_cleanup (delete statement) errors out with above error because of the incompatibility of the SET option for the QUOTED_IDENTIFIER with above mentioned options.
Workaround:
If you are getting same error then check for the properties of the table in question. You will find out either of the 7 conditions is true.
If so drop the article in question from the Publication.
Refer following link for steps: https://technet.microsoft.com/en-us/library/ms152493(v=sql.110).aspx
Alter the table drop the property of the table you found mentioned in the conditions in the error message.
Ex: To drop the “persist” property for the column from the table:
ALTER TABLE <table name> ALTER COLUMN <computed column> DROP PERSISTED
Add the article back to the Publication:
Please note: Adding an article involves: adding the article to the publication; creating a new snapshot for the publication; synchronizing the subscription to apply the schema and data for the new article.
Refer following link for detailed steps: https://technet.microsoft.com/en-us/library/ms152493(v=sql.110).aspx
Written by:
Shreyanka Mathapati – Support Engineer, SQL Server Support
Reviewed by:
Devashish Salgaonkar – Technical Lead, SQL Server Support
Akbar Farishta – Escalation Engineer, SQL Server Support