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 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.
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 more information about how to use logs to restore data, see the FAQ in the essence area.) Therefore, we do not recommend that you truncate logs in the production database unless you have sufficient reasons and confidence, or ......
You are not responsible for this.
Since this method is not safe, I will introduce a security method below.
Large
All know, SQL Server
When the transaction log backup is completed, the inactive part of the transaction log is automatically truncated. These inactive parts contain completed transactions, so they are no longer used during recovery. On the contrary, the activity part of the transaction log still contains
In the running but not completed transactions. 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.
Exec sp_dboption 'your _ dbname', 'trunc. Log On chkpt. ', 'true'