Speaking: How to make Microsoft SQL Server log files increase?
How to narrow MSSQL log files is already a regular problem, but this problem has many answers in the essence, and I will not go here.
Now we discuss the problem of cure, how to make the log file no longer increase?
First introduce a simple method.
It is set to "simple" (SQL2K) of the fault reduction model of the database. This will cut off the log when checkpoint.
The specific method of operation is:
1. In Enterprise Manager, the "Properties | Options | Fault Restore", select "Simple", if it is SQL7, there is a "Trunc. Log on chkpt." In "Properties | Options", select Yes.
2, if you don't want to use Enterprise Manager, execute in Query Analyher or IsQL
Exec sp_dboption 'Your_dbname', 'trunc. Log on chkpt.', 'True'
You can
However, pay attention is that after doing this, although the log will not increase, it also means that once you have an error, you will not have an opportunity to use log recovery. (How to use logs to restore, please refer to FAQ of the essence area)
Therefore, it is absolutely not recommended to cut off the log on the production database unless you have sufficient reasons and enough grasp, or ...
Not yours of responsibility.
Since this method is not safe, I will introduce a safe approach.
Everyone knows that SQL Server will automatically truncate inactive parts in the transaction log when completing the transaction log backup. These inactive parts contain completed transactions, so they are no longer used during recovery. Instead, the event part of the transaction log contains a transaction that is still running but not completed. SQL Server will reuse these truncated non-active spaces in the transaction log, not the transaction log, continue to increase and occupy more space.
So, we can back up the transaction log to make the log file no longer increase.
However, log files are not a way, delete, will lose recovery.
We can do it with complete backup. You can delete the transaction log before you have a complete backup.
For example, a backup plan is completely backed up once a day, reserved within 7 days, and retains 2 days every 15 minutes.
Use the Database Maintenance Plan Wizard to establish a backup plan, but be sure to remember how long the settings are kept, otherwise the hard disk space is made to account for a bad thing.
