SOFFCONT1 table size increased abnormally

[Former Member]

Dear Experts,

For the last some days, Table size is getting increase for SOFFCONT1 . Please let's know which reports should be run to empty space,I found below reports for the same, Kindly suggest which is suitable

RSBCS_REORG

RSSODFRE

RSSORESN

RSSODLWF

RSSOREDF

RSSOEXDA

RSSODLIN

RSSOTRCL

Presently, we have

SAP ECC6.0(EHP4), Kernel release-> 701;

SAP_BASIS: Release-> 701; Level-> 0007

Database: Oracle (10.2.0.2)

[Former Member · Accepted solution]

The table SOFFCONT1 , can be deleted and reorganized. There are no archiving strategy for this table.

So you can follow this process:

A) Run report RSBCS_REORG
    Select checkbox
    Reorganize documents with send requests
    Reorganize documents that do not have send requests

    Radio button Specify Age (in Days) 30 - 1000
    Created By: (select small numbers of users from attached worksheet)
    Select check box Detailed Output
    Deselect checkbox Test Mode
    Run as background Job immediate

B) Run report RSSO_DELETE_PRIVATE
     select users  *  (all users)
     Select checkbox for
     Inbox All
     Outbox
     Resubmissions
    Private folders
    provate trash
    Minimum Age (days) 30
    Deselect Test Mode
    keep other fields blank

    Run as background Job
    Period: Daily Job

C) Run report RSBCS_REORG
    Select checkbox
    Reorganize documents with send requests
    Reorganize documents that do not have send requests

    Radio button Specify Age (in Days) 30 - 1000
    Created By : *
    Select check box Detailed Output
    Deselect checkbox Test Mode
    keep other fields blank

    Run as background Job
    Period: Daily Job

D)Create variant and schedule daily job for report RSSOTRCL

    Retention period-30 days,

   Timing - Off business hours

    Frequency - Daily

E)Create variant and schedule job for report RSSODFRE

   Retention period-30 days and for All users (Test Mode should not be selected)

  Timing - Off business hours

   Frequency - Daily

So basically it will delete the Shared folders, hidden folders a data which are linked by RSBCS_REORG but can not be deleted. Better if you run this JOB with a test mode with detailed output so that you can get a clear idea what is the data volume .

Thanks,

Biswajit Sarkar

[Former Member]

Hello SAP experts,

I have a mysterious problem with reclaiming space from SOFFCONT1. Has anyone else witnessed the following situation?

Note 1641830 suggests running RSSO_DELETE_PRIVATE before RSBCS_REORG should give the best results

I ran RSSO_DELETE_PRIVATE and it deleted 799,000 documents. Good news so far!!

I then ran RSBCS_REORG and it deleted 788,000 documents from a total of 875,000, which almost tallies with the number deleted by RSSO_DELETE_PRIVATE. I'd expect that to show a 90% reduction in the number of rows within SOFFCONT1. Even better news!!!

In DB02, SOFFCONT1 had 690,000 rows before I ran the above jobs, which dropped to 571,000 after I ran the jobs. I'd have expected the number of rows to be far smaller, given the good results from the purge jobs. Not so good news!

I reorganised SOFFCONT1 and it claimed back only 33MB from a 20GB table. Dreadful news!

Can anyone tell me why the great results from the purge jobs did not result in any reduction in the size of SOFFCONT1? It's a mystery to me!

Many thanks,

Arwel.

[igor_taratutin]

Arwel,

I've just start doing cleanup of SOFFCONT1 table in test system and found next note very useful:

1641830 - Reorganization of Business Communication Services data (RSBCS_REORG)

After execution of mentioned reports (all of them) SOFFCONT1 table lost half of entries from 700000 to 310000 rows. That's maybe because I've kept only 30 or 60 days of data in test system.

In addition I went to SOST TCode and deleted all sent e-mails older than 30 days (again - just test system/copy of production) and after another RSBCS_REORG execution - SOFFCONT1 went down to 77000 rows! Check your SOST - do you still have all those e-mails hanging around?

In few days I'll try to reorg table and hope to claim more than some Mb out of 17Gb.

Good luck to all of us

Next step - TST03...

[JPReyes]

IMO theres only so much space you can recover from reorganizing SOFFCONT1. In the long run and because the nature of the documents stored in SOFFCONT1 you could setup a repository and archive them.

Regards, Juan

[Former Member]

