Snapshot Agent Fails while Generating Snapshot in SQL Server 2014

SQLServerF1

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.

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

 

SQL Server Express Edition and Its Limitations

SQLServerF1

Microsoft SQL Server Express Edition is a free and feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs.

Express (SQLEXPR) – Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.

Express with Tools (SQLEXPRWT) – This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server Management Studio for 2014 version. Choose either LocalDB or Express depending on your needs.

SQL Server Management Studio Express (SQLManagementStudio) – This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure for SQL Server 2014, full version of SQL Server Management Studio for SQL Server 2014, etc. This can be used if you already have the database and only need the management tools.

Express with Advanced Services (SQLEXPRADV) – This package contains all the components of SQL Server Express including the SQL Server Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

Although SQL Server Express Edition is great for low cost applications, but it has many limitations too as mentioned below.

Maximum Number of CPUs – SQL Server 2005 and SQL Server 2008 R2 Expression Editions can only use one CPU. Starting with SQL Server 2012, it can be either 1 Socket or 4 cores which ever is lesser.

Maximum Memory – SQL Server 2005, 2008 and R2 Express Editions can only use up to 1 GB of memory per instance. If you install 2 instances of SQL Server Express Edition on the same server, then each one can use up to 1 GB RAM. Start with SQL Server 2012 and 2014 SQL Server Express Edition can use 1.4 GB for Database Cache and other caches can use some more memory, so up to 2 GB can be used but 1.4 GB for data and index pages.

Maximum Size of Each Database – SQL Server 2005 Express Edition can have each database only to a maximum of 4 GB size, however this has been extended to 10 GB starting from SQL Server 2008 or higher.
This 4 GB for SQL 2005 and 10 GB for SQL 2008 or higher is only for data file, but not for Log files or FileStream data. So in SQL Server Express Edition a database can be of more than 10 GB size as the data file may be less than 10 GB and transaction log file or filestream data may be of any size making it look larger.

Maximum Number of Databases Per Instance – There are no limits to the number of databases that can be attached to the server.

Maximum Number of Connections – There is no limit on number of connection for SQL Server Express Edition, so it will default to 32,767, but you will not be able to use these many connections as before that you will hit one of the other above mentioned limitations.

There are other limitations which include that SQL Server Express Edition cannot be used in features like Log Shipping, AlwaysON Availability Groups, Alwayson Failover Cluster Instances, SQL Server Failover Cluster Instances, Online Indexing, Page level restores, Fast Recovery, etc. Almost SQL Server Express Edition does not have any features other than just a basic database engine.

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.

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

 

Top Known Issues with Microsoft SQL Server 2014 CU3

SQLServerF1

With release of each new version and new service packs of SQL Server, new issues or bugs will surface and Microsoft will work on fixing high priority bugs. Below are some of the top known issues which were identified and fixed in Microsoft SQL Server 2014 CU3.

– In Microsoft SQL Server 2014, if you are running a query which involves multiple table joins, and if any of the tables referenced in the query has Clustered Columnstore Index, then you may notice significant poor performance with the query because of missing Clustered Column Store Index segment elimination.

o Micorsoft has identified this issue and has released a fix for the same.
http://support.microsoft.com/kb/2984923/en-us
o Either Apply this fix if this is business critical issue, else apply latest available Cumulative Update or Service Pack.

– If you have defined a temporary variable and populated huge number of row into it, and then if you are using this temp variable in queries where you join this with other tables, then you may notice slow performance problems with the query.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You may receive above error when you try to open memory-optimized table template from SQL Server Management Studio (SSMS) from a remote client machine and using SQL Server Authentication to connect to SSMS.
Failed to connect to Server ""
The target principal Name is incorrect. Cannot generate SSPI context

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– If a database which is part of AlwaysOn Availability Group and if this database consists of an In-Memory Table in new SQL Server and while you try to insert or update which affects multiple rows in one transaction on this table may lead to an “non-yielding scheduler” error. This will then result in the failover of the AlwaysOn Availability Group because the dump file generation takes a long time to complete.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Access Violation exception and memory stack dump will be generated in SQL Server 2014, when you create a user-defined table and add some extended properties at the column level and then tried to query sys.extended_properties system table.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You will receive below error when you have a FileTable or FILESTREAM in SQL Server 2014, and you have defined the MAXSIZE of FILESTREAM data to UNLIMITED and when the used size of FileTable when the FILESTREAM container size has reached 32 terabytes (TB) and then you tried to insert more data.
error 1105 Could not allocate space for object ‘‘ in database ‘’ because the ‘‘ filegroup is full.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Log Reader Agent crashes by causing an access violation exception and memory stack dump during the initialization of the log reader agent in Transactional Replication.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you have a table that is partitioned and also has a clustered columnstore index and when you try to create an indexed view on this table, you will receive the error message: Internal Query Processor Error: The query processor could not obtain access to a required interface.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– By default “Auto Create Statistics” databases option is enabled on all databases. In SQL Server 2014, if you try to create a natively compiled stored procedure and if internally triggers the automatic creation of missing statistics, then a self-deadlock can occur which can significantly increases the compilation time.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you created a memory-optimized table variables outside of the natively compiled stored procedures, then a LCK_M_SCH_M (schema modify lock) is taken on the corresponding memory-optimized table, during destructing of the table variable, this any concurrent declaration of memory-optimized table variables outside of natively compiled stored procedures are blocked.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, If “UPDATE STATISTICS” is performed on a computed column that contains the “ISNULL” function then it will cause an access violation exception and generated a memory stack dump.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

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

 
1 9 10 11