Skip to Content

Database of SAP Business One

I am using SAP Business One 8.82 PL:10 and SQL Server 2008 R2.Two months ago the database size was about 12 GB. Now the size of database has increased to 22 GB. Also, the size of SBO_Common has increased to 20 GB.

As per our daily routine work, in two months the database should increase by 1 GB i.e. about 13 GB. On the database servers, we have other databases related to web and desktop applications. These database have not swollen but the problem is with SAP database.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Jan 03, 2017 at 10:00 AM

    Hi Dilip,

    please check your properties of each Database in SQL-Mgmt-Studio.

    -> options

    please check, if the recovery Model is "simple"... if not, try to change

    after that, you have to restart SQL-Services...

    To reduce filesize of LOG-data, you have to make a Maintenance-Plan for Shrinking each Database.

    Hope that help you.

    Markus

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 03, 2017 at 10:56 AM

    Recovery model for the all the databases including SAP is simple. Can you please explain me in detail the maintenance-Plan for Shrinking each Database

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 03, 2017 at 11:33 AM

    Hi Dilip,

    Use shrink database it reduces size of your database

    SQL Server Management ----> database (which you want to shrink) ------> Write Click select shrink ----> Database

    hopes it will be helpful for you

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 03, 2017 at 12:28 PM

    Dear Girish,

    I tried shrinking the database and the database size shrinked to 20 GB. I want to know the reason why the database expanded from 12 GB to 22 GB ?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 03, 2017 at 05:17 PM

    Hi Dilip,

    please try this Query to have a look to the biggest tables in your DB:

    You can paste this into your Mgmt-studio... it will show you the Size of each Table in KB, ordered by Size.

    CREATE
    TABLE #temp (table_name sysname ,row_count INT,reserved_size VARCHAR(50),
    data_size VARCHAR(50),index_size VARCHAR(50),unused_size VARCHAR(50))
    SET NOCOUNT ON INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,a.row_count,COUNT(*) AS col_count,a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default= b.table_name
    collate database_default GROUP BY a.table_name, a.row_count, a.data_size
    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp

    Hope that help you.

    Markus

    Add comment
    10|10000 characters needed characters exceeded