I'm trying to upgrade our 2012 Master Data Services database to 2016. I've taken a backup of the MasterDataServices database from the 2012 box and restored it to the 2016 box. Utilizing Master Data Services Configuration Manager, I have selected
the database and it says "This database requires an upgrade. You cannot change system settings until the database is upgraded". So, I clicked upgrade database. It runs for approx 1 hr before giving the below timeout message.
I can see the query that runs almost that entire time is inserting into [mdm].[tbl_3_TR].
I've tried increasing the timeout settings on the original MDS database - then doing another backup and restore. This does not seem to change anything. I've tried restarting services after the database is restored to no avail. I watched
what queries were being executed and it seems to be hanging on the below query. Next, I am trying to back up the transaction log and setting the growth to 100MB as opposed to 10% (will run over the weekend to see if that works, but I am not hopeful at
this point).
Any ideas?
Below is the error:
Microsoft.MasterDataServices.Configuration.ConfigurationException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.ProcessSqlStringWithTransaction(String commandSql, SqlConnection connection, SqlTransaction upgradeTransaction)
at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.UpgradeMasterDataServicesDatabase(SqlConnection connection, String databaseName, Collection`1& businessRuleExclusionWarnings)
at Microsoft.MasterDataServices.Configuration.Commands.UpdateMasterDataServicesDatabase.InternalProcess()
at Microsoft.MasterDataServices.Configuration.Commands.SqlCmd`1.Execute()
at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
--- End of inner exception stack trace ---
at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
at Microsoft.MasterDataServices.Configuration.ConfigurationAdministration.UpgradeDatabase(DatabaseInformation databaseInformation)
at Microsoft.MasterDataServices.Configuration.UI.MdsDatabase.UpgradeDatabase()
Below is the query it seems to be hanging up on:
(@Model_ID INT) INSERT INTO [mdm].[tbl_3_TR] ( [Version_ID], [TransactionType_ID], [OriginalTransaction_ID], [Hierarchy_ID], [Entity_ID], [Attribute_ID], [Member_ID], [Member_MUID],
[MemberType_ID], [MemberCode], [OldValue], [OldCode], [NewValue], [NewCode], [Batch_ID], [EnterDTM], [EnterUserID], [LastChgDTM], [LastChgUserID], [OldTransaction_ID] )
SELECT Transactions.[Version_ID], Transactions.[TransactionType_ID], Transactions.[OriginalTransaction_ID], Transactions.[Hierarchy_ID], Transactions.[Entity_ID],
Transactions.[Attribute_ID], Transactions.[Member_ID], 0x0, Transactions.[MemberType_ID], Transactions.[MemberCode], Transactions.[OldValue], Transactions.[OldCode], Transactions.[NewValue], Transactions.[NewCode], Transactions.[Batch_ID],
Transactions.[EnterDTM], Transactions.[EnterUserID], Transactions.[LastChgDTM], Transactions.[LastChgUserID], Transactions.[ID] FROM [mdm].[tblTransaction] Transactions INNER JOIN
[mdm].[tblModelVersion] Versions ON Versions.ID = Transactions.Version_ID WHERE Versions.Model_ID = @Model_ID; --Lookup member MUIDs (which were not stored in the original transaction table).
DECLARE @Entity_IDs TABLE(Entity_ID INT, MemberType_ID INT, MemberTableName NVARCHAR(MAX)); WITH entityMemberTypeCte AS ( SELECT DISTINCT
Entity_ID ,MemberType_ID FROM mdm.[tbl_3_TR] ) ,withTableNames AS (
SELECT cte.* ,CASE cte.MemberType_ID WHEN 1 THEN e.EntityTable
WHEN 2 THEN e.HierarchyParentTable WHEN 3 THEN e.CollectionTable END AS MemberTableName
FROM entityMemberTypeCte cte INNER JOIN mdm.tblEntity e ON cte.Entity_ID = e.ID ) INSERT INTO @Entity_IDs
SELECT * FROM withTableNames WHERE LEN(MemberTableName) > 0 DECLARE @Entity_ID INT ,@MemberType_ID
INT ,@MemberTableName NVARCHAR(MAX) ,@SQL NVARCHAR(MAX); WHILE EXISTS(SELECT 1 FROM @Entity_IDs) BEGIN SELECT TOP
1 @Entity_ID = Entity_ID ,@MemberType_ID = MemberType_ID ,@MemberTableName = MemberTableName
FROM @Entity_IDs; SET @SQL = N' UPDATE tr SET Member_MUID = mem.MUID
FROM [mdm].[tbl_3_TR] tr INNER JOIN mdm.' + QUOTENAME(@MemberTableName) + N' mem ON tr.Member_ID = mem.ID
AND tr.Version_ID = mem.Version_ID WHERE tr.Entity_ID = @Entity_ID
AND tr.MemberType_ID = @MemberType_ID '; EXEC sp_executesql @SQL, N'@Entity_ID INT, @MemberType_ID INT', @Entity_ID, @MemberType_ID;
DELETE FROM @Entity_IDs WHERE Entity_ID = @Entity_ID AND MemberType_ID = @MemberType_ID; END;-- WHILE
