none
SCDW Groom Job Fails: The transaction log for database 'SystemCenterReporting' is full RRS feed

  • Question

  • Hi,

    We have Forefront Client Security and the SCDWGroomJob has been consistently failing with the following message in the log:

    06/01/2010 14:50:30,SCDWGroomJob,Error,1,FOREFRONTCLIENT,SCDWGroomJob,DWGroomingStep,,Executed as user: DomainName\UserName. The transaction log for database 'SystemCenterReporting' is full. To find out why space in the log cannot be reused<c/> see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002).  The step failed.,00:00:02,17,9002,,,,0

    When I check the log_reuse_wait_desc column in sys.databases, it says "Nothing". (It actually says nothing when I run it, so I assume it has nothing to report).

    >>I've backed up the database and backed up the log. I've given the log more space. I've tried with SQL 2005 Standard set to Full or Simple. I"ve shrunk the files through command line and truncated the log, with still the same error.

    When I check the free space for the replog file I see that 2977MB are allocated with 640MB Free Space (21%).

    I've tried changing the groom statistics in the Admin Console from anywhere from 1-60 days with no difference.

    Tuesday, June 1, 2010 10:35 PM

Answers

  • Nick, thanks for the help, the latency switch by itself didn't fix my issue but it was one of the steps:

    The fix was this:

    Uninstall SQL and MOM Reporting from Control Panel Programs and Features and delete the databases

    Reinstall SQL Reporting through SQL Install and MOM Reporting from Forefront Client Security Server installer

    Then follow the steps in this article:

    http://support.microsoft.com/kb/899158/en-us

    to move smaller amounts of data by using the /latency switch until it's as low as it will go.

    Then manually running the SystemCenterDTSPackageTask from Task Scheduler (moved the data from OnePoint to SystemCenterReporting, and after this step, showed the 14 day reports).

    • Marked as answer by Donia Strand Tuesday, August 31, 2010 10:35 PM
    Tuesday, August 31, 2010 10:35 PM

All replies

  • Wednesday, June 2, 2010 5:42 AM
    Moderator
  • The first link does not describe the issue I am having.

    I've been running that script in the 2nd link for days now (it will only take increments of a day or two). I've gotten from 395 days of data down to 277.  If I try to up the days groomed I still get the "system center reporting log full error". Is there any way to speed this up?

    Monday, June 7, 2010 3:38 PM
  • Hi,

     

    Thank you for the update.

     

    To try to correct the problem, you may refer to KB899158 (http://support.microsoft.com/kb/899158) including increasing the size of the transaction log and using the /latency switch to migration parts of the older data in an effort to the get the MOM DTS job back on track.

     

    Regards,


    Nick Gu - MSFT
    Tuesday, June 8, 2010 5:25 AM
    Moderator
  • Nick, thanks for the help, the latency switch by itself didn't fix my issue but it was one of the steps:

    The fix was this:

    Uninstall SQL and MOM Reporting from Control Panel Programs and Features and delete the databases

    Reinstall SQL Reporting through SQL Install and MOM Reporting from Forefront Client Security Server installer

    Then follow the steps in this article:

    http://support.microsoft.com/kb/899158/en-us

    to move smaller amounts of data by using the /latency switch until it's as low as it will go.

    Then manually running the SystemCenterDTSPackageTask from Task Scheduler (moved the data from OnePoint to SystemCenterReporting, and after this step, showed the 14 day reports).

    • Marked as answer by Donia Strand Tuesday, August 31, 2010 10:35 PM
    Tuesday, August 31, 2010 10:35 PM
  • Uninstalling is unnecessary.  You need to set the log files to 50% of the database size.
    MCP, MCTS, MCSA,MCSE
    Wednesday, September 1, 2010 1:17 PM
  • Yes, these are the default log settings:

    Collection (OnePoint)
     15 gigabytes (GB) data
     Log: 20% of data

     
    Reporting (System Center Reporting)
     1 GB data
     Log: 50% of data
    This did not help in this case--it had been at 50%. Changing log sizes had no effect on the error SystemCenterReporting transaction log is full and the groom job failing. Enlarging the log did not help.

    In this article:

    http://blogs.technet.com/b/fcsnerds/archive/2008/09/25/fcs-with-mom-2005-database-guidance.aspx

     Here is what it says about the log:

    The SystemCenterReporting DB transaction log file is used in the data transfer and needs to be approximately 5x (based on real world example) the size of the OnePoint DB.

    The log showed as having lots of space, and yet the groom job would say it would fail because the log was full. Other people with this issue on the forums fixed it be reinstalling both MOM reporting and SQL Reporting as I did.

    Wednesday, September 1, 2010 3:54 PM