Server How to shrink 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.
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.
