2

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 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.

  • Can you? Sure. Should you? That's a question you should run by the business. Any ability to test out the truncate in a Test environment? I've yet to work in an environment where MDS is used as I think MS originally intended it, so this scenario doesn't shock me at all. – John Eisbrener May 16 '17 at 16:17
  • @JohnEisbrener But clearing this data staging table shouldn't impact the business in my case, right? Nobody here is using the MDS GUI to manually review and roll back old batches or anything, all they care about is whether their reports get this particular "project" data updated daily, and if I'm understanding things right, purging old import batches shouldn't effect that at all. My main concern is not breaking MDS if I'm not supposed to be messing with the "back end" database, but none of the documentation or tutorials I can find seem to address this. – BradC May 16 '17 at 16:33
  • I don't know your environment well enough to say it won't impact business, but I have done direct data manipulation to underlying MDS tables as it made more sense in my situation than going through the GUI. Test it out in a lower environment if possible and see if anything breaks. – John Eisbrener May 16 '17 at 16:39
2

In short: The official answer is to use the new proc mdm.udpEntityStagingBatchTableCleanup to clean up entity staging tables, but in practice, DELETE or TRUNCATE should work just fine (or might be necessary), as long as you understand your batch detail.


I found reference to a SQL 2012 patch that adds 3 stored procedures to MDS, including the one I need:

--Cleanup entity-based staging table
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

See also this article for scheduled cleanup options utilizing the same procs.

For my purpose, though, I was still concerned about blowing up the tran log with the 300+ million rows that had to be deleted, so I looked in detail at the inner workings of mdm.udpEntityStagingBatchTableCleanup. This stored proc does the following:

  1. Populates a temp table with all completed batches that match your deletion criteria (for the given @Model_ID, older than @CleanupOlderThanDate).
  2. Builds dynamic DELETE statements for the corresponding Leaf, Consolidated, and Relationship tables for each DISTINCT Entity_ID in the temp table.
  3. It executes each of those DELETE statements for each entity table, filtered via a join to the temp table.
  4. It then deletes from mdm.tblStgBatch itself, again filtered by join to the temp table.

There is no attempt to limit the amount of rows deleted at once, and in my case, the 300+ million rows are all for a single Entity_ID, which would likely blow up the transaction log.

The good news, though, is that if I intend to remove all Entity_IDs and all dates anyway (which I do), then that's functionally equivalent to a TRUNCATE, as long as I am careful to not attempt it right in the middle of an active batch.

Finally, while I couldn't find any official MS sources that explicitly recommended truncating these tables, I did find several articles where DBAs do a routine TRUNCATE at either the beginning or the end of their import process:

So my plan is to do an initial TRUNCATE, and then schedule mdm.udpEntityStagingBatchTableCleanup to run on a periodic basis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.