The query you used to capture tsqls doesnt capture sleeping connections. Was that intentional? I would prefer to grab the sleeping ones as well so that I grab all connections that have completed and not closed.
It seems correct to me. The idea is to capture information about the occasional rogue process
that consumes an unusual amount of log space
and thus causes a log growth. (In any well-designed production system, log growths are very unusual.)Any process that consumes sufficient log space is almost certain to run long enough to be caught by Jason's arrangement.
I really do not see the benefit in including inactive connections - not much information is available after the query has finished executing anyway. Talking about including 'short transactions' also misses the point - we are trying to capture unusually large log usage after all. Short transactions do not use much log space by definition.
Concerning the 12800% growth thing. That is a very old bug now - see KB917887
. There were workarounds to both, were not specific to upgraded SQL Server 2000 databases, and hotfixes were available very quickly, for both RTM and SP1 - so it is not true to say there is no fix pre-SP3. I cannot find a reference to say that the fix was not included in Service Pack 2, but even if it only made it into Service Pack 3 as you suggest, all 2005 production systems should be at least at that level now.
"So What I do is to check how much it has grown, using a few automated scripts, and expand it myself well in advance."
This is does not seem like a best practice. Is there some reason you cannot pre-allocate a log of sufficient size, with well-sized VLFs, and just leave it be?