locked
MDS Database Cleanup and Truncation RRS feed

  • Question

  • We're doing some cleanup on a large MDS database. We have deleted 20-30 models which represent close to 70 million transactions. However, tblTransaction has not reduced its record count, and the database has grown considerably after the model deletions (from 50GB to ~120GB).

    Is there any method to remove the transactions from the deleted models and increase free space?

    We don't plan to shrink the DB file due to fragmentation issues, but think that performance will increase with less extraneous records in the database and some index rebuilds.

    Tuesday, October 7, 2014 2:22 PM

Answers

All replies

  • Hi,

    Unfortunately not a reply, just a completion, since I experience the same issue on another level. When using the staging procedure setting Import_Type to 4 or 6 (hard-delete) data is added to the transaction table and not removed as stated.

    Best Regards,
          Per

    Friday, November 14, 2014 8:52 AM
  • When you call the staging procedure are you setting the LogFlag to 0?

    The staging procedure called with LogFlag = 0 should not log the transactions.

    Saturday, November 15, 2014 3:18 AM
  • Hi Ed,

    Using the LogFlag = 0 effectively prevents more entries from being added to the transaction log, but the previous transactions on that specific code remain however. This is not an expected behavior based on:
    http://msdn.microsoft.com/en-us/library/ee633854(v=sql.110).aspx

    wherein it is stated under Import Type:
    6: Permanently delete the member, based on the Code value. All attributes, hierarchy and collection memberships, and transactions are permanently deleted.

    During a user test a very large amount of data was inserted into an entity. I can "hard-delete" them with the staging table functionality, but the transactions from insert remains which is very annoying.

    How do I get rid of these?

    Best Regards,

            Per

    Thursday, November 27, 2014 8:15 AM