Tutorials to .com

Tutorials to .com » Database » Sqlserver » crosoftSQLServer the log file will not increase

crosoftSQLServer the log file will not increase

Print View , by: iSee ,Total views: 9 ,Word Count: 537 ,Date: Sun, 23 Aug 2009 Time: 10:07 AM

crosoft SQL Server's log file will not be increased Description: how to make the Microsoft SQL Server will not increase the log file?

How to reduce the MSSQL log file has been a recurrent problem, but the essence of the problem in many areas has been the answer, I will not repeat them here.
Now we discuss the fundamental issues, namely how to make the log file is no longer increasing?

First to introduce a simple method.
Failure is to restore the model database set to simple (SQL2K). So that it will be truncated when Checkpoint log.
Specific methods of operation are:
1, in the right point in the Enterprise Manager database, attribute | Options | failure to restore, you can choose a simple, if it is SQL7, in the Properties | Options, there is a trunc. Log on chkpt., Select it.
2, If you do not want to use Enterprise Manager, or Query Analyser in which the implementation of isql
EXEC sp_dboption your_dbname, trunc. Log on chkpt., TRUE
You can, however, is important to note that after doing so, although the log does not increase, but also means that once you misoperation, there will be no opportunity to resume the use of the log. (How to use the log to restore the essence of the district see the FAQ)
So, definitely not recommended in the production database log truncated, unless you have sufficient reason and sufficient grasp, or ... ...
Not your responsibility.

Since this method unsafe, I will introduce a safe method.
As we all know, SQL Server in the completion of the transaction log backup will automatically truncate the transaction log in the inactive part. Some of these activities include what has been done, and therefore no longer used during the recovery process. In contrast, the transaction log contains the activities are still running but has not yet done. SQL Server transaction log to re-use of these cut-off of non-activity space, rather than let the transaction logs continue to grow and occupy more space.
Therefore, we back up transaction logs so that log files can no longer be increased.
However, the log file has been placed is a way not to delete it, it will also lose the possibility of recovery.
We can combine to do a full backup. Full backup done before the transaction log can be deleted.
For example, a backup plan, a full backup once a day, 7 days, reservations, and every 15 minutes a transaction log backup, retention of two days.
With database Maintenance Plan Wizard can easily create a backup plan, but we must remember how long to retain the backup set, oh, or hard disk space to be filled back up on a bad thing.

Microsoft SQL Server Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.