cancel
Showing results for 
Search instead for 
Did you mean: 

Rapid DataBase size Increase's nearly 30Gb for SAP B1 8.81 PL5

Former Member
0 Kudos

Hello Experts,

As i am with a big doubt at database tunning level and decreasing data for space allocation in database.As one of my client is using sap b1 8.81 pl5.

The data is from 2013 April 1st to tilldate in the database,the size of the database is 30gb.how can i reduce the database size to certain size or how can shrink my database.As the mdf file is 30Gb and ldf file is 1mb.

According to the forums i have been done process at Administration-General setting-Services-History-Previously it was 10,now i have been changed to 1 and runned the addon at the object updation level.Apparently,the records are cleaned but the size of the database is still in the same process(30Gb).
Aitm-prevoiusly it was around 100000 transaction,but after my updation of the items it came around 25000 and list of the items are 25000
My problem is for 1 year it is around 30gb.what will be the estimation for couple of years.
solution would be greatly appreciated.Kindly,please suggest me the process to do the shrinking and reducing the size of the database.

Accepted Solutions (0)

Answers (9)

Answers (9)

Former Member
0 Kudos

did you consider Data Archive Wizard?   Sandy

Former Member
0 Kudos

Hi Nagarjan,

Thanks for your quick reponse!

I have taken the backup and runned the shrink command ,but the size of the DB is same as before.

Hello experts i can find the huge reserved space and data in the History tables of SAP

For examples,AITW,AITM,ACRD.

But how to reduce my size of the DB.

Solution would be greatly greatly appreciated!!!

Thanks,

Vijay Kumar

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Check SAP note:

1651592 - Unable to reduce the size of the Log file

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hello Haroon

just running the Srink command wouldnt help time to time.

Step 1# after you run the shrink Take Backup of the Database, here instead of FULL back up only take the back up of Log File (twice). (you can overwrite the backup of Log file each time, as sometimes space could be an issue)

Step 2# Run Shrink Command (Twice)

Repeat Step 1 # & Step 2#

now you should be able to see the log life size shrinking each time you do it. do it until you achieve 1 MB Log File size.

PS: for each Database you will find two type of files,*.MDF & *.LDF File (you can search server to find these files locations on server), The Bulge size of the database is mostly because of LDF file as it keeps logging every activity. you data is stored in MDF file (Tables & Values)

by the way, what is the size of your MDF & LDF files?

Cheers

Sandy

Former Member
0 Kudos

Hi,

To reduce the actual database size, your option is to change the number of log from 99 to a lower number. If you don't care about changing history, you can change it to one for example.

Thanks,

Gordon

Former Member
0 Kudos

Hi Taruna & Nagarajan,

Thanks for the reply of post.

As tried all the cases to reduce or shrink the transaction data file.But all the scenario are failed and database size still remains at same size of the transaction(Mdf as 30Gb) and log file is 1 mb.

Is there any solution for rapid growth of the size.

Solution would be greatly appreciated,.hoping for good solution

Former Member
kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Check SAP note :

875512
- Find out each table size (disk space used)

Also check 1783630 - Redundant entries in OILM

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hello Experts,

As my total database size of Mdf file is 30Gb.Is it possible to reduce the size of the database of mdfFile.

Is there any solution to reduce or it is not possible for Mdf File.

Waiting for ur quick response!

Thnaks,
Vijay kumar

Former Member
0 Kudos

Hi,

Plz find the following link:

http://www.sqlservercentral.com/Forums/Topic908307-1550-1.aspx

Thanks

Taruna

Former Member
0 Kudos

Hi Experts,

AITW Table is reserving 6Gb of data with n no of records,when i cleaned the data of the history tables with addon and making my settings in general settings to 1.the records are minimised but the the reserved space and space used by data used are same.

how could i reduce this reserved and data size in terms of kb.

help would be greatly appreicted.

Thanks,

Vijay Kumar

Former Member
0 Kudos

Hello

Run below query in you data base

USEdatabasename

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE databasename

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (databasename_Log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE databasename

SET RECOVERY FULL;

GO

Please first you can check this on test data base if it run properly then you can use the same in Live data base

Thanks & Regards

Pankaj Jha

Former Member
0 Kudos

Hi,

The commands are reducing the size of LogFile of the database.

how about the transaction data of the database.

Hep would be greatly appreciated.

Thanks,

Vijay Kumar

Former Member
0 Kudos

this solved mine, but what is the impact of setting the LOG file to 1 MB?

Thanks for your share.

AHmad

Former Member
0 Kudos

Hi!

Please refer to SAP note 548772 it  has some information on how to improve performance in general; check antivirus software, truncate the database log and set auto-shrink to on. The note has more detail on how to follow each of these steps.

There is also the Performance landing page which provides other resources: Go to service.sap.com/smb/sbo -> Click Support on the left, and then "Support Self Help and Library". Then expand the Performance section and click through to the landing page.

Hope this is useful to you,

Thanks

Sudhir

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Check SAP note:

1224089 - How to shrink a MSSQL Server 2005 / 2008 Database?

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

Thanks for ur quick reply.

As ur message can shrink only the log file.As my logfile is 1mb.

I need to shrink or reduce the size of my Transaction Data of  the database without any issues.

Thanks,

Vijay Kumar

Former Member
0 Kudos