Shrinking the Transaction Log files in SQL Server

re: Shrinking the Transaction Log files in SQL Server

Suhas De — Tue, 25 Aug 2009 17:33:56 GMT

Hi Chris,

Thanks for the comments.

Shrinking a Transaction Log file generally does not need any resources and is very quick (generally does not take more than a few seconds). Please check DBCC LOGINFO(DatabaseName) to know how much of the log can be shrunk.

Also, DBCC SHRINKFILE can be blocked by other users / applications trying to access the database. Make sure that there are no users or applications connected to the database before executing the command.

Hope this helps.

Thanks

Suhas

Resources required to shrink the log file

Chris — Tue, 25 Aug 2009 00:47:45 GMT

Thanks for a great write up on the log; we have a very large logfile (14GB or so), for a 40Gb database. And per above ( sqlperf) it's less than 1% utilized! So, I can't wait to shrink it; but, I'm a little afraid; how many database resources does shrinking use? Is it quick, or intensive? Should I do it after hours? Will it take seconds, or minutes, or hours?

Thanks!

re: Shrinking the Transaction Log files in SQL Server

Suhas De — Fri, 17 Jul 2009 07:01:24 GMT

Hi David,

Thanks a lot for your comments. Really appreciate it.

To answer your first question, VLFs have been introduced to reduce the overall cost of finding the next free block. If we make the VLFs small enough to be able to accommodate just one Log Record (or eradicate the concept of VLF all together), we will have to examine each log record to check if it is free and can be over-written. This will increase the overall cost of finding the next free record.

Consider the following scenario:

My log file is small and can hold 10 records. I have the following:

Record 1 => Tran 1 (Start)

Record 2 => Tran 1 (Insert)

Record 3 => Tran 2 (Start)

Record 4 => Tran 1 (Insert)

Record 5 => Tran 2 (Delete)

Record 6 => Tran 2 (Delete)

Record 7 => Tran 1(Update)

Record 8 => Tran 2 (Commit)

Now, when I start Tran 3, Record 9 will be utilized [Record 9 => Tran 3 (Start)] and the next record (say, insert for Tran 3) will have to go to Record 10 [Record 10 => Tran 3 (Insert)]. Where will the next record go? To find that out, we have to scan the Log File from the beginning to find the next free record. As you can understand, each time I hit the end of the file, I have to start scanning from the beginning. It’s easy when the log file is small, but when the log file is huge, this can create severe performance issues.

Grouping the records into logical segments (VLFs) can reduce the overall cost of finding the next free block. The larger the logical segment, the less number of scans we have to do, and hence the lower the cost. However, at the same time, the larger the size of the segment, the more space we waste. So, we have to strike a balance between the cost and space wasted. Hence, we came up with the concept of VLFs.

Your second question is a bit tricky and will need a lengthy explanation. If it’s ok for you, I will prefer writing a whole blog post on this topic. I will have some time during this week-end, so, by Monday, you should find a detailed post on this.

Thanks,

Suhas.

re: Shrinking the Transaction Log files in SQL Server

David — Thu, 16 Jul 2009 23:55:41 GMT

"So as to be able to properly utilize log space, a log file is divided into logical segments called VLFs or Virtual Log Files"

A question from someone who is not really qualified to ask it...

Could you explain why VLFs are beneficial? Why not allow the logical log to rotate through the whole physical log with a pointer to the next free space, rather than divide the physical log into large chunks? Log backup would record the last LSN backed up, and you could use that and the active log pointers to figure out what is "inactive" and can be reused for new log records.

Also, I have read in the documentation (Transaction Log Physical Architecture) that truncation of the log marks any VLFs as inactive "whose [log] records all appear in front of the ... MinLSN". Shouldn't that continue on and say "or after the last-written log record"? Or does "in front of" include the tail-end VLFs? Please excuse me, I am having a hard time "wrapping my mind around" the logical log.

Why doesn't an helpful post like this receive more comments? I have not seen any more helpful description of shrinking a transaction log. Thanks!

Anith » Shrinking the Transaction Log files in SQL Server

Anith » Shrinking the Transaction Log files in SQL Server — Mon, 16 Mar 2009 20:20:45 GMT

PingBack from http://www.anith.com/?p=19333