Hi Shaishav,

There was not much difference after running the script in the given link to clean the audit trail and version history. It has only come down by few thousands.

How odd. The IsSqlCe test is based on the connection string's providerName, so that shouldn't be an issue. Can you check what it's actually running with something like

SELECT sqltext.TEXT, req.session_id, req.status, req.start_time, req.command, req.cpu_time, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 

The query it should be running for SQL Server is still fairly unpleasant and slow, but it shouldn't be taking hours.

UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId

It shows that the above query is running

Reassuring that it's not using the SQL CE version, though strange that it's taking so long. My test database has 8.5 million rows in cmsPropertyData and only takes a couple of minutes to run that query.

You could do the change of the versionId column manually before starting the migration, and the migration should then quietly skip that bit. That might let you experiment with indexes to speed it up.

This SQL should be what the migration runs, though you might need to drop an index or two first:

ALTER TABLE cmsPropertyData ADD [versionId2] [int] NULL

UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId

ALTER TABLE cmsPropertyData DROP COLUMN [versionId]

sp_rename 'cmsPropertyData.versionId2', 'versionId', 'COLUMN'

It finishes the given query in SSMS very fast, like in 3 mins. But this keeps running for hours from the application. As a workaround, i'm running the alter, update and rename query from SSMS before migration and then start Umbraco migration, now it is not getting stuck at the update query. I will let you know once the whole migration process is done. Thanks.

Hi Steve Megson,

We are now getting an error in PostMigrations.

{"@t":"2019-10-03T14:21:55.0051163Z","@mt":"Database configuration failed","@l":"Error","@x":"System.NotSupportedException: Surrogate pairs are not supported.\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanCodeString(String text, CleanStringType caseType, Char separator, String culture, Config config)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanString(String text, CleanStringType stringType, String culture, Nullable`1 separator)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanStringForUrlSegment(String text, String culture)\r\n   at Umbraco.Core.StringExtensions.ToUrlSegment(String text, String culture)\r\n   at Umbraco.Core.Strings.DefaultUrlSegmentProvider.GetUrlSegment(IContentBase content, String culture)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.<>c__DisplayClass0_0.<GetUrlSegment>b__0(IUrlSegmentProvider p)\r\n   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()\r\n   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.GetUrlSegment(IContentBase content, IEnumerable`1 urlSegmentProviders, String culture)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.GetDto(IContentBase content, Boolean published) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1356\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.<RebuildMemberDbCacheLocked>b__82_1(IMember m) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1581\r\n   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()\r\n   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)\r\n   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.RebuildMemberDbCacheLocked(IScope scope, Int32 groupSize, IEnumerable`1 contentTypeIds) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1542\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1393\r\n   at Umbraco.Web.Migrations.PostMigrations.PublishedSnapshotRebuilder.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\Migrations\\PostMigrations\\PublishedSnapshotRebuilder.cs:line 27\r\n   at Umbraco.Core.Migrations.PostMigrations.RebuildPublishedSnapshot.Migrate()\r\n   at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger)\r\n   at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)\r\n   at Umbraco.Core.Migrations.Install.DatabaseBuilder.UpgradeSchemaAndData(MigrationPlan plan)","SourceContext":"Umbraco.Core.Migrations.Install.DatabaseBuilder","ProcessId":14792,"ProcessName":"iisexpress","ThreadId":7,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"SLBLR-LT-209","Log4NetLevel":"ERROR","HttpRequestNumber":7,"HttpRequestId":"c6965a3d-c7ee-4be3-83ae-84250fb34d59"}

Could you please share your source code repository or the branch name, so that we could debug and find out the error ?

Here's my repository: https://github.com/stevemegson/Umbraco-CMS/tree/v8/migration-performance

It looks like you're back into unmodified code with that error, though. The cache is trying to generate clean versions of member names in the same way that document names get cleaned to generate URLs (I don't think we ever use a URL for a member anywhere, but the cache doesn't know that). I'd guess that the most likely reason for surrogate pairs in a member name is someone including emoji.

Thanks Steve for the hint about emojis. I also got the "Surrogate pairs are not supported" error. I removed the emojis from content, and then I successfully upgraded from Umbraco 7 to 8.

Thanks Steve. We were able to locate the emoji in UmbracoNode table and finished with the migration. Will your performance changes be pushed to the main Umbraco repository and be part of any upcoming release ?

Hi Cimplex, I am struck on the same step. Did you find any solution? Thanks