SharePoint 2013 Site Collection Export - String or binary data would be truncated

Problem: During a site collection export, we received the nebulous SQL Server error “String or binary data would be truncated.” Based off some research and experience in the past, this is normally caused by a URL somewhere in the site exceeding the 260 character limit. We queried the AllDocs table in the content database to check if the combination of the DirName and the LeafName exceeded the limit of 260 for the site collection in question. Our result came up empty.

Solution: A different site in the content database had a URL that was over the limit and it was causing issues when trying to export the original site. See below for the technical details for more information. Thanks to Paul Feaser for figuring this one out!

Technical Details: With our only trouble-shooting information being the generic error message, “String or binary data would be truncated”, we set up a SQL Server Profiler session to get more details while attempting to export the site collection again. SQL profiler showed us that the error was being generated while running the stored procedure, DeplAddWebDependencies. In order to do some testing, we made a copy-only SQL backup and restored it in our test environment. Running the exact same SQL procedure, it completed successfully which made the problem even more complex.

Since the error was still occurring in our production environment every time, the next step we took was to restore the same copy of the SQL database on the production environment. Our production environment has over 100+ sql databases with most databases containing many different site collections. The SQL Server itself is appropriately sized to handle the massive workload we are running. In the production environment, we were able to recreate the error every time on the restored database copy running the procedure DeplAddWebDependencies. The problem within the stored procedure was the fifth “Insert Into …Select” statement. When commenting out the “Insert Into” part of the statement, the select runs and interestingly returns zero records. So the question is how could an insert of zero records cause a data truncation error?

After further investigation, it turns out the issues was with a query hint that was being used in the select statement: OPTION (FORCE ORDER). The following screen capture shows the query hint along with the error.

This particular query hint tells the SQL Compiler to override its derived execution plan and join the tables in the order that they are listed. The select statement in production was taking a little over 2 seconds with the FORCE ORDER hint. Commenting out just the query hint resulted in an immediate response without the data truncation error. With this in mind, we were able to figure out that the issue was some records in a different site collection were greater than the 260 allowed for the full URL. Because SQL Server is executing parts of the query in parallel and then puts everything together in the end, SQL was actually bringing back all records from the AllDocs quicker than the WHERE clause was being evaluated. The records from the different site collection where causing the data truncation error. Since it is unsupported to modify SharePoint code, the fix was to have the owner of the different site collection fix the records that were greater than 260. As soon as the offending site collection URLs were reduced, the original site collection exported correctly.