The Un-Droppable Transaction Log File

Abstract

Adding a temporary, second transaction log file is acceptable when the log file runs out of space. In this case, when the ALTER DATABASE command was issued to drop the second log file, lhe following error message was returned:

I had to add a second log file to my database because the log file ran out of space and I was unable to perform a transaction log backup. Now I can't drop the second log file. Can you help?

Adding a temporary, second transaction log file is acceptable when the log file runs out of space. If the Jog cani be cleared, then the alternative really comes down to adding another log file or switching to the simple recovery model.

After the crisis has passed, the second log file should be removed again. In this case, when the ALTER DATABASE command was issued to drop the second log file, lhe following error message was returned:

If you look at Iog_reuse_wait_desc in sys.databases, you'll see that there's no reason listed for why the log can't be cleared. How can this be? It could be that the currently active virtual log file happens to be in the second file, because there's always at least one active virtual log file. Figure 1 shows the virtual log file status in DBCC LOGINFO.

Ah, this is the problem! Each log file has only a single virtual log file, and the entire log must always have at least two virtual log files. This means the second log file can't be dropped because the entire log would be left with only a single virtual log file.

The trick here is to expand the first log file so thai it has more lhan one virtual log file. After this is completed, the second log file can be dropped. If you want to know what the DBCC LOGINFO output means, see my blog post "Inside the Storage Engine: More on the circular nature of the log," at tinyurl.com/67gnrfh.

But how did the database get into this state? As it turns oui. it's pretty simple to engineer this case, and it involves shrinking each log file when it contains virtual log files that are larger than 1MB. The script in Listing 1 will produce the case I've described here.