"Go" SQL Server log full or too large processing method

Translated from: http://blog.chinaunix.net/uid-7953959-id-2543262.html

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 , 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 SUSPECTbecause 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 "logfull" 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 the database you want to compress- - All Tasks- - Shrink Database- - Shrink file--Select log file- - in the contraction mode to choose to shrink to XXM, here will give a permit to shrink 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 LOGwith a size of more than 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

This way , however, you should be careful when recovering to the last full backup

In addition, the database properties have two options, which are related to the growth of the transaction log:

Truncate Log On Checkpoint

( This option is for SQL7.0, the recovery model is selected as a simple model in SQL

This option is set to True when the CHECKPOINT command is executed if the transaction log file exceeds its size 70% The content is purged in the development database

Auto Shrink

Periodically check the database when the unused space for a database file or log file exceeds the size of the 25% , the system automatically shrinks the file so that its unused space equals 25% Files that do not shrink when the file size does not exceed the initial size when it is established must also be greater than or equal to the initial size of the transaction log file reduction only if it is backed up or the Truncate log on checkpoint option is set to True can be carried out.

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.

"Go" SQL Server log full or too large processing method