Upgrading your MasterDataServices database and cleaning the tblTransactions table

If you have been running Master Data Services (MDS) you may have noticed the tblTransactions in your MasterDataServices database growing rather large. This is a situation we recently faced. Our tblTransactions was over 100 GB and by far the largest table in the database. We were faced with the challenge of trying to clean up this table. After some research we found that starting with SQL Server 2012 SP1 CU15 there would be built in stored procedures to clean up this table and related tables while maintain referential integrity. We found that for our purposes SQL Server 2012 SP2 CU7 is the build we needed. I scheduled a maintenance window for our test environment and ran the update. Once it was done I connected to the database, navigated to the stored procedures and…nothing. Discussing the issue with Microsoft PFE Josh Gerszweski provided me the answer. With Master Data Services running the cumulative update is not the end of the process but only the beginning.

First you run the cumulative update as normal. If Master Data Services service is running on a different server you will need to run the cumulative update on that server as well.

If you check the stored procedures in the MasterDataServices database you will see that there are no new procedures for cleaning up the tblTransactions table.

The next step is to connect to the Master Data Services Configuration Manager. If you are not familiar with this tool you will have to determine where you have the Master Data Services running. If it is on the same server as the MasterDataService database you can look in C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSConfigTool.exe however if it is running on a different server you will need to look in the previously mentioned location on the application server.

Change to the Database Configuration page, click the Select Database button.

Enter the information to connect to your MasterDataServices database.

You will notice that the configuration manager detects the MasterDataServices database needs an upgrade.

Click the Upgrade Database button...but first you should backup the MasterDataServices database.

Click Next, Next, Finish to complete the upgrade. Now go back refresh the stored procedures in the MasterDataServices database. You will now see mdm.udpTransactionsCleanup available for you to clean up the tblTransactions table.

To make this work you need to know the Model_ID that you need to clean up and give it a CleanupOlderThanDate. It should be noted that this only uses a date not date time. To find your model IDs you can use this query:

SELECT ID, Name FROM mdm.tblModel ORDER BY ID;

Once you have found your model ID and decided on a CleanupOlderThanDate you are ready to run the stored procedure:

EXEC mdm.udpTransactionsCleanup
@Model_ID = [model id from query],
@CleanupOlderThanDate = [date you picked]

If your tblTransactions table is large enough you may encounter issues like filling the transaction log if you try to clean up all the records in one iteration and may need to batch the process to get down to the desired date. Once you have done that you can create a SQL Agent Job to keep the table trimmed to an acceptable size.