Captools/net Documentation

DataBase Compaction

DataBase Compaction

Previous topic Next topic  

DataBase Compaction

Previous topic Next topic  

Over time the Captools/net SQL database will naturally grow in size due to the addition of records.  However, when records are deleted from the database, the SQL Server maintains a log of deleted records in the "CaptoolsDB.LDF" file.  Since certain tables in the Captools/net are regularly cleared, e.g. the Transaction Blotter and Reconciliation tables, the CaptoolsDB.LDF will grow over time. To minimize the size of the CaptoolsDB.LDF file, use the Server Control Panel "Admin/Configure Database" command (see Database Settings) and specify "Simple Mode" for the Log file. This will ensure that the log file size is maintained at no more than a few Mb in size.

Although most users should not find it necessary, if you think that the database MDF file is getting larger than expected there are also SQL tools which will allow you to try to optimize it using the following procedure.

1.) Log out of the Captools/net Desktop program and run the Captools/net Server Control Panel program.

2.) Click the "Back up Captools/net DB" button on the toolbar to do a full back up.

3.) After the backup is successfully done, close the Captools/net Server Control Panel program.

4.) Run the SQL Server Management Studio (Express). If not present, download it from Microsoft (www.microsoft.com and search for "SQL Management Studio") and install it.

5.) Connect to CAPTOOLSDBINST instance and open the CaptoolsDb database.

6.) Open a "New Query" window and execute the following commands:

  "DBCC SHRINKFILE(CaptoolsDB_Log, 150)" where 150 is more than the physical size of the MDF file. It is important to specify the right size which should be greater than the current data file (CaptoolsDB.mdf) size.

7.) Reboot the machine.

8.) Re-launch the Captools/net Server Control Panel program and then run the Captools/net Desktop program and verify functionality by running a report.

9.) Mark those old backups to a folder named as "BACKUP-DO NOT USE". After the number of months specified in the backup configurations, this will will eventually be purged.

10.) Close out of the Captools/net Desktop program and do a full back again.

See here for the actual database shrink steps: http://support.microsoft.com/kb/272318/

For more info:

http://support.microsoft.com/kb/317375

http://support.microsoft.com/kb/873235