How to cleanup transaction logs, Validation issues and Staging tables

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

First published on MSDN on Apr 14, 2015

Master Data Services till recently didn’t have a supported way to clean the transaction logs, validation issues history and Staging tables. For a MDS system with lot of data changes and ETL processes over the period these tables can grow exponentially and lead to performance degradation and storage space issues.

To overcome this problem in “ Cumulative update 15 for SQL Server 2012 SP1 we are providing some helper Stored Procedures which users can call to clean the tables.

What is cleaned?

All the transaction history older than specified date, all the validation issues history older than specified date and all the staging batches which ran before specified date.

How:

You can call the Stored Procedures manually which will clean up the tables. To clean Transaction tables use mdm.udpTransactionsCleanup, to clean Validation History use mdm.udpValidationsCleanup and to clean staging table use mdm.udpEntityStagingBatchTableCleanup.

Sample:

--@ModelID is the model ID that you clean up the log for.

--@CLeanupOlderThanDate is the date before that the logs or records are deleted.

DECLARE @CleanupOlderThanDate date = '2015-01-01',

@ModelID INT = 7     --You can get this fromtable mdm.tblModel

--Cleanup Transaction Logs

EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;

--Cleanup Validation History

EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;

--Claenup EBS tables

EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

----------------------

You can also run the cleanup on predetermined schedule by setting up a SQL Agent Job which triggers in regular intervals to call the stored procedures to clean up the data.  You can use SQL Server Management Studio to make any changes to this scheduled job.

Upcoming Releases:

These stored procedures will also be release as part of the upcoming cumulative update for SQL Server 2014.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.