How to handle full SQL server logs)

Method:
1. backup log billionlinksoft_vip with no_log (recommended)
Then shrink the database in the enterprise manager. You can also execute:
DBCC shrinkdatabase (billionlinksoft_vip)

2. Dump transaction database name with no_log (the old SQL Server method may not be supported in future versions)

 

How to handle full SQL server logs

ArticleCategory: SQL Finance

........................................ ........................................ ................

SQL Server transaction logs increase unexpectedly or are full of Processing Methods

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 ApplicationsProgramDo 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 database established is set, but the database attribute is changed in case of exceptions. please clear the log and check the preceding attributes of the database to prevent transaction logs from being full again.

 

 

From: http://www.cnblogs.com/codehunter008/archive/2005/05/08/150998.html