Reducing the Database Size

This topic explains how to reduce the size of the transaction log if db_controlmanager is taking up a lot of disk space.

Specifically, the db_controlmanager_log.ldf is using 24GB while the db_controlmanager.mdf is only using 300MB.

How can the size of this transaction log be reduced?

The size of the log files are physically reduced when:

• A DBCC SHRINKDATABASE statement is executed.

• A DBCC SHRINKFILE statement referencing a log file is executed.

• An autoshrink operation occurs.

Option 1: Truncate the TB_invalid Log

Type the following SQL command:

truncate table tb_invalidlog

Option 2: Configure the Database

The following configuration will help in truncating the log automatically.

The log will be truncated every 30 min (SQL 2000 default) if the record is done after Checkpoint.

Execute the following commands on the SQL Server:

• Alter Database db_controlManager set recovery simple

• Alter Database db_controlManager set auto_shrink on

• EXEC sp_dboption 'db_ControlManager', 'trunc. log on chkpt.', 'TRUE'

Option 3: Manually Truncate the Log

DBCC shrinkDatabase(db_controlManager)

Backup log db_controlManager with truncate_only

See also:

Shrink db_ControlManager.mdf and db_ControlManager.ldf using SQL commands

Shrink db_controlmanager_log.ldf using SQL Server Enterprise Manager