SharePoint New-SPContentDatabase in VSTS Build throws error “String or binary data would be truncated”

Problem

Nightly build was running nicely until 3rd December, 2015, after which it started throwing error:

2015-12-08T07:51:03.8157500Z ##[error]New-SPContentDatabase : String or binary data would be truncated.    
    2015-12-08T07:51:03.8157500Z ##[error]The statement has been terminated.    
    2015-12-08T07:51:03.8157500Z ##[error]At E:\Builds\Plaza for SharePoint\Scripts\50_CreateModuleSiteCollections.ps1:36 char:9    
    2015-12-08T07:51:03.8157500Z ##[error]+         New-SPContentDatabase -Name $ContentDatabase -WebApplication $WebApplica ...    
    2015-12-08T07:51:03.8157500Z ##[error]+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    
    2015-12-08T07:51:03.8157500Z ##[error]    + CategoryInfo          : InvalidData: (Microsoft.Share...ContentDatabase:SPCmdletNewContentDatabase) [New-SPConte     
    2015-12-08T07:51:03.8157500Z ##[error]   ntDatabase], SqlException    
    2015-12-08T07:51:03.8157500Z ##[error]    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewContentDatabase

when the PS script doing nightly environment reinstall attempted to create SharePoint content database with simple command:

New-SPContentDatabase -Name $ContentDatabase -WebApplication $WebApplicationName

In ULS logs it says things like:

    System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.  The statement has been terminated.
    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.
    Exception occured during acquiring a server lock. System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.  The statement has been terminated.

Thoughts

I had all recent Windows Updates installed to SQL and SharePoint.

When I run the exact same script manually in PowerShell window in the same server, it works OK, but when the script is run as a part of nightly build, it fails.

I do not see the initial CREATE DATABASE in SQL Profiler when running New-SPContentDatabase via the Visual Studio Team Services Agent (nightly build script). I see CREATE DATABASE just fine if I run the script manually on the same server.

Issue occurs also if I manually Queue the build during day time.

There are no SQL server or SharePoint maintenance jobs taking place at the time when script is failing.

Build Agent account is the same account I'm using when running the script manually.

Finally after everything seemed OK, I was inclined to think cause is one of these things that occurred on Dec 3rd just before errors started to appear:

1. Build Agent was updated at that time (cannot test with previous version as it auto-updates, have SR open of this whole issue with VSO support)
2. Running Configuration wizard (ConfigWiz was also run 30th Nov, and there were no WU SharePoint updates released between 30thNov and 3rdDec, so it shouldn't directly be related to any updates, although ConfigWiz may have made some changes still)
3. Windows Update KB3112336 (which I cannot seem to uninstall in order to test the effect)

So, I ended up opening service request at VSTS.

After 1,5 months of sending logs to friendly team at VSTS, I got answer that “Based on investigations from our Product team it does appear there were code changes in how VSTS communicate’ s with PowerShell which is breaking your builds. Development team will continue to investigate areas they may have to rework.”

Workaround

Now that I had confirmation VSTS is the culprit, I came to think perhaps good old STSADM would work. AND IT DID!

So, use STSADM -o addcontentdb instead of New-SPContentDatabase:

#this works
& 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\STSADM.EXE' -o addcontentdb -databasename My_ContentDB -url $WebApplicationURL

#this does NOT work
New-SPContentDatabase -Name My_ContentDB -WebApplication $WebApplicationName

 

Solution

Will update here after it is fixed at VSTS or on the Agent and there is no need for workaround.