Reducing 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

There are five 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. Then a script to Shrink the database once attachments have been deleted. 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.

Open

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.

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

Open

Open

Open

Open

Script to Shrink Database

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

Open

Open

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.

See also

Delete documents attached to Quotes based on criteria

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}

Related articles