Shrinking Large SQL Transaction Log Files
The transaction log is a record of all the transactions that have been performed against a SQL database since the transaction log was last backed up. Microsoft SQL Server 2000 database transaction log files (.ldf) can grow to a large size if proper maintenance to shrink the size is not regularly performed. When this happens an error will appear to the user initiating the transaction that the log file is full. If the log file is full it generally means that the hard disk on which the transaction log file resides is nearly full and as a result this can spawn other system problems.
- Make a complete backup of your database and store it in an offline location.
- Resolve the issues causing the SQL maintenance plan failures.
- Open the SQL Query Analyzer and enter a login and password.
- Enter the following query: Backup log "Your_Database_Name" with truncate_only
- Execute the query.
- Open the SQL Enterprise Manager. Expand Microsoft SQL Servers. Expand SQL Server Groups. Expand your SQL Server. Expand Databases. Right-Click your database.
- Choose All Tasks>Shrink Database. Click the Files button.
- Choose the log file from the popup menu.
- Click the "Shrink file to" radio button.
- Enter the minimum file size. (listed in parenthesis).
When the steps are complete, the transaction log file of the database will have been truncated and the file size reduced to a manageable size.