SQL Master Data Services database has huge leaf member staging table - can I purge old records?
4
votes
1
answer
2805
views
One of our SQL instances has a large MDS database, and virtually 100% of the size is due to a 300+ million row table named
stg.Stg_Project_Leaf
. It recently started blowing up our weekly index rebuilds, so I need to figure this out.
From what I can tell, this is a [Leaf Member Staging Table](https://technet.microsoft.com/en-us/library/ee633854(v=sql.110).aspx) for a "Project" entity, set up and automated (by a prior consultant) as a part of an ETL process that builds BI cubes/reports.
Looks like it has been processing about 10k rows per half hour for the past *several years* and has never been purged. When I check the ImportStatus_ID
, though, I see 0 rows waiting to be processed. (Most are 1 - succeeded, less than 1% are 2 - failed.)
**Can I just TRUNCATE
this table?**
This system is being used in an automated fashion, its not like people are logging into the MDS console and individually monitoring and rolling back changes. I found some references to stored procedures that purge these tables (mdm.udpStagingClear
or maybe mdm.udpDeletedMembersPurge
), but I'm not sure which of those is the one I'd need, nor do I know if they would blow up the log if they tried to do 300m rows at once.
Asked by BradC
(10023 rep)
May 15, 2017, 09:23 PM
Last activity: May 17, 2017, 10:08 PM
Last activity: May 17, 2017, 10:08 PM