Database growing rapidly

[tamil_arasan]

Hi Experts,

We are using Windows SQL server 2008 and ECC 6.0 EHP 4,since last two months the database growth is in 7 GB or 8 GB per day.

We have not been scheduled daily backup of log files and datafiles.The house keeping jobs are running at Application level like ABAP dumps,Spool request clearence etc on daily basis even TST03 table is largest growing table in the system.

Please help me to fix this.

Thanks in advance.

Regards,

Tamil

[Former Member · Accepted solution]

Hello,

For Technical Tables growth you can check the SAP Note 706478.

For other DB Tables I would suggest you to go to DBACOCKPIT and analyze the topmost tables and check with the application team with their functionality and whether any abnormal activity is taking place in those SAP Areas.

Note 706478 - Preventing Basis tables from increasing considerably

Regards,

Mudasir

[tamil_arasan · Accepted solution]

Hi Mudasir,

    Top three tables are TST03,BALDATA,BALHDR.I am analyzing the note which is suggested by you and i already deleted expired logs by SLG2 even the size is in high.

regards,

Tamil

[former_member188883]

Hi Tamil,

1) Check the variant for spool reorg jobs RSPO1041 and RSPO1043. Ensure that you have selected appropriate checkbox and updated number of days to 7 or lesser.

2) You may also manually delete the old spools using SP01 and SP02 transaction codes.

3) Once the deletion is done you may perform reorg of the database table using SQL editor as follows

        EXEC sp_help TST03

       dbcc dbreindex ('TST03', ") with no_infomsgs

       GO

       dbcc updateusage ('SID','TST03')  <----SID is your instance SID

      GO

4) As a best practise ensure that RSPO1041 ( Spool deletion) and RSPO1043( Spool consistency check) reports are running with updated variants. You may keep number of days as 3 based on business requirement.

Regards,

Deepak Kori

[tamil_arasan]

Hi Deepak,

Finally i have deleted some entries by SP01 tcode now should i do reorg table?

Regards,

Tamil

[former_member188883]

Hi Tamil,

As per the my response above you may perform reorg as follows

3) Once the deletion is done you may perform reorg of the database table using SQL editor as follows
        EXEC sp_help TST03
       dbcc dbreindex ('TST03', ") with no_infomsgs
       GO
       dbcc updateusage ('SID','TST03')  <----SID is your instance SID
      GO

Replace <SID> with Database SID.

Regards,

Deepak Kori

[tamil_arasan]

Hi Deepak,

I  got error messages like no table TST03 exists in database.

Please clarify do we need down time of SAP to reorgnize the table at DB level?

Do we need space in data files?

Regards,

Tamil

[former_member188883]

Hi Tamil,

Refer link http://scn.sap.com/thread/1708897 for steps to perform reorg.

Refer link http://technet.microsoft.com/en-us/library/ms189858.aspx
for steps on performing index rebuild which also is a form of reorg.

[tamil_arasan]

Hi Deepak,

I came to know that we need equal free space of table size in hard drive(I mean to reorg 20GB table, we need 20GB free space in the hard disk) by sdn thread link which is provided by you but unfortunately we don't have equal free space of TST03 table.

Is there any alternative way to recover the space?

Regards,

Tamil

[former_member188883]

Hi Tamil,

Yes you are right. Other Alternative is to perform database export and import to regain the space.

Regards,

Deepak Kori

[Former Member]

Hi Tamil,

If you can't regain the space yet, by running the deletion program now you will stop the table growing and give you some time.  You need to do this right now to get some control plus find out exactly what is causing so many spool files to be created.  Have a look at the spool by size of file and see if there are some very large files you can delete directly.

Regards,

Graham

[tamil_arasan]

Hi Graham,

Yes, one spool request has around 500 pages which is creating by backgroundjob with 15 min frequency.Before the table size was 130GB, we have deleted by SPAD-->administration-->spool-clean up.current size of TST03 table is 51GB.first we want to recover this 80 GB for database usage.

The same job also updating BALDAT and BALHDR tables these are next largest tables.we have deleted logs by SLG2 transaction code.

Regards,

Tamil

[Former Member]

Hi Tamil,

You are going to need to add a disk to this server in order to export the table.  Can you not mount a disk from another file server to do this?

Regards,

Graham

[tamil_arasan]

Hi Graham,

Thanks for your precious inputs and help!

Finally we have found easiest way to recover the space.I got help from one expert to recover the space.first we deleted all the data of TST03 by SE14 in quality system then we shrink the whole database by management studio.

We didn't shutdown the SAP application and we were watching the free space level after 3 or 4 hour, the free space on the disk increased around 73GB(note:TST03 table in QAS system was 72GB before delete the data).QAS system is up and running as usual after recovered the space.

Now we planned to do it on production system.

Thanks all for their most valuable inputs and guidance!.

Regards,

Tamil

[Former Member]

Hi,

Please check the Note 10551 - Table TST03 grows

I hope it will help you to fix your issue.

Thanks and Regards,

Vimal

[Former Member]

Hi Tamil,

How many records are in TST03 table then?  Are the reports very large? 

You say you are not doing backups - did you mean you are or are not?

What has changed over the last two months?  Have you had an upgrade?  Have you had a new set of users added?  Have you turned on new functionality?

Regards,

Graham

[tamil_arasan]

Hi Graham,

   Currently rows in TST03 table is 13,315,978, log file/data file backup on daily basis would stop database growth?

No we haven't upgraded anythning and turned on new functionality such traces.

Regards,

Tamil

[Former Member]

Hi Tamil,

Wow - I've never seen a TST03 table with that many records!

You really need to run the delete programs to get rid of all the old spools now as recommended by the others in this thread.  As you have so many, then you will need to run this in a number of cycles, trying to delete several thousand at a time otherwise it will timeout.  It's going to take some days to delete the records, so plan to start now and keep running the delete program with differing selections over the coming week.

Once the records are deleted, then the db will stop growing in this area.  Thereafter you will need to reorganise the table with an export/import (can use the brtools for this) to recover the space

Regards,

Graham

[anja_engelhardt2]

moved to SAP NetWeaver Application Server by moderator