Stop SQL Server transaction log (.LDF) files from growing indefinitely

Symptoms

You notice that in your SQL databases directory the .LDF files are growing permanently.

Solution

Set the recovery mode of your SQL Server databases to ‘simple’.

Step-by-step instructions

    1. Perform a full-backup of your SQL Server databases.
      Note: This is very important since switching from the full or bulk-logged recovery model to the simple recovery model breaks the backup log chain. Therefore, it is strongly recommend to back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log. [Source]
    2. Switch recovery mode of SQL databases to SIMPLE.
      (See also: What is simple recovery mode?)Important Note:

“The Simple recovery model lets you restore the database to the point from which it was last backed up. However, this recovery model does not enable you to restore the database to the point of failure or to a particular time.”  [Source]

For each database...

... set recovery mode to SIMPLE.

  1. Shrink the transaction log (.LDF) files.Don't forget to Shrink...

    ... the log files after changing the recovery mode.

  2. Perform a full-backup of your SQL Server databases.

Optionally you can use a script for the steps described above:

SqlScript

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s