locked
MESSAGE_LOG Table in DB Huge RRS feed

  • Question

  • Well, it seems our vendor turned on debug logging on the mgmt/streaming server and didn't configure any grooming settings. After 2 years, this table now has 65 million (yes!) rows and is 20 GB is size. I'd only like to keep the last 6 months worth of data for the time being. I've tried setting the keep usage value to 6 months and running the job but after 6 days, it's still running. Having had a look at the table structure it appears it's completely flat and has no index so I'm probably doing a full table scan for each lookup of a row (the clean_usage stored proceedure checks each row against a defined cutoff date to see if it should be deleted). The only thing I can think of now is to run a truncate on this table but then I will loose all the data there-in. Any other ideas?
    Wednesday, August 7, 2013 7:21 PM

Answers

  • Unfortunately, i had no other option but to truncate the table.
    • Marked as answer by shocko-tnet Friday, August 16, 2013 12:36 PM
    Friday, August 16, 2013 12:35 PM

All replies

  • You're probably better off asking this question in the SQL Server forums.


    Please remember to click "Mark as Answer" or "Vote as Helpful" on the post that answers your question (or click "Unmark as Answer" if a marked post does not actually answer your question). This can be beneficial to other community members reading the thread.


    This forum post is my own opinion and does not necessarily reflect the opinion or view of my employer, Microsoft, its employees, or other MVPs.

    Twitter: @stealthpuppy | Blog: stealthpuppy.com | The Definitive Guide to Delivering Microsoft Office with App-V


    Thursday, August 8, 2013 6:57 AM
    Moderator
  • Thanks Aaron,

    Agreed.

    Thursday, August 8, 2013 8:50 AM
  • Unfortunately, i had no other option but to truncate the table.
    • Marked as answer by shocko-tnet Friday, August 16, 2013 12:36 PM
    Friday, August 16, 2013 12:35 PM