This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
First published on MSDN on Jun 02, 2015Transactions, Validation Issue and Staging table cleanup:
Master Data Services previous versions 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 these tables can grow exponentially and lead to performance degradation and storage space issues.
In the next release of Master Data Service (which will be part of next SQL Server release SQL vNext) we will provide option to clean these tables on a pre-determined intervals or schedule. Based on the user setting the data in these tables will be truncated.
What is cleaned?
All the transaction history older than specified number of days, all the validation issues history older than specified number of days and all the staging batches which ran before specified number of days.
How:
There will be settings where user can specify for how many days he wants to retain the logs and data in these tables. He can do this in two places. One using a System Setting (LogRetentionDays) which will apply to all the models in the system. Second ways is to set the property at Model level which will override the system level setting.
System Setting:
By default the system setting will be -1 which means do not truncate/clean any tables. If the value is 0 then the tables will retain only today’s data and all the previous days data logs will be truncated. For n > 0 the logs will be retained for n number of days.
To set this value use the MDS Configuration Manger tool and change the setting shown below.
Model level Setting :
By default the model setting is NULL which means the value will be inherited from the System Setting “Log Retention in Days”. If you want to override system setting and NOT clean any logs then set as -1. If you do not want to retain any transaction logs and validation history and already processed staging data then set as 0. For n > 0 the logs will be retained for n number of days. Acceptable values: -1 to 5000.
To Set or Update the Model property in the MDS Web UI go to System Administration -> Manage -> Model and change the property shown below
Scheduler :
When you create the database from Configuration Manager, by default it schedules a SQL Agent Job, “ MDS_<DB Name>_Log_Maintenance ” which triggers every day at 2AM and scans all the models and cleans the table as per the settings above.
If you want to change the frequency of this job then use the SQL Agent -> Job (MDS _<DB Name>_Log_Maintenance ) and update the schedule. You can use SQL Server Management Studio to make any changes to this scheduled job.
How to manually cleanup the tables without setting a recurring cleanup as above
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:
DECLARE @CleanupOlderThanDate date = '2014-11-11',
@ModelID INT = 7
--Cleanup Transaction Logs
EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;
--Cleanup Validation History
EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;
--Claenup EBS tables
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;
More related information can be found at: