Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I know shrinking the log is counter-indicated and has been covered many times, but I just encountered the following, baffling scenario:

We had a run-away query attempt to delete so many records that the transaction log file grew to 30GB on a Windows Server 2008-R2 machine running SQL Server 2008 with the database set to FULL recovery mode. My boss ran a script to backup the database once and then shrink the transaction log to the point where transaction log file was just 1024K. He says the size of the transaction log did not shrink by executing the backup and had the shrink run in a loop to decrease the unused space. How can it reduce the size of the file 30,000 times by just cleaning up unused space? Are we losing useful data by shrinking the log?

share|improve this question

migrated from stackoverflow.com Oct 16 '11 at 17:00

2 Answers

up vote 5 down vote accepted

"Shrinking" the log is actually "clearing" the log.

The log is basically a list of operations that have been performed, and is used as a reference for rollbacks, restores, checkpoints, etc.

The space wasn't "unused" but it contained transactions that had been committed and were reflected in the backup that was performed.

Your boss cleared out all the transactions in the log file, but since you had just run a backup this is acceptable since none of those transactions were uncommitted.

share|improve this answer
You can run backups with transactions pending, and you can restore backups with the transactions still pending (it's an option in the interface) – xanatos Oct 14 '11 at 14:38
2  
@xanatos - in that case those transactions would still be in the log, and he wouldn't be able to recover that space until they were committed though. That obviously was not the case in this instance. – JNK♦ Oct 14 '11 at 14:42
SQL Server eventually cancelled and rolled back the runaway delete query. Does that mean "cancelled and rolled back" counts as "committed enough"? I'm still shocked the log can be 30,000 times larger than it needs to be. – flipdoubt Oct 14 '11 at 14:43
2  
@flipdoubt - The rollback is using the log file to undo the delete. That's why the log got so large - it had to keep track of all the data changes until you either committed or rolled back the transaction. It was exactly the size it needed to be to keep track of the work you were performing. – JNK♦ Oct 14 '11 at 14:46
That makes sense, but what I don't get is the size of the log after the rollback. You initially said the transaction had to be committed, even though this one was rolled back. So that tells me there was still a need for those 30GB of deletes. Assuming there really was no need, does SQL Server really keep the log file that big just to save the effort of shrinking? If shrinking the log is counter indicated, should we have left it that large? – flipdoubt Oct 14 '11 at 14:51
show 1 more comment
up vote 2 down vote

Shrinking the log does not loose any uncommited log data, i.e. the log file after truncation can still be used to recover the current state of the database, from its most recent backup and from the log file (Assuming FULL recovery mode).

Shrinking the log is merely a matter of "repacking" the useful data towards the beginning of the file and truncating the unused portion of the file at the end.

Edit: in re-reading the question, I can offer a bit of explanation about the
    "[my boss]had the shrink run in a loop to decrease the unused space"...
The loop part, i.e. the need of shrinking multiple times arises from the fact that depending on the amount of fragmentation of the log, a single SHRINKFILE may not be sufficient to reclaim all the unused part of the log.
I can see how the idea of having to run this process in a loop could lead someone to be weary of the process or even to think that possibly the shrinking process is akin to some ZIP-like compression whereby compressing the archive further improves the overall compression. Nothing of the sort, this is just a small idiosyncrasy of SQL Server: the defragmentation isn't always optimal at once.

share|improve this answer
The "unused" portion still represents transactions though. – JNK♦ Oct 14 '11 at 14:35
@JNK: the unused portion is made of transactions that were readily committed at the time the last checkpoint occurred and of transactions that are now duplicated to some location earlier in the file. The former can safely be lost since they are implicit to the last recovery point, the latter can be "lost" since they are actually safeguarded at the beginning of the file. – mjv Oct 14 '11 at 14:47
1  
yes, I just wanted to clarify it wasn't empty space that was there for no reason. – JNK♦ Oct 14 '11 at 14:49
@JNK Thank you for keeping me honest ;-), i.e. for prompting me to clarify the nature of the truncated part of the log. – mjv Oct 14 '11 at 14:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.