How to handle SQL server logs that are full

Transaction log file is a file used to record database updates with the extension LDF.
In SQL Server 7.0 and SQL Server 2000, if the automatic growth function is set, the transaction log file is automatically expanded.
Generally, when the maximum number of transactions between two transaction log truncation occurs, the transaction log size is stable. Transaction Log truncation is triggered by the checkpoint or transaction log backup.
However, in some cases, the transaction log may become very large, resulting in exhausted space or full. Generally, when the transaction log file occupies sufficient disk space and cannot be expanded, you will receive the following error message:
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 due to the lack of transaction log extended space. For more information about how to recover from this situation, see the "insufficient disk space" topic in SQL Server online help.

In addition, transaction log extensions may cause the following situations:
· Very large transaction log files.
· Transactions may fail and may start to roll back.
· Transactions may take a long time to complete.
· Performance problems may occur.
· Blocking may occur.

Cause
Transaction Log extensions may occur for the following reasons or situations:
· Uncommitted transactions
· Very large transactions
· Operation: DBCC dbreindex and create Index
· During restoration from transaction log backup
· Client applications do not process all results
· Query times out before the transaction log is extended. you receive a false "log full" error message.
· Unreplicated transactions

Solution
When the log file is full and the SQL database cannot write the file, you can use either of the following methods:
One method: Clear logs.
1. Open the query analyzer and enter the command
Dump transaction database name with no_log
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to shrink to xxm In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Another method is risky because the log files of SQL Server are not immediately written to the master database file. improper processing may cause data loss.
1: Delete log
Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete log files
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 you do not want it to become larger later.
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple

In addition, for example, the database attribute has two options, which are related to the growth of transaction logs:
Truncate log on checkpoint
(This option is used for sql7.0. in SQL 2000, the fault recovery model is selected as a simple model)
When the checkpoint command is executed, if the transaction log file exceeds 70% of its size, the content will be cleared. This option is often set to true in the development database.
Auto shrink
Check the database in a specified period. When the unused space of the database file or log file exceeds 25% of its size, the system will automatically reduce the file so that the unused space is equal to 25%. When the file size does not exceed the initial size when it is created, the file size after the file is not reduced must be greater than or equal to its initial size for the transaction the log file can be reduced only when it is backed up or the truncate log on checkpoint option is set to true.

Note: Generally, the default attribute of the newly created database has been set, but the database attribute is changed in case of an accident. Please clear the log and check the above attributes of the database to prevent the transaction log from being full again.

 

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

 

This morning, I saw that there was no space on the server's hard disk. I checked the Database Log Size for 18 GB and ran the article I found on the Internet last time to modify the Database Log Size, prompting "the database city log is full. Back up the transaction logs of the database to release some log space ." I checked the relevant information on the Internet and found the best method below. The effect of compressing the original 100 GB Database Log File to MB is very obvious. (I have only tried the first three steps, and the method below is not verified.)-compressed logs and database file size/*-Please follow these steps in particular, the previous steps are not performed. do not perform the subsequent steps. Otherwise, your database may be damaged. -*/1. clear log dump transaction
Database Name with no_log2. truncation transaction log: backup log database name
With no_log3. shrink database files (if not compressed, the database file will not be reduced by the Enterprise Manager-right-click the database you want to compress-all tasks-shrink database-shrink file-Select log file-select to contract to xxm In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click "OK"-Select data file-select "contract to xxm" in the contraction mode, here we will provide a minimum number of M that can be reduced. Enter this number directly to confirm the operation. You can also use an SQL statement to complete the contraction: DBCC shrinkdatabase (customer data) -shrink the specified data file. 1 indicates the file number. You can use this statement to query: Select * From sysfilesdbcc shrinkfile (1) 4. to minimize log files (for SQL 7.0, this step can only be performed in the query analyzer). database separation: Enterprise Manager-server-database-right-click-Detach database B. delete the log file C. attached Database: Enterprise Manager-server-database-right-click- This method generates a new log with a size of more than 500 K or code: The following example separates pubs and then attaches a file in pubs to the current server. A. separate exec sp_detach_db @ dbname = 'pubs' B. delete the log file C. add exec sp_attach_single_file_db @ dbname = 'pubs', @ physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF '5. in order to automatically contract in the future, make the following settings: Enterprise Manager-server-right-click Database-properties-options-select "auto contract"-SQL statement setting method: exec sp_dboption 'database name ', 'autoshrink', 'true' 6. if you want to prevent the log from increasing too much in the future, choose "Enterprise Manager"> "server"> "right-click" Database ">" properties ">" transaction log "> Restrict File growth to xm (X is the maximum data file size you allow) -SQL statement setting method: Alter database name Modify file (name = logical file name, maxsize = 20)