Removing the Failed Instances Manually From the Database

6 Replies

The error in BO XI 3.1 SP3 causes millions of failed instances generated (SAP note 1448881 “Multiple instances spawned after daylight savings time change”). If the problem is not solved timely, the BO is getting slow, services is starting to fail often. It is usually possible to remove the instances programmically (SAP note 1568718 “How to delete all failed instances in XI3.1 programmatically”), however this script does not work if the number of the failed instances large – millions of records.

SAP note 1654183 “How to delete all the failed instances in SAP BusinessObjects Enterprise manually” is not that clear and more over it is incorrect. The correct script (for Oracle) is given below.

1. Stop SIA.
2. Make backup of CMS database and FileStore folder.
3. Execute the following script:

CREATE TABLE cms_infoobjects6_temp AS (
    SELECT * FROM cms_infoobjects6
    WHERE schedulestatus != 3 
    OR schedulestatus IS NULL);

TRUNCATE TABLE cms_infoobjects6;

INSERT INTO cms_infoobjects6 
    SELECT * FROM cms_infoobjects6_temp;

COMMIT;

DROP TABLE cms_infoobjects6_temp;

4. Start SIA and test the system.

6 thoughts on “Removing the Failed Instances Manually From the Database”

  1. banesusa

    I’ve experienced this problem with BMC Analytics for BSM in a test enviroment that had been down for months.

    Very useful script, thanks!

    Like

    Reply ↓
  2. Alexandre Quinas

    WOW!
    You saved my day!
    I’ve had a CMS DataBase with 40Gb of Used Space, and after this script, I have only 200Mb of Used Space.
    In SQL server it worked fine and I had +20 Million Lines in the table, now It’s just 15k lines!
    If you have SQL Server you just have to do slight modification in the create table code.

    Thanks!

    Like

    Reply ↓
    1. EmmanMash

      Only change you need for BO 4.0. Table now called CMS_INFOOBJECTS7

      SELECT * FROM CMS_INFOOBJECTS7
      WHERE schedulestatus != 3
      OR schedulestatus IS NULL

      Like

      Reply ↓
  3. Ahmed Mohammed

    thanks Guys,

    Worked like Charm for me.

    It reduced the Database size from 25GB to 8.2MB.

    Great Post…

    Regards
    Ahmed Mohammed

    Like

    Reply ↓
  4. Jatinder Singh

    In Sybase SQL anywhere databse it will be

    SELECT * into cms_infoobjects6_temp FROM cms_infoobjects6 WHERE schedulestatus != 3
    OR schedulestatus IS NULL

    Like

    Reply ↓
  5. Eric Gumo

    Thanks for this post we have reduced our DB from 70GB to 200MB

    I also run the optimize table cms_infoobjects6 command at the end

    Like

    Reply ↓

Leave a comment