Thursday 14 May 2015

Version store out of memory when building SSDT project with msbuild.exe

I use Bamboo to build my SSDT database projects. It is random that sometimes build fails with message:
error MSB4018: Microsoft.Isam.Esent.Interop.EsentVersionStoreOutOfMemoryException: Version store out of memory (cleanup already attempted)

Full stack trace is like this:
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(550,5): 
error MSB4018: The "SqlBuildTask" task failed unexpectedly.
error MSB4018: Microsoft.Isam.Esent.Interop.EsentVersionStoreOutOfMemoryException: Version store out of memory (cleanup already attempted)
error MSB4018:    at Microsoft.Isam.Esent.Interop.Update.Save(Byte[] bookmark, Int32 bookmarkSize, Int32& actualBookmarkSize)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.EseResultSet.Update(Action action)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.RelationshipEntry.set_Element(IModelElement value)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.ReferenceLinker.ResolveInternalElementReferences()
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.LoadExternals(TextReader input, String fileName, String logicalSourceName, IList`1 externalPartsSubstitution, Boolean suppressErrorsForMissingDependencies)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.AddReference(CustomSchemaData customData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.OnCustomDataAdded(CustomSchemaData customData)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.AddCustomData(CustomSchemaData customData, Boolean raiseEvents)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlModelBuilder.Add(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelBuilder.AddOrUpdate(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.Build.SqlTaskHost.AddOrUpdate(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.ProcessReferences(TaskLoggingHelper providedLogger, SqlTaskHost host, ErrorManager errors)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.LoadImpl(ITaskHost providedHost, TaskLoggingHelper providedLogger)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.Load(ITaskHost providedHost, TaskLoggingHelper providedLogger)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteLoadTaskHostStep()
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteStep(Func`1 step)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.Execute()
error MSB4018:    at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
error MSB4018:    at Microsoft.Build.BackEnd.TaskBuilder.d__20.MoveNext()

It has been already registered as a connect bug: https://connect.microsoft.com/SQLServer/feedback/details/749108/msbuild-fails-randomly-with-version-store-out-of-memory-cleanup-already-attempted-errors-msb4018
According to comments in the bug a solution is to edit project file *.sqlproj and add such section:

I just want to confirm that this fix works for me.

Wednesday 4 March 2015

SSDT generates 2012 option for target 2008 when target is a dacpac

There is a bug in SSDT that causes database options to be generated for SQL Server 2012 even if the target platform is set to SQL Server 2008.

I observed it in a such scenario:
sqlpackage.exe was used to generate publish script to create new database. Sqlpackage target is set to a dacpacfile (just empty model without any tables).

The problem is that publish script has few options set which are available only in 2012. The options are:


But when I generate publish script directly from VisualStudio and specify target database as real 2008 database (not dacpac) then resulting publish script is correct. It doesn't contains above options.

This is already registered as a connect bug:
https://connect.microsoft.com/SQLServer/feedback/details/1049478/ssdt-generates-2012-option-for-target-2008-when-target-is-a-dacpac

I have implemented a deployment contributor that removes such invalid options. The code can be downloaded from following link:

https://gallery.technet.microsoft.com/scriptcenter/SSDT-contributor-to-remove-05488cf9