How to reduce the size of the database

Problem

Documents and images are stored in the database and for some databases, these attachments are using a large part of the database size.

Solution

Advanced SQL skills are required

This task requires advanced SQL skills and should be done is consultation with the client.

There are 7 separate scripts attached to this article:

Four that delete attachments from emails, purchase orders, quotes and sales orders based on a date entered into the script.
One script to delete inventory images on Discontinued and Deleted items.
One script to delete Webhook Messages that have already successfully been sent, or failed and will never be sent.
Then a script to Shrink the database once attachments have been deleted.

Plus we describe one Jiwa utility to delete expired prices from Jiwa.

Step 1

Run the standard stored procedure usp_Jiwa_SpaceUsed. Then copy the results into an Excel document and sort largest to smallest on space used to see which tables are taking the most room.

On a database of 32GB removing these attachments that were older than 1 January 2016 reduced the database size by 10GB. On this test database it took 1.5 hours per delete script (run them separately) and 5.5 hours to shrink the database. In Jiwa, the link to the attachment is still there but trying to open it will report an error.

We recommend that you run this on a copy of each database first, review the results and adjust the date part of the script accordingly i.e. keep more or less data. And then the live. It can be done while users are in but will significantly impact response time for them so suggest its an overnight or weekend job to do.

Step 2

Scripts to delete document attachments. The script to delete pdf attachments from Email Maintenance usually offers the most gain.

See also special note below about email attachments.

Step 3

Run this script to delete inventory images against Discontinued and Deleted inventory items. You may also want to consider Slow and Obsolete items. See https://jiwa.atlassian.net/wiki/spaces/J7UG/pages/986184/Inventory#Status

When an image is deleted from an inventory item (I.e. set to NULL), then the picture that will display is the standard Jiwa inventory image:

Step 4

Run this script to delete Webhook Messages that have already successfully been sent, or failed and will never be sent.

Step 5

Delete expired prices from Jiwa. When you add a debtor or debtor classification price to an inventory item, the default expiry date is 20 years. (Thank you to our loyal customers who have been with Jiwa for at least as long as that, this may now be relevant to you). You can also set a shorter expiry. When the price has expired there’s a utility to delete those records. When you have too many price records it makes the Inventory > Configuration > Sell Prices form unmanageable and even unable to open.

Make sure you are on 7.02.01 SR13 or higher

Inventory > Utilities > Price Updates > Debtor/Debtor Class Batch

Step 6

Deleting all this data doesn’t necessarily reduce the size of the database. It can just create blank space. There are then tools to remove that blank space.

In SSMS open the Properties form against the database. On the General tab note the Size and the Space Available. The Space Available indicates how much you can save, it may be more or less.

Run the attached script to Shrink Database

Step 7

Check the Properties again after running the Shrink database script. If you haven't made any gains, you should try running the Task > Shrink > Database from the SQL Server database options.

Special Note

Where the database was a version 6 database upgraded to a version 7 database, the upgrade script causes the last saved date of the email attachments to be the date of the upgrade.

Run this script first to re-set the last saved date of the attachment to the date of the email.

^{UPDATE EM_Attachments SET LastSavedDateTime = (SELECT LastSavedDateTime FROM EM_Main WHERE RecID = EM_Attachments.EmailMessageID)}
^GO