May 27, 2008 at 9:44 pm
The other day the trans log of a particular DB grew to about 100GB. (The DB uses simple recovery mode). How can I track down the source of this growth and why the log is not shrinking? (The program used to populate this DB is GUI-based and does not generate easy to access sql. )
TIA,
barkingdog
P.S. I can use profiler but I'm certain that, in no time, I'll be overwhelmed by the shear amount of info in the trace. I'm hoping for a more 'sophisticated' approach.
May 27, 2008 at 11:37 pm
It's a misunderstanding that the logfile wil stay small in single recovery mode. De logfile will be as big as a single transaction. So if you do an update on a large table and it costs 800MB for undo operations, that space will be claimed in your logfile.
The difference between full and simple recovery is that after you commit this update, your 800GB will be marked as 'reusable' in a simple recovery mode, while this only happens in full recovery after a full-/logbackup.
Now about (auto-)shrinking: This means that unused space at the end of the logfile will be released. The graphical presentation (in SQL 2000) is a little misleading; it suggests that all the free space is at the end of the logfile, which is not true. Say, your logfile looks like this:
XXXXX---XXXXX--------XX-XXXX---
(X = in use, - = free)
In this case only the last 3 pages can be released, while there's much more space in this logfile.
Be aware that Autoshrink can lead to blocking, so it's not advisable in production environments
Wilfred
The best things in life are the simple things
May 28, 2008 at 12:16 am
Have you done any index rebuilds lately?
Rather stay away from shrinking. Often you shrink the file and the next thing sQL needs to do is grow it again. It can lead to file-level fragmentation. Rather size the log for the largest transaction it needs to hold and leave it alone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply