Unexpected increase in size of the database after migration

[AHassan]

Hi,
I have experienced that the SQL database backup (.bak) size increases after the migrations from HOPEX V2 to V2R1 and also from HOPEX V2R1 to V3 which might be expected due to the introduction of new features and GUI etc., in the latest versions.

However, I have seen a dramatic increase in size for one database from less than 100 GB (V2) to 320 GB (V2R1) and 520 GB (V3). This seems quite strange that the database size is increasing with this high ratio. I have already tried to run different tools but still not much difference in the size of database – I ran tools such as:

• Shrink “Database” and “files” tools in SQL Server Management studio
• Took the transaction log backup to reduce the database size
• Remove Private Workspace Temporary Data
• Shrink unused repository historical data
• Delete history logs
  

Any idea what could be the reason of this unexpected increase in size of the database and what can be done to cater this behavior?

[AHassan]

Hi again,

I have added screenshots of an example migration from V2 -> V2R1 -> V3 which shows the database backup size and space occupied.
I actually don't have any database where the recovery mode is "Full" and the size didn't increase - the size of .bak file from V2 -> V2R1 becomes roughly 2 to 3 times bigger.

[jcamara]

Hi,

You're welcome and thank you for your reply as well.

What was the consumed versus allocated to SystemDb and data repositories at V2 and again at V3?

Also, the environments that did not increase, were any of those setup with "Full" for recovery as well?

JCAMARA

[AHassan]

Hi Jcamara,

Thanks for the swift response, it is appreciated.

The recovery settings as "Simple" is fine for Pre-Prod/Test databases but normally it is not set for Production database and I also find that the MEGA databases e.g. PreSales and Training are setup with recovery mode as "Simple" but not many of the customers do this on Production databases.

Also the space allocation and autogrowth of logs contributes more towards the maintenance of the databases in addition to other maintenance task mentioned in the online documentation i.e. "RDBMS Repository Installation Guide" which are also taken care of in this case.
Having said that the question still remains that why the database size increases dramatically after the migration and even if we run all the maintenance task as mentioned in the previous post, it doesn't really help.
This behaviour only came to my notice when the database size increased to 500+GB and when I checked with other databases which are recently migrated, they have more or less same ratio of increase in size.

[jcamara]

Hi,

It could be due to:

1. The recovery settings in SQL typically set to "Full" retaining all the changed during the upgrade. You could backup your existing environment(s) then setting it to "Simple".

2. Also, calculate exact how much space you are actually consuming via the database's properties and how much is allocated. If there is over allocation you can set the "initial size" to just 300-400MB over. Sql Management will resize the databases accordingly.

3. in Sql Management, set the autogrowth to something like 128-256MB:

4. You can perform some maintenance on the repository logs through management, assuming you have them enabled. You can find information in our online documentation on what that would impact (some of the historic change logs of objects would be deleted). 

JCAMARA