Running out of disk space because transaction log is too big.

Products

• Control Compliance Suite Databases MS SQL SRVR
• Control Compliance Suite Unix
• Control Compliance Suite Databases Oracle
• Control Compliance Suite Netware
• Control Compliance Suite Exchange
• Control Compliance Suite Windows

Issue/Introduction

Running out of disk space because transaction log is too big.

Symptoms
Hard disk has 0 bytes free.

Cause

When Control Compliance Suite is installed, all the CCS databases are configured to use Full recovery model. This requires that the database and transaction log files are regularly backed up. When the transaction logs are too big and the disk runs out of space, SQL may mark the database as suspect because of the lack of space for the transaction log expansion.

Resolution

Note: Please backup all the affected SQL databases (including transaction logs) before proceeding.

1. Make sure that all Control Compliance Consoles are closed.
2. Open SQL Server 2005 Management Studio. and connect to the SQL server hosting the Contol Compliance Suite databases.
3. Click on File | New | Query with Current Connection
4. Set the database recovery model to Simple by running the following t-SQL command
   1. ALTER DATABASE "database_name" SET RECOVERY SIMPLE
5. Right click on the affected database and click on Tasks | Detach...
6. Click on OK.
7. Go to the directory where the CCS database files are located (default location: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
8. Rename the *.ldf file for the affected SQL database.
9. Go back to the SQL Server 2005 Management Studio.
10. Right Click on Databases | Attach...
11. Click on Add on Attach Databases screen,
12. Browse to the affected database and select it. Click on OK.
13. In "Database details" section, select the transaction log file and click remove.
14. Click on OK to attach the database. This will create a new transaction file.

Technical Information
http://support.microsoft.com/kb/873235 -- How to stop the transaction log of a SQL Server database from growing unexpectedly