How to clean up SQL Server log free file space

"Problem scene" has a database, its name is called Cnblogstext, the log file occupies 23G hard disk space, and the transaction log has been truncated (Truncate), the actual log content is very small, 1G are not. 23G of space only put 1G log, like a villa with a pet, too extravagant! Adhering to the Chinese nation's virtues of thrift, this luxury is absolutely not allowed, you must 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, this is the lesson of blood)

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.

That's what I found. The ultimate way to clean up SQL Server log free file space. Perhaps experienced you think this is common sense, I do not care, as long as the second kill the actual problem, it is worth sharing!

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/