Snapshot Agent Fails while Generating Snapshot in SQL Server 2014


Replication in SQL Server is most widely used and most often of not the setup and configuration of Replication is quite simple. However there are many issues with the replication which causes problems for DBAs and with each release of newer versions new problems come up.

On such problem with replication comes with setting up replication on SQL Server 2014 instance. When you setup Merge replication and then try to initialize the replication by running the Snapshot agent, it may fail with below error.

There is already an object named “TestConstraint” in the database. Could not create constraint or index. See previous errors.

There is no special configuration with the replication, same database, same tables and same data on SQL Server 2008 R2 or 2012 instance works fine without any errors, this happens only with SQL Server 2014.

After looking at the code where it is failing, we noticed that it is trying to create system table MSmerge_conflict_publication_article where it fails. After digging and research found that in SQL Server 2014 the Snapshot Agent is trying to generate the create script to create the table MSmerge_conflict_publication_article with columns, it is also defining a constraint with same constraint name which already exists in the table as part of user table constraint. Here both the user table constraint name and system table constraint name are same thus causing this error.

Because of this change in SQL Server 2014, the merge replication can fail on tables which have constraints defined on them. This appear to be bug, but not seem to have any solution so far. Post talks about same problem and they have already tested installing SQL Server 2014 CU4, but the issue still persisted.

As of now one of the workaround is to drop all constraints before on the user tables which are replication and create them later. Other options which can be tried include initializing the Subscriber database using other methods such as Backup/Restore where we can avoid the Snapshot agent from running the scripts. Hopefully this issue will be fixed soon.

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Adding New Articles to Transactional Replication without Generating Snapshot of All Articles


In Transaction replication which was already setup and If we want to add new articles, then a new Snapshot has to be generated and applied to the Subscriber, but by default snapshot of all the existing and new articles will get generated and applied to the Subscriber, which is not what we want as it can take long time when the database is large and causes Subscriber to be unavailable while applying the Snapshot. There is a way in which we can avoid this behavior and just generate the Snapshot of the newly added articles and apply them to the Subscriber, which will not cause any problem to the existing articles which can be available during this process.

Let’s see how we can achieve this.

Run the below commands on the Publication database


If the columns “immediate_sync” and “allow_anonymous” are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated. As we do not want this behavior, we will change these values to 0(Disabled) for both the “immediate_sync” and “allow_anonymous” options.

We can disable these options by running below commands on Publication database

EXEC sp_changepublication 
@publication = 'your publication name', 
@property = 'ALLOW_ANONYMOUS' , 
@value = 'False' 

EXEC sp_changepublication 
@publication = 'your publication name', 
@property = 'IMMEDIATE_SYNC' , 
@value = 'False' 

Now, we can go ahead and add the new article to the publication from GUI, and then we need to start the Snapshot Agent, which will generate new Snapshot just for the new articles. If we have not run the snapshot agent then the newly added article will be in Subscribed state, but will not be active, Inorder to make this active, we need to run the Snapshot agent job.

We can check article status on Subscriber database by running the Subscriber database.


Do not follow these steps directly on production environment, please test the solution on test server to get comfortable and test if it is working as expected.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Script upgrade may fail when Database snapshot is created on subscriber database

Consider a scenario where you are applying Service Pack or Cumulative Update for SQL Server instance and after which the SQL Server instance does not start. Below errors are logged in SQL Server errorlogs.

Error: 3906, Severity: 16, State: 1.
Failed to update database "DBSnapshot_20120512" because the database is read-only.

Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 3906, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

As the SP or CU scripts are trying to upgrade the snapshot database which is a read-only database the upgrade script was failing. Below workaround should help you with starting the SQL Server service.

Start the SQL Server service from command line using trace flag -T902 which will by-pass running upgrade scripts

Sqlservr.exe -s Instance_Name -T902 -T3608

From another command prompt connect to the SQL instance

Sqlcmd –S Server_Name\Instance_Name

Drop the snapshot database and stop the SQL Service which was started from command prompt.

Start the SQL Service normally and it should start fine and you should be able to see from SQL Errorlog that the upgrade scripts completed successfully.

The reason for failure was because the parent database for database snapshot was also a subscriber in replication setup.

Within the upgrade script procedure “master.sys.sp_vupgrade_replsecurity_metadata proc”, a cursor is being opened that walks through all user databases and for each database where HAS_DBACCESS()returns 1, we then check if an object with name MSsubscription_properties exists and if sys.columns contains an entry with name ‘job_step_uid’ and if both conditions are met, we call sys.sp_MSupgrade_subdb_security_metadata on the database. This procedure fails with error 3906 if the database is read only.

In this particular scenario the failure happens on a snapshot database which meets the two conditions.

Run the below script to identify any database snapshots that can cause this issue. If the script returns any results then drop those database snapshots before applying the Service Pack/ Cumulative Update

use master
declare @name sysname, @has_problem bit, @stmt nvarchar(512)
declare @tab table (name sysname)
declare dbcur cursor local fast_forward for
select name from sys.databases
where database_id > 4 and has_dbaccess(name) = 1 and is_read_only = 1 and source_database_id is not null
open dbcur
fetch next from dbcur into @name
while @@fetch_status = 0
set @stmt = N'SET @has_problem = CASE WHEN EXISTS (SELECT * FROM ' + quotename(@name) +
N'.sys.objects WHERE name = N''MSsubscription_properties'') AND EXISTS (' +
N'SELECT * FROM ' + quotename(@name) + N'.sys.columns WHERE name = N''job_step_uid'') ' +
exec sp_executesql @stmt, N'@has_problem bit output', @has_problem output
if @has_problem = 1
insert @tab values (@name)
fetch next from dbcur into @name
close dbcur
deallocate dbcur
select name as problem_databases from @tab

Keerthi Deep