How can I keep the log files of Microsoft SQL Server from increasing? _ PHP Tutorial

Source: Internet
Author: User
How can I keep the log files of Microsoft SQL Server from increasing ?. It is a regular issue to narrow down the MSSQL log file. However, there are already many answers to this question in the essence area. I will not go into details here. Now, we will discuss how to reduce the log files of MSSQL. However, there are already many answers to this question in the essence area. I will not go into details here.
Now let's discuss the permanent problem, that is, how to make the log file no longer grow?
First, we will introduce a simple method.
It is to set the fault recovery model of the database to "simple" (SQL2K ). In this way, the log will be truncated at the Checkpoint.
The procedure is as follows:
1. right-click the database in Enterprise Manager, select "properties | option | fault recovery", and select "simple". if it is SQL7, there is a "trunc. log on chkpt..
2. if you do not want to use Enterprise Manager, execute it in Query Analyser or isql.
EXEC sp_dboption 'Your _ dbname', 'trunc. log on chkpt. ', 'True'
You can.
However, it should be noted that after this operation, although the log will not increase, it also means that once you have misoperations, there will be no chance to use the log to recover. (For details about how to use logs to restore data, see the FAQ in the essence area)
Therefore, it is absolutely not recommended to truncate logs on the production database unless you have good reasons and are sure enough, or ......
You are not responsible for this.
Since this method is not safe, I will introduce a security method below.
As we all know, SQL Server will automatically cut off the inactive part of the transaction log when backing up the transaction log. These inactive parts contain completed transactions, so they are no longer used during recovery. On the contrary, the activity part of the transaction log contains the transactions that are still running but not completed. SQL Server re-uses the truncated inactive space in the transaction log, instead of allowing the transaction log to continue to increase and occupy more space.
Therefore, we can back up the transaction log so that the log file does not increase.
However, it is not a way to keep the log file. deleting it will lead to loss of recovery.
We can combine full backup. The transaction log can be deleted after full backup.
For example, for a backup plan, a full backup is performed once a day, and the transaction logs are backed up every 15 minutes within seven days, which is retained for 2 days.
You can use the database maintenance plan wizard to easily create a backup plan, but you must remember how long the backup will be retained. Otherwise, it will be a bad thing if the hard disk space is backed up.
Wrotten by Lucky @ Dev-club
March 8, 2002

Bytes. Now let's discuss governance...

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.