March 23, 2010 at 4:45 am
So long as SQL Server does not need any more space for the transactions, it will re-use the current space in the log.
Think of it like this: The transaction log is a trash bin (garbage can, etc.)
When a transaction is committed to the data file, it's "tombstoned". A marker is put on it telling the system that this transaction can be deleted from the log or thrown in the trash (so to speak). But this garbage collection doesn't happen until the log is truncated. So a truncate command is like telling your significant other to take out the trash. The S.O. may take forever to actually empty the can (this is a checkpoint), but when he/she does take out the trash, you have a free trash can to put your stuff in. So long as you keep generating the same amount of "stuff" at the same pace as always, you never have to get a bigger trash can.
But if you never take out the trash, your can will overflow and overfill. Also, if you change your habits (i.e., if the database generates more transactions during the day than it used to), you'll have to take out the trash more often or get another can to handle the overflow.
Does that make sense?
March 23, 2010 at 5:00 am
So it's better not to shrink the log unless it's too large.
So that SQL server can use the same space in log and it doesn't have to expand it everytime. Am I right.?
Or probably can you tell me a situation in which we realy need to shrink the log.?
Thanks for such detailed explanation. Am almost clear now.
March 23, 2010 at 5:10 am
Joy Smith San (3/23/2010)
So it's better not to shrink the log unless it's too large.So that SQL server can use the same space in log and it doesn't have to expand it everytime. Am I right.?
Absolutely.
Or probably can you tell me a situation in which we realy need to shrink the log.?
Best practices say you should never shrink the log, but if you feel you must, the most acceptible situations are
1) Running out of hard drive space. In which case you'd better be getting more HD space while you're using this as a stop-gap only. And you'd still better be backing up regularly.
2) Runaway transaction (infinite loop) that you have stopped and rolled back PRIOR to shrinking the log.
3) Moving the data file & log file to a new server for whatever reason. But in this case you'll have backed up both before-hand (as CYA) and stopped allowing new transactions so that you don't accidently lose any.
Mind you, in all of these situations, there are better ways to deal with the problem of a growing transaction log. Shrinking the log is solving a symptom, not curing the disease. If you have a log that keeps growing that fast and you're backing it up quite regularly, then there's a code problem somewhere that needs to be resolved.
Another thing to keep in mind. Index rebuilds can cause your log to grow overwhelmingly fast. Shrinking the log won't stop it from growing again the next time you do a rebuild, so factor that into your plans for hard drive space requirements.
Lastly, if your HD runs out of space, it might not be a Transaction Log problem. It could be that you have had a sudden influx of other files or programs on your server that took up the space the TLog is used to using. So make sure to keep track of that too.
March 23, 2010 at 5:46 am
Thank you somuch. I am very much clearon it now.
March 23, 2010 at 6:32 am
Glad I could help.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply