Home >Backend Development >PHP Tutorial >How to prevent Microsoft SQL Server log files from growing larger? _PHP Tutorial
How to prevent Microsoft SQL Server log files from growing larger? _PHP Tutorial
- WBOYOriginal
- 2016-07-13 17:01:32914browse
How to reduce the size of MSSQL log files is a frequent question, but there are already many answers to this question in the essence area, so I won’t go into details here.
Now let’s discuss the fundamental problem, that is, how to prevent the log file from growing larger?
First introduce a simple method.
Just set the database's failure recovery model to "Simple" (SQL2K). This way it will truncate the log during Checkpoint.
The specific operation method is:
1. Right-click the database in Enterprise Manager, "Properties | Options | Fault Recovery", select "Simple", if it is SQL7, there is A "trunc. log on chkpt. ", just select it.
2. If you don’t want to use Enterprise Manager, execute
EXEC sp_dboption 'your_dbname', 'trunc. log on chkpt.', 'TRUE'
in Query Analyser or isql. However, It should be noted that after doing this, although the log will not increase, it also means that once you make a mistake, you will not have the opportunity to use the log to recover. (For how to use logs to recover, please see the FAQ in the essence area)
Therefore, it is absolutely not recommended to truncate logs on a production database, unless you have sufficient reasons and sufficient assurance, or...
It is not you who bears the responsibility. .
Since this method is not safe, I will introduce a safe method below.
As everyone knows, SQL Server will automatically truncate the inactive portion of the transaction log when completing a transaction log backup. These inactive sections contain completed transactions and are therefore no longer used during the recovery process. In contrast, the active portion of the transaction log contains transactions that are still running but have not yet completed. SQL Server will reuse this truncated inactive space in the transaction log rather than allowing the transaction log to continue to grow and take up more space.
So, by backing up the transaction log, the log file will no longer grow.
However, leaving the log file always is not an option. If you delete it, you will lose the possibility of recovery.
We can do it in conjunction with a full backup. The transaction log before the full backup can be deleted.
For example, a backup plan includes a full backup every day and keeps it for 7 days, and a transaction log backup every 15 minutes and keeps it for 2 days.
Using the Database Maintenance Plan Wizard can easily create a backup plan, but you must remember to set how long the backup should be retained, otherwise it will be a bad thing if the hard disk space is filled up by the backup.
Wrotten by Lucky@Dev-club
March 8, 2002