How to reduce the MSSQL log file is a recurring problem, but this problem in the elite area has a lot of answers, I will not repeat here.
Now let's talk about the fundamental problem of how to make log files grow no longer.
Let's introduce a simple method.
is to set the database's failover model to "simple" (SQL2K). This will truncate the log when it checkpoint.
How to do this:
1, in the Enterprise Manager right-click Database, "Properties | options | Fail Restore", select "Simple", if it is SQL7, in the "properties | Options" There is a "trunc. Log on chkpt. ", check it out.
2, if you do not want to use Enterprise Manager, in query analyser or isql inside execution
EXEC sp_dboption ' your_dbname ', ' trunc. Log on chkpt. ', ' TRUE '
You can do it.
However, it is important to note that, after doing so, although the log does not grow, it also means that once you have a mistake, you will not have the opportunity to take advantage of the log recovery. (How to use the log to recover see the FAQ for the elite area)
Therefore, it is absolutely not recommended to truncate the log on the production database unless you have sufficient reason and enough certainty, or ...
It's not you who takes responsibility.
Now that this method is unsafe, I'll introduce a safe approach.
As you know, SQL Server automatically truncates the inactive part of the transaction log when it completes the transaction log backup. These inactive parts contain completed transactions and are therefore no longer used during the recovery process. Instead, the active part of the transaction log contains transactions that are still running but have not yet completed. Instead of letting the transaction log continue to grow and consume more space, SQL Server will reuse these truncated inactive spaces in the transaction log.
Therefore, we can back up the transaction log so that the log files are no longer increased.
However, the log file has been placed is not a way to delete it, and will lose the possibility of recovery.
We can do that with a full backup. The transaction log before the full backup can be deleted.
For example, a backup schedule, a full backup every day, a 7-day, 15-minute transaction log backup, and 2-day retention.
The Database Maintenance Plan Wizard makes it easy to set up a backup plan, but be sure to remember how long it takes to set up the backup, otherwise the hard disk space is backed up to a full-on bad thing.
Wrotten by Lucky@dev-club
March 8, 2002
http://www.bkjia.com/PHPjc/631146.html www.bkjia.com true http://www.bkjia.com/PHPjc/631146.html techarticle How to reduce the MSSQL log file is a recurring problem, but this problem in the elite area has a lot of answers, I will not repeat here. Now let's discuss the cure ...
