EC Enterprise: File Shrinking with EnergyCAP databases
Posted by Barry Kroeker, Last modified by Joel Brickell on 10 June 2013 03:48 PM
Issue: SQL Server has procedures for reducing the size footprint of a database. When the AUTOSHRINK option is set to TRUE for a particular database, SQL Server will periodically perform the AUTOSHRINK operation.
However, some computer software/hardware management professionals have suggested that the AUTOSHRINK operation can fragment files and thus slow data processing. This is a potentially troublesome issue for some larger EnergyCAP databases.
SOLUTION: EnergyCAP recommends periodic re-indexing of EnergyCAP databases, particularly in situations where the in-house database management includes database shrinking procedures.
The following script will turn the AUTOSHRINK feature ON (TRUE) when run on the EnergyCAP database:
NOTE: The above script may need to be modified depending upon whether the client is using the full or bulk-logged recovery models on the database. If the answer is yes, then the WITH TRUNCATE_ONLY should be dropped from the BACKUP LOG statement, otherwise the data in the transaction log will not be backed up. All databases created by EnergyCAP Professional use the simple recovery model, thus the inclusion of the WITH TRUNCATE_ONLY.
The following is a sample script for re-indexing your EnergyCAP database files.
NOTE: Rebuilding an index puts an exclusive lock on the table being indexed, so the reindex script operation should be performed during "down time" when no users are logged into the database.
! CAUTION: EnergyCAP, Inc. strongly recommends creating a database backup immediately prior to running scripts on any EnergyCAP database. !
by B. Kroeker/R. Ohlson/T. Marte 01/16/2009 Updated by Joel Brickell 08/23/2012 EnergyCAP ALL RELEASES AND VERSIONS