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...
