Failed Export: String or binary data would be truncated

  • Article

 

Situation:

The export of a site or list fails with the below stack trace:

Export-SPWeb : String or binary data would be truncated.
The statement has been terminated.
At line:1 char:13
+ export-spweb <<<< -identity "https://portal_url/" -itemurl "Lists/list_name" -path "c:\export.cmp" -includeversions lastmajor
+ CategoryInfo : InvalidData: (Microsoft.Share...CmdletExportWeb:
SPCmdletExportWeb) [Export-SPWeb], SqlException
+ FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletExportWeb

We see this in the application event logs as well:

Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 5/23/2012 9:28:10 AM
Event ID: 6398
Task Category: Timer
Level: Critical
Keywords:
User: domain\account
Computer: machine_fqdn
Description:
The Execute method of job definition Microsoft.SharePoint.Deployment.SPExportJobDefinition (ID 6039e388-2fed-4b86-8730-ccd93637d18f) threw an exception. More information is included below.
String or binary data would be truncated.
The statement has been terminated.
Event Xml:
<Event xmlns="https://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{GUID}" />
<EventID>6398</EventID>
<Version>14</Version>
<Level>1</Level>
<Task>12</Task>
<Opcode>0</Opcode>
<Keywords>0x4000000000000000</Keywords>
<TimeCreated SystemTime="2012-05-23T16:28:10.187689900Z" />
<EventRecordID>75286</EventRecordID>
<Correlation ActivityID="{71EC711F-236B-4005-8E8C-25779D315843}" />
<Execution ProcessID="9532" ThreadID="4620" />
<Channel>Application</Channel>
<Computer>machine_fqdn</Computer>
<Security UserID="SID" />
</System>
<EventData>
<Data Name="string0">Microsoft.SharePoint.Deployment.SPExportJobDefinition</Data>
<Data Name="string1">6039e388-2fed-4b86-8730-ccd93637d18f</Data>
<Data Name="string2">String or binary data would be truncated.
The statement has been terminated.</Data>
</EventData>
</Event>

Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 5/23/2012 9:28:09 AM
Event ID: 5586
Task Category: Database
Level: Error
Keywords:
User: domain\user
Computer: machine_fqdn
Description:
Unknown SQL Exception 8152 occurred. Additional error information from SQL Server is included below.
String or binary data would be truncated.
The statement has been terminated.
Event Xml:
<Event xmlns="https://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{GUID}" />
<EventID>5586</EventID>
<Version>14</Version>
<Level>2</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x4000000000000000</Keywords>
<TimeCreated SystemTime="2012-05-23T16:28:09.987507400Z" />
<EventRecordID>75285</EventRecordID>
<Correlation ActivityID="{71EC711F-236B-4005-8E8C-25779D315843}" />
<Execution ProcessID="9532" ThreadID="4620" />
<Channel>Application</Channel>
<Computer>machine_fqdn</Computer>
<Security UserID="SID" />
</System>
<EventData>
<Data Name="int0">8152</Data>
<Data Name="string1">String or binary data would be truncated.
The statement has been terminated.</Data>
</EventData>
</Event>

Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 5/23/2012 9:20:19 AM
Event ID: 5586
Task Category: Database
Level: Error
Keywords:
User: domain\user
Computer: machine_fqdn
Description:
Unknown SQL Exception 8152 occurred. Additional error information from SQL Server is included below.
String or binary data would be truncated.
The statement has been terminated.
Event Xml:
<Event xmlns="https://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{GUID}" />
<EventID>5586</EventID>
<Version>14</Version>
<Level>2</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x4000000000000000</Keywords>
<TimeCreated SystemTime="2012-05-23T16:20:19.283631600Z" />
<EventRecordID>75284</EventRecordID>
<Correlation ActivityID="{8460A5F2-442F-420E-8B73-3C4B40E43BBC}" />
<Execution ProcessID="3448" ThreadID="4200" />
<Channel>Application</Channel>
<Computer>machine_fqdn</Computer>
<Security UserID="SID" />
</System>
<EventData>
<Data Name="int0">8152</Data>
<Data Name="string1">String or binary data would be truncated.
The statement has been terminated.</Data>
</EventData>
</Event>

 

We see this in the export logs:

[5/23/2012 9:28:09 AM] FatalError: String or binary data would be truncated.The statement has been terminated.
[5/23/2012 9:28:09 AM] Debug: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.Deployment.SPExport.CalculateChildrenToExport(Boolean linksOnly)
at Microsoft.SharePoint.Deployment.SPExport.CalculateObjectsToExport()
at Microsoft.SharePoint.Deployment.SPExport.Run()

 

All the pointers are towards the SQL backend. So what is going on here?!

Export operation creates a temporary table on the content database. One of the fields on this table is for the item FullURL, defined for length 260 characters. Every object we export from the list/site will have a full URL and that gets fed here on this table. There is a possibility that the FullURL exceeds the 260 character limit - by nested folders and long [very long] names. That's when SQL will throw this exception.

Now here is the catch - SharePoint won't warn you when the FullURL for an item is exceeding this length limitation while creating it. Also, all the items from all the site collections present within a content database will go into a common table within the content database. So when we have even one rogue document library with exceptionally large number of nested folders, or with really, really long names for the folders and documents - Export operation will break for that entire content database on any location. This also impacts the list performance severely on all the sites within that content database - an interesting side effect.

So what do we do?

Run the below query on the content database to identify the rogue items:

SELECT DirName + '/' + LeafName AS fullurl, LEN(DirName + '/' + LeafName) AS urllength with(nolock) FROM alldocs WHERE siteid = 'XXXXXGUIDXXXXXX' and LEN(DirName + '/' + LeafName)>260 ORDER BY urllength DESC

You would need to get the GUID of the site collection we are having issues with export on and substitute it in the GUID part of the query above. This can be obtained from the sites and webs tables on the content database [Or look for a post of mine that talks about SharePoint GUIDS]. All the items that appear in the output of the above query need to be "adjusted". It is very likely that by now these items are not even accessible and SharePoint throws many different kinds of errors on them. So just rename one or many of the folders on the directory structure that contains this item, such that the FullURL value is less than 260 characters. Once the above query returns no results for a content database, the export should no longer return that error we were seeing for any of the sites within that content database. This will also clear up many unexplained performance issues on the lists on that content database - things will be magically faster!