Knowledgebase: EC Enterprise
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:
==============================
backup log [insert database name here without brackets] with truncate_only
dbcc shrinkdatabase ([insert database name here without brackets])
alter database [insert database name here without brackets] set auto_shrink on
==============================

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.
==============================
DECLARE @tableName VARCHAR(128)
DECLARE curTables CURSOR FOR SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN curTables
FETCH NEXT FROM curTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tableName)
FETCH NEXT FROM curTables INTO @tableName
END
CLOSE curTables
DEALLOCATE curTables
==============================

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

(618 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: