locked
Table growing !!! RRS feed

  • Question

  • Hello,

    I was running this query:

    Simple query to display large tables: 
    
    SELECT 
    so.name, 
    8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, 
    Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb, 
    Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb 
    FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id) 
    WHERE 'U' = so.type GROUP BY so.name  ORDER BY data_kb DESC 
    

    and apparently I have tables out-of-control:

    SC_EventParameterFact_Table	409,424,680	0	0
    SC_EventFact_Table	264,628,848	51,164,544	0
    SC_ClassAttributeInstanceFact_Table	11,178,672	4,464,880	0
    SC_RelationshipInstanceFact_Table	2,119,288	474,384	0
    SC_ClassInstanceFact_Table	617,568	196,096	0
    SC_ComputerToComputerRuleFact_Table	522,864	283,336	0
    SC_AlertHistoryFact_Table	228,608	23,536	0
    SC_SampledNumericDataFact_Table	201,960	108,232	0
    SC_AlertFact_Table	114,208	33,712	0
    

    409 millions in SC_EventParameterFact_Table and 260 millions in SC_EventFact_Table does not seem right, isn't it?

    I have a grooming on 14 days only !!

    What is the next step to clean up this?

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Monday, March 4, 2013 5:13 PM

All replies

  • Hi

    Thank you for the post.

    Would you please elaborate the issue? Does SystemCenterDTSPackageTask fail to run? Do you want to clean up the history data?

    Regards,


    Nick Gu - MSFT

    Wednesday, March 6, 2013 8:24 AM
  • Hello,

    yes to both questions, SystemCenterDTSPackageTask failed filling up the systemcenterreporting.ldf file within 6 hours...

    yes I need to clean 2012 as the database over 1 TB is non-manageable anymore for 9,000 machines

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Thursday, March 7, 2013 12:36 AM
  • Hi,

    Thank you for the post.

    Please refer to this thread: http://social.technet.microsoft.com/Forums/en-US/Forefrontclientreporting/thread/ec89fadd-4ff5-4dfd-9f68-2cc9cc856a35/

    Regards,


    Nick Gu - MSFT

    Tuesday, March 12, 2013 3:53 AM
  • hi Nick,

    This article was already used and no benefiot out of if. So the only way so far has been to kill all 2012 entries and regenerates all 2013 enteries and so far it comes 50% good ... waiting for the next runs...

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Tuesday, March 12, 2013 11:17 PM