SQL Server log file too large workaround

SQL Server transaction log unexpectedly increases or fills up the processing method

Transaction log files transaction log file is a file that is used to record database updates, with an extension of LDF.
In SQL Server 7.0 and SQL Server 2000, the transaction log files are automatically expanded if the autogrow feature is set.
In general, the size of the transaction log is stable when the maximum number of transactions that can be accommodated between two transaction log truncation, and transaction log truncation is triggered by checkpoints or transaction log backups.
In some cases, however, the transaction log can become very large, so that it runs out of space or becomes full. Typically, you receive the following error message when the transaction log file takes up free disk space and is no longer extensible:
error:9002, Severity:17, State:2
The log file for database '%. * ls ' is full.
In addition to this error message, SQL Server may mark the database as SUSPECT because of a lack of transaction log expansion space. For additional information about how to recover from this scenario, see the "Low Disk space" topic in SQL Server online Help.

Additionally, transaction log extensions can cause the following scenarios:
· Very large transaction log files.
· The transaction may fail and may start rolling back.
· The transaction may take a long time to complete.
· Performance issues may occur.
· There may be blocking behavior.

Reason
Transaction log extensions can occur for the following reasons or scenarios:
· Non-committed transactions
· A very large transaction
· Action: DBCC Dbreindex and CREATE INDEX
· When restoring from a transaction log backup
· Client application does not process all results
· The query timed out before the transaction log completion extension, and you receive a fake "log full" error message
· Transactions that are not replicated

Workaround
Two methods are available when the log file is full and the SQL database cannot write to the file:
One way: Empty the log.
1. Open Query Analyzer, enter a command
DUMP TRANSACTION database name with NO_LOG
2. Open Enterprise Manager again--right click on the database you want to compress--all tasks--shrink the database--Shrink the file--Select the log file--in the contraction mode to choose to shrink to XXM, here will give an allowable contraction to the minimum number of M, directly enter this number, OK.

The other approach has some risk, because SQL Server log files are not written to the database master file immediately, such as improper processing, can cause data loss.
1: Delete Log
Detach Database Enterprise Manager-> Server-> Database-> Right-click-> Detach Database
2: Delete log file
Attach Database Enterprise Manager-> Server-> Database-> Right-click-> Attach Database
This method generates a new log with a size of more than 500 K.

Note: The first method is recommended.

If later, do not want it to become larger.
Use under SQL2000:
On the database, right-click Properties--Options--model-selection-simple model.
or with an SQL statement:
ALTER DATABASE name set recovery simple

In addition, there are two options for database properties, which are related to the growth of the transaction log:
Truncate Log On Checkpoint
(this option is used in Sql7.0,sql 2000, where the recovery model is selected as a simple model)
This option is often set to true when the checkpoint command is executed if the transaction log file exceeds 70% of its size to clear its contents in the development database
Auto Shrink
Periodically check the database when the unused space for a database file or log file exceeds 25% of its size, the system automatically shrinks the file so that its unused space equals 25% Files that do not shrink when the file size is not larger than the initial size when it is established must also be greater than or equal to the initial size of the transaction log file to be reduced only if it is backed up or the Truncate log on Checkpoint option is set to true.

Note: The default properties of the database that are generally established are set, but when unexpected conditions make the database properties changed, after the user empties the log, check the properties of the database to prevent the transaction log from being filled again.

Excerpt from: http://www.bokesun.com/blogger/laobai/archives/2009/70717.shtml

P.S.

The shrinking of TL process is changed in SQL Server 2008. You just need to

(a) Set the database to simple recovery model

(b) Shirnk the file

(c) Set back-to-full (If required). Ie. If you aren't taking TL backup let it is in the simple recovery model only

SQL Server log file too large workaround