Microsoft Dynamics RMS is a Microsoft SQL based ePoS application which ships by default with MSSQL Desktop Edition (MSDE).   MSDE is a free entry level offering to the SQL Server database software family from Microsoft.  Althou free, MSDE does have its limitations; the most painful being a 2GB database size limit.

Dynamics RMS archives receipts of all transactions processed by the point of sale to the journal table. Over time, this table can grow to be very big (primarily xml bloat).

A customer who runs a busy store has been running for years without any problems but after approx 3 years their daily backup process was producing backups which exceeded the size of a single layer DVD (~4.7GB). This was starting to cause problems so something needed to be done.  You could get something like the Archiver utility from Retail Realm but in this case it was decided that we'd just dump all the receipts which were over 1 year old. Obviously, with disk space being so cheap these days it's no harm to make a database backup now before you go deleting receipts! Keep the backup safe in case you ever need to dig up an old receipt.

This seemed simple, I logged in to the database server using RMS Administrator and executed the following command:

DELETE FROM journal WHERE time < GETDATE() - 365

Unfortunately, this query took a very long time to execute and really seemed to put the server under strain.  As a work around, I decided I would delete the data in smaller bite-sized chunks:

First, transactions older than 3 years were deleted:

DELETE FROM journal WHERE time < GETDATE() - (365 * 3)

Followed by transactions older than 2 years:

DELETE FROM journal WHERE time < GETDATE() - (365 * 2)

Followed by transactions older than a year:

DELETE FROM journal WHERE time < GETDATE() - 365

Each query took awhile to complete but in the end it worked a treat.