Dear Juan,

Thanks a lot...

1) would u please let me know  what is IMO in->" IMO theres only so much space you can recove"

2) yes, your understanding is correct, we are considering for reorg tablespace/tables bcoz DB space utilized by LOB segments mainly in tables TST03 & SOFFCONT1 . So before reorgnizing, we want to execute jobs/reports responsible for deleting unwanted data from these tables , then we'll go for reorging activity.

For TST03 SAP standards jobs are in place, so no issue..

For SOFFCONT1, we aren;t able to find reports. Please help us

Looking forward for your co-operation....

Regards

Sumit

[JPReyes]

IMO = In my opinion 

For reorg of SOFFCONT1 the notes above are as good as it gets. My suggestion is that you should consider in the future (depending on the volume) to set a content reposirory to archive the SAP Office docs.

Regards, Juan

[Former Member]

Dear Juan,

Ok, we'll think about content server option...

one more help required, I have some queries regarding Reorg database, Can i ask if you allow me...

[Former Member]

Hi,

Please, Can I have your response to my queries !!!!

[former_member189725]

Once you delete table entries of SOFFCONT1 , you will have to do a table reorg to release space out of the segment .  Else you see the table still occupying space . Since this table I believe will have LOB field , hence take special care while doing a reorg .

Follow SAP note Note 646681 - Reorganizing tables with BRSPACE

if your database is Oracle.

Follow this link http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.d...

for DB2 LUW database.

Regards

Ratnajit

[Former Member]

Hi,

Thankyou for SAP NOTE 646681

Right, your understanding is correct, we are considering for reorg tablespace/tables bcoz DB space utilized by LOB segments mainly in tables TST03 & SOFFCONT1 .

We have ORACLE as database.

but i think it's safe if we go using BRTools,  plz guide us steps for the same...

[Former Member]

Hi,

1.) Pls find DDL for table to analyze :

SQL> set long 5000

SQL> select DBMS_METADATA.GET_DDL ('TABLE','SOFFCONT1', 'owner _name') from dual;

DBMS_METADATA.GET_DDL('TABLE','SOFFCONT1', 'owner _name')

--------------------------------------------------------------------------------

CREATE TABLE "owner _name"."SOFFCONT1"

   (    "MANDT" VARCHAR2(9) DEFAULT '000' NOT NULL ENABLE,

"RELID" VARCHAR2(6) DEFAULT ' ' NOT NULL ENABLE,

"PHIO_ID" VARCHAR2(96) DEFAULT ' ' NOT NULL ENABLE,

"SRTF2" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,

"PH_CLASS" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

"CLUSTR" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

"CLUSTD" BLOB

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 522240000 NEXT 1048576 MINEXTENTS 1

DBMS_METADATA.GET_DDL('TABLE','SOFFCONT1','SAPPRD')

--------------------------------------------------------------------------------

MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "PSAPPRD"

LOB ("CLUSTD") STORE AS (

  TABLESPACE "PSAPPRD" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10

  CACHE

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

DBMS_METADATA.GET_DDL('TABLE','SOFFCONT1', 'owner _name')

--------------------------------------------------------------------------------

above output suggesting PCTVERSION 10, Is there anything to do with PCTVERSION

2.) If go by below SQL query to know PCTVERSION, output is "no rows selected", WHY???. Although it's reflecting for same table in same column

SQL> select nvl(to_char(pctversion),'NULL') pctversion,nvl(to_char(retention),'NULL') retention

from dba_lobs where segment_name = '<SYS_LOB0000009903C00007$$>';  2

no rows selected

3.)  what precautions should we take while doing reorg.. via BRtools..

[Former Member]

Dear All,

Can I have response please....

[JPReyes]

I don't understand your questions... SAP Note 646681 describe the reorganization in detail using brtools.

Regards, Juan

[former_member189725]

Please refer the following SAP notes to reduce size of SOFFCONT1.

1634908  - Reduce the size of table SOFFCONT1

1641830  - Reorganization of Business Communication Services data (RSBCS_REORG)

Typically you should use RSBCS_REORG report.

Regards

Ratnajit

[Former Member]

Hi Ranjit,

Thanks for information,

We are checking from our end..'ll revert to you

[Former Member]

Dear Ratnajit,

You are right, beside above mentioned report, We require to run RSSO_DELETE_PRIVATE & RSSODFRE.

Thanks a lot.....