locked
SCDWGroomJob Not Running and Forefrnt DB Server disk Filling Up RRS feed

  • Question

  • I've had an on going problem with the DTSTask not running; I add space to the SystemCenterReporting DB and it runs and completes.  Now I am running out of disk space. My SystemCenterReporting Datbase is at around 90 Gigs. I found this article about grooming the SystemCenterReporting DB:

    http://ops-mgr.spaces.live.com/Blog/cns!3D3B8489FCAA9B51!147.entry

    After reading this, I realized that my SCDWGroomJob has not been running in a long, long time.   Now when I try to run it, I receive the following error:

    Date  10/11/2010 7:10:24 AM
    Log  Job History (SCDWGroomJob)

    Step ID  1
    Server  DWTFRNTDB
    Job Name  SCDWGroomJob
    Step Name  DWGroomingStep
    Duration  00:08:28
    Sql Severity  21
    Sql Message ID  3314
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    Executed as user: DWT-DOMAIN\DZ031562. ...ve been terminated by the server. [SQLSTATE HY000] (Error 0)  The transaction log for database 'SystemCenterReporting' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002)  The transaction log for database 'SystemCenterReporting' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002)  During undoing of a logged operation in database 'SystemCenterReporting', an error occurred at log record ID (8007:937300:238). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314)  The log for database 'SystemCenterReporting' is not available. Check the event log for related error messages. Resolve any errors and restart the database. [SQLSTATE HY000...  The step failed.

    I can add more space to any of the temp databases to get this to run and then begin to groom the SystemCenterReporting Database down to a more reasonable size but which TEMPDB should I add more space to?   TempDB or ReportServerTempDB??

    If I can get the SCDWGroomJob to run at least once, I think I can get a starting point for beginning to groom the SystemCenterReporting Database per the article linked above.

    Thank you for the help,

    Dave Zuver

     ADDITION:

    I am also trying to run this script as recommended in a KB article but it also times out.  I think my SystemCenterReporting DB is just too large.  It hasn't been groomed since 11/2009.  Is there a way to just delete the Database or purge it and start again ???

     

    Declare

     

    @tempdays int

    Declare

     

     

    @daysdiff int

    Declare

     

     

    @dayskeep int

     

    set

     

    @daysdiff = 1

    set

     

    @dayskeep = 160

     

    :

     

    select

     

    @tempdays = datediff(dd, min(timestored), getdate()) from sdkeventview

    set

     

    @tempdays = @tempdays - 1

     

    set

     

    @tempdays = @tempdays - @daysdiff

    select

     

    @tempdays

    IF

     

    (@tempdays < @dayskeep)

    BEGIN

    select

     

    'Finished Grooming'

    return

    END

     

    select

     

    'start grooming in a new loop'

    select

     

    @tempdays

     

    IF

     

    (@tempdays > @dayskeep)

    BEGIN

    Exec

     

    p_updategroomdays 'SC_AlertFact_Table', @tempdays

    Exec

     

    p_updategroomdays 'SC_AlertHistoryFact_Table', @tempdays

    Exec

     

    p_updategroomdays 'SC_AlertToEventFact_Table', @tempdays

    Exec

     

    p_updategroomdays 'SC_EventFact_Table', @tempdays

    Exec

     

    p_updategroomdays 'SC_EventParameterFact_Table', @tempdays

    Exec

     

    p_updategroomdays 'SC_SampledNumericDataFact_Table', @tempdays

    END

     

    exec

     

    dbo.p_GroomDatawarehouseTables

    select

     

    getdate()

    DBCC

     

    opentran

    goto

     

    Groom_Again

     

     

     

     

    Groom_Again

    • Moved by Miles Zhang Thursday, October 28, 2010 3:17 AM (From:Forefront Client Security Setup and Configuration)
    Monday, October 11, 2010 3:39 PM

Answers

All replies

  • I bumped the SystemCenterReportingLog to 35 gigs and ran the above grooming query again; this time it took three hours to give me the error about the SystemCenterReportingLog is full.  How much do I need to give it, anyway?

    Also, to my orginal question, can I just clear it out completely and start new?   I don't care about all that past data anyway.

    Thanks

    Tuesday, October 12, 2010 10:03 PM
  • Hi,

     

    Thank you for the post.

     

    Please refer to this post: http://social.technet.microsoft.com/Forums/en-US/Forefrontclientgeneral/thread/f824e05e-03fb-425b-acfb-ff09945983aa

     

    Regards,

    ========

    After receiving a lot of feedbacks from the community, it was decided to conduct the Forefront Products and Technologies Forums consolidation to improve forum discoverability and reduce customer efforts. This forum will be locked down at the end of Oct. For continued information about Forefront Client Security Setup and Configuration, please post to Forefront Client Security General forum at: http://social.technet.microsoft.com/Forums/en-US/Forefrontclientgeneral/threads. On Oct 25<sup>th</sup>, forum engineers will move any new threads to the Forefront Client Security General forum.

     

    Please post a reply to this announcement if you have any feedback on this decision or the process. Thank you for your understanding.


    Nick Gu - MSFT
    • Marked as answer by Miles Zhang Tuesday, October 26, 2010 6:48 AM
    Friday, October 15, 2010 7:11 AM