Upgrade from 2012 to 2016 timeout RRS feed

  • Question

  • 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      

    Friday, July 26, 2019 7:49 PM

All replies