How do I make the log files for Microsoft SQL server not grow?

Source: Internet
Author: User
Tags microsoft sql server
How to reduce the MSSQL log file is a recurring problem, but this problem in the elite area has a lot of answers, I do not repeat here.
Now let's talk about the root cause, which is how to make the log files no longer grow.
Let's introduce a simple method.
is to set the database's failover model to "simple" (SQL2K). This will truncate the log when it is checkpoint.
The specific method of operation is:
1, in Enterprise Manager right key database, "Properties | options | failback", select "simple" on it, if it is SQL7, there is a "trunc in the Properties | options". 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 '
It's okay.
However, be aware that after doing so, although the log will not increase, but also means that once you have a misoperation, there will be no chance to take advantage of log recovery. (How to use the log to restore please see the FAQ for the Essence area)
Therefore, it is absolutely not recommended to truncate the log on the production database unless you have sufficient reason and sufficient certainty, or ...
It's not you who take responsibility.
Now that this method is unsafe, I'll introduce a secure approach.
As you know, SQL Server automatically truncates the inactive portions of the transaction log when it completes a transaction log backup. These inactive parts contain completed transactions and are therefore no longer used during the recovery process. Instead, the active portion of the transaction log contains transactions that are still running but have not yet been completed. Instead of allowing the transaction log to continue to grow and occupy more space, SQL Server will reuse these truncated inactive space in the transaction log.
So, we back up the transaction log to make the log file no longer grow.
However, the log file has been placed is not a way to delete it, and will lose the possibility of recovery.
We can do it with a full backup. The transaction log can be deleted before a full backup is made.
For example, a backup plan, a full backup every day, retained within 7 days, every 15 minutes a transaction log backup, retained for 2 days.
The Database Maintenance Plan Wizard makes it easy to set up a backup schedule, but be sure to keep the backup as long as you want, otherwise the hard disk space is backed up and it's a bad thing.
Wrotten by Lucky@dev-club
March 8, 2002
Related Article

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.