How SQL SERVER log full is handled

　　Transaction log files transaction log file is a file that is used to record database updates, with the extension LDF.

　　In SQL Server 7.0 and SQL Server 2000, if automatic growth is set up, the transaction log files are automatically expanded.

　　In general, the size of the transaction log is stable when the maximum number of transactions that occur between two transaction log truncation can be accommodated, and transaction log truncation is triggered by checkpoints or transaction log backups.

　　However, in certain situations

　　Transaction log files transaction log file is a file that is used to record database updates, with the extension LDF.

　　In SQL Server 7.0 and SQL Server 2000, if automatic growth is set up, the transaction log files are automatically expanded.

　　In general, the size of the transaction log is stable when the maximum number of transactions that occur between two transaction log truncation can be accommodated, and transaction log truncation is triggered by checkpoints or transaction log backups.

　　However, in some cases, the transaction log can become so large that it runs out of space or becomes full. Typically, you receive the following error message when the transaction log files occupy free disk space and cannot be extended:

error:9002, Severity:17, State:2

The log file for database '%.*ls ' are full.

　　In addition to this error message, SQL Server may mark the database as suspect because of the 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.

In addition, transaction log extensions can cause the following situations:

· A very large transaction log file.

· The transaction may fail and may start rolling back.

· Transactions may take a long time to complete.

· A performance problem may occur.

· A blocking behavior may occur.

Reason

The transaction log extension may occur because of the following reasons or circumstances:

· UNCOMMITTED transactions

· Very big business.

· Actions: 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 completes the extension, and you receive a false "Log full" error message

· Transactions that are not replicated

　　Solving method

　　When the log file is full and the SQL database cannot write to the file, there are two methods available:

　　One way: Empty the log.

　　1. Open Query Analyzer, enter command

DUMP TRANSACTION database name with NO_LOG

　　2. Then open Enterprise Manager-right you want to compress the database-all tasks-shrink the database-shrink the file-Select the log file-in the shrink option to shrink to XXM, this gives you a minimum m number that you want to shrink to, just enter the number and make sure.

　　Another approach has a certain risk, because SQL Server log files are not immediately written to the database master file, such as improper handling, can result in loss of data.

1: Delete Log

Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database

2: Delete log file

Additional database Enterprise Manager-> Server-> Database-> Right key-> attached database

This method generates a new log with a size of more than 500 K.

Note: The first method is recommended.

If you don't want it to get bigger later.

Use under SQL2000:

Right-click on the Database-> properties-> option-> failure recovery-model-select-Simple model.

or with SQL statements:

ALTER DATABASE name set recovery simple

In addition, the database properties in the figure above have two options that are related to the growth of the transaction log:

Truncate Log On Checkpoint

(this option is used in Sql7.0,sql 2000, which is the recovery model selected as the simple model)

　　Clears the contents of the transaction log file if it exceeds 70% of its size when executing the checkpoint command This option is often set to true when developing the database

Auto Shrink

　　Periodically check the database when the unused space of the database file or log file exceeds 25% of its size, the system will automatically shrink the file so that its unused space equals 25% When the file size does not exceed its initial size when it is established, the file reduction file must also be greater than or equal to its initial size the reduction of the transaction log file can only be done if it is backed up or when the Truncate log on Checkpoint option is set to true.

Note: generally established database default properties have been set, but encountered unexpected circumstances so that the database properties have been changed, please empty the log, check the database above properties, in case the transaction log again full.