Clean up SQL Server 2008 log files cannot shrink log file 2 solution _mssql2008

Source: Internet
Author: User
Tags sql 2008

SQL 2008 log Files account for 23G of hard disk space, and the transaction log has been truncated (Truncate), the actual log content is small, 1G is not, want to release the extra space occupied by the log files.

However, no matter how you shrink (Shrink) log files, space is not free and always error occurs:

cannot shrink log file 2 (Cnblogstext_log) Because of minimum log space required.

Solved similar problems before, and also wrote a blog-sql Server 2008 transaction log cleanup, the final solution adopted at that time was:

Change recovery mode from complete (full) to Simple

Shrink (Shrink) log file

Change recovery mode from simple to complete (full)

Always thought that this is the ultimate method, but unexpectedly failed to end the problems encountered.

After unremitting efforts, finally found the real ultimate method:

1. Detach Database (detach must block all writes to this database before it is done )

2. Delete or rename log files

3. Attach database, you will be prompted not to find the log file

4. Remove (remove) This can not find the log file, and then click OK for Attach,sql server will automatically create a new log file in the folder where the data files are completed attach.

5. If the log file needs to be stored in a different path, it is necessary to move the log files again through Detach/attach.

Detach Database

Move the log file to a new location

Attach the database, modify the log file path and complete the Attach

The disadvantage of this ultimate approach is that during the operation, the database being manipulated cannot be accessed properly.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.