How to make the log files for Microsoft SQL Server not grow

Server Description: How do I make the log file for Microsoft SQL Server not grow?








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 we're going to talk about the root cause, how do we make log files no longer grow?





first introduce a simple method.


is to set the database's failover model to "simple" (SQL2K). This will truncate the log when it is checkpoint.


Concrete Operation method is:


1, in Enterprise Manager right-click 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 don't want to use Enterprise Manager, execute
in query analyser or isql

EXEC sp_dboption ' your_dbname ', ' trunc. Log on chkpt. ', ' TRUE '


on it,
.

However, note that this is done, although the log does 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 that
take responsibility for.





Since this method is not secure, I will introduce a secure method below.


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.


with the Database Maintenance Plan Wizard can easily set up a backup plan, but must remember to set how long to keep the backup Oh, otherwise hard disk space is backed up to fill the bad.