How can I shrink my RMS database to optimize performance and minimize backup file size?

We suggest you reindex and shrink your database periodically to ensure optimum performance. This procedure is best performed when the store is closed and RMS activity is idle.

1. Run Administrator, File, Connect, and enter your database connection settings, then click OK.
2. Create a backup by selecting Database, Backup.
   Do NOT skip this step! If the following procedure fails for any reason you may need to restore the backup.
3. Select Database, Reindex and wait for the process to finish.
4. Select Database, Query and cut and paste following script into the window. Change MYDATABASE to the name of your database file.
   
   DECLARE @Database varchar(50)
   SET @Database = 'MYDATABASE'
   /* Truncate log */
   DUMP TRANSACTION @Database WITH TRUNCATE_ONLY
   /* Shrink Database */
   DBCC SHRINKDATABASE (@Database)
   
   
5. Press F5 to run it. When finished exit Administrator and select Yes to save. Name this script "Shrink database". Next time select File, Open for step 4.

Repeat this procedure every few months or so to ensure your database is optimized, or when you need to free up space to stay below the MSDE or SQL 2005 Express limits (2G or 4G) or the 10G limit on SQL 2008 R2.

Notes:

Remove line "DUMP TRANSACTION @Database WITH TRUNCATE_ONLY" if running SQL 2008 R2.

To keep database overhead to a minimum, MSDE and SQL 2000 users should also disable AutoClose. You only need to run this script once on your database.

EXEC sp_dboption 'MYDATABASE', 'autoclose', 'FALSE'

Replace MYDATABASE with your database name.