SQL SERVER Log Full processing method (RPM)

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: Generally the newly established database default properties 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.

/***************************************/

This morning a look at the server hard disk has no space, check the database log up to 18G run the last article on the Internet to modify the database log size, prompted "Database city log is full." Back up the transaction log for the database to free up some log space. "Online Check the relevant information, find the best use of the following, the original 18g database log files compressed to 100M effect is very obvious." (I have only tried the first three steps OK, the following method is not verified) – compression log and database file size/*– Special attention please follow the steps, do not follow the previous steps, do not do the following steps may damage your database. –*/1. Emptying the log dump      transaction      Library Name      WITH      NO_LOG2. Truncate the transaction log:backup   log   database name    WITH   no_log3. Shrink the database file ( If not compressed, the database file does not reduce the Enterprise Manager – Right-click on the database you want to compress – all tasks – shrink the database – Shrink the file – Select the log file – select Shrink to XXM in the shrink mode, here will give a minimum number of m allowed to shrink to, enter this number directly, OK-Select the data file – Select Shrink to XXM in the shrink mode, here will give a allowable contraction to the minimum number of M, directly enter this number, OK   can also be done with SQL statement – Shrink database dbcc   shrinkdatabase (Customer Profile) – Shrinks the specified data file, 1 is the file number, which can be queried through this statement to:select   *   from   sysfilesdbcc   shrinkfile (1 4. In order to maximize the size of the log file (if it is sql   7.0, this step can only be done in Query Analyzer) a. Detach a database: Enterprise Manager – Server – Database – Right – detach database B. Remove the log file from My Computer c. Additional database: Enterprise Manager – Server – Database – Right – Attach database This method will generate a new log with a size of more than 500 K or in code: The following example separates    pubs, and then    pubsA file in    is attached to the current server. A. Separating exec   sp_detach_db   @dbname    =   ' pubs ' B. Deleting log files c. Additional exec    sp_attach_single_file_db   @dbname    =   ' pubs ', @physname    =    ' c:\program   files\microsoft   sql   server\mssql\data\pubs.mdf ' 5. In order to automatically shrink in the future, Make the following settings: Enterprise Manager – Server – Right-click Database – Properties – Options – select "Auto shrink" –sql statement settings:exec   sp_dboption   ' database name ',   ' AutoShrink ',   ' TRUE ' 6. If you want to not let it grow too big in the future Enterprise Manager – Server – Right-click Database – Properties – Transaction log – limit file growth to XM (x is the maximum data file size you allow) the way the –sql statement is set: alter   database   database name    modify   file (name= logical file name, maxsize=20)  

The SQL SERVER log is full processing method (RPM)