Can I truncate a table that is participating in transaction replication?

Can I truncate a table that is participating in transaction replication?

If you think about it conceptually, the table exists within the publication database, and the table is expected to exist on any databases that are subscribing to the publication. SQL Server Replication works by monitoring the database transaction log for inserts, updates, and deletes on objects that are replicated, and by transmitting those changes to subscribers. Truncate is a minimally logged transaction that essentially empties the table, but leaves the schema intact.

So, if I try to truncate a table that is in a published database by running TRUNCATE TABLE TRADES, I get an error:

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table ‘trades’ because it is published for replication or enabled for Change Data Capture.

If I convert the truncate into a DELETE FROM TRADES statement, this works perfectly – and is replicated to any subscribers.

If I try the same TRUNCATE TABLE TRADES statement on a subscriber database, this also works perfectly.

SQL Server Parameter Sniffing

MVP Gail Shaw has produced an excellent video tutorial on SQL Server Parameter Sniffing, available on the Pluralsight network. The video was released in August 2016, and covers SQL Server 2016. At 90 minutes long, it’s nicely concise – and it covers what parameter sniffing is, identifying and capturing parameter sniffing via techniques such as DMVs, Extended Events, and the Query Store; and naturally covers some of the options available to work

Microsoft Dynamics CRM – Filtered Views and SQL Server 2014

Microsoft Dynamics CRM makes extensive use of filtered views – these are standard views in the database, but they prevent unauthorised users (including DBAs) from seeing sensitive data by the way they are coded. During several migrations of Microsoft Dynamics CRM from both the 2011 and 2013 versions to 2015, my project teams have hit performance issues which center on filtered views and the new Cardinality Estimator in SQL Server 2014. The Cardinality Estimator is the part of the database engine that works out how to actually do the work for a particular query, and it was largely based on a code base written for SQL Server 7.0.

Another reason to sign-up for Visual Studio Dev Essentials

The March update for Visual Studio Dev Essentials has announced that SQL Server 2014 Developer Edition is now free for Dev Essentials members! It was always great value for non-production development and testing, but now – it really shows how Microsoft SQL Server is really gunning for Oracle’s database customers.

Exciting news! Starting today, SQL Server 2014 Developer Edition is available as a free download for Dev Essentials members. Build almost any kind of database application and unlock insights faster with a complete business intelligence solution. SQL Server 2014 was designed to work in hybrid environments, making it easier for you to build back-up and disaster recovery solutions with Azure, as well as delivering a consistent experience across on-premises and cloud.

DTS_W MAXIMUMERRORCOUNTREACHED Error in SQL Server SSIS Package

I’ve had a process for several years that imports approximately 20 million rows into a staging table from a 3GB CSV flat file. As these things do, it has evolved over time. It started as a bulk import statement, but – as errors started to regularly kill the process – it evolvedinto a SSIS package. Without too much difficulty, I was able to set up a data flow task so that rows that generate errors would be diverted. Unfortunately, the package suddenly started failing with an DTS_W_MAXIMUMERRORCOUNTREACHED error.

The column delimiter for column “Column 15” was not found.

Error: 0xC0202055 at Import, Flat File Source [13]: The column delimiter for column “Column 15” was not found.

Error: 0xC0202092 at Import, Flat File Source [13]: An error occurred while processing file “E:\Data\csv-import.csv” on data row 1.
Error: 0xC0047038 at Import, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.