After Transaction Log Grows, it won't shrink.

The other day I had someone truncate an extremely large transaction log (100GB) but it wouldn't return the space to the OS, not even after a backup and restore operation. There’s an interesting bit of trivia around shrinking this file. When the log file is truncated, you may not see all of the space returned to the OS. The reason is the way the data might be spread. Here’s the way we think data looks in the log file:

<BeginFile>xxxxxxxx--<EndFile>

So when you truncate it and it gets another couple of transactions, it should do this:

<BeginFile>xx<EndFile>

But in fact it might look more like this:

<BeginFile>xx-xx-xxxxxxx<EndFile>

So if you truncate the entries and a couple are left it really looks like this:

<BeginFile>x--x<EndFile>

So when you shrink it, you don’t gain any space back because of this file marking. So what do you do?

There are two ways to shrink a large log file, both of which are a bit painful, and not always practical in production. The first is to write enough transactions into the log to “bump” the data all the way to the end. Since that makes it contiguous, when you truncate it you’ll get the shrink you’re looking for – probably. You can do this by creating a table, adding thousands or millions of rows to it, and then deleting them and the table. Of course this slows down a production system while you do it, and it’s difficult to figure the math.

The other way is more problematic in a production system, but works every time. Take a full backup of the database. Follow the process to “detach” the database as described in Books Online. Find the transaction log file for that database (and only that database) and delete it. Then, “attach” the database. The system will automatically build a new, clean log file. Obviously, there are a lot of places you can be careless here, so I recommend this process only for extreme cases.

Hi,

have a look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Good info - but you'll find that the DBCC shrink commands won't work with the TL as I mentioned. It's less a question than some real-world experience - but thanks for the links! I think they will help people to understand how the logs and files are used.