SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Growing Pains


Log Growing Pains

Author
Message
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54106 Visits: 11391
arr.nagaraj (3/11/2010)
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 and KB919611. 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?

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
arr.nagaraj
arr.nagaraj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2722 Visits: 1596
Well, I have systems running with SP2 + still facing the issue.
And I know few people who have the same issue even after sp3.

Lemme explain a bit more on how the growth part is done. This scripts I use alert me the log which are 75% or more full. We increase the size at a downtime or at non peak hours. The reason is a file growth happening at peak hour can have a serious performance impact and its a good practice to monitor closely have it done yourself instead of relying on auto grow.

Having said that, as Production DBAs with almost zero knowledge about
application and being responsible for few hundred databases across several servers, its hard to allocate perfectly at one shot. We do by getting the info we have. And when autogrow needs to be done occasionally( probably abt once in 2 months) we do.

Regards,
Raj

http://Strictlysql.blogspot.com
arr.nagaraj
arr.nagaraj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2722 Visits: 1596
@Paul,


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.)


The unfortunate thing is my apps are not well designed and I have very little control over it. And My idea is this. To play it safe and instead of regretting on
missed data because of a miss on the time frame, I am better of having all the info at little cost ;-)

Regards,
Raj

http://Strictlysql.blogspot.com
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54106 Visits: 11391
arr.nagaraj (3/11/2010)
Well, I have systems running with SP2 + still facing the issue.
And I know few people who have the same issue even after sp3.

That really makes no sense at all. Either you have not researched the issue properly and applied the correct hotfix, or the servers in question are not at the Service Pack level you think they are. You also do not mention why the work-arounds in the KB articles will not work for you.

arr.nagaraj (3/11/2010)
Lemme explain a bit more on how the growth part is done. This scripts I use alert me the log which are 75% or more full. We increase the size at a downtime or at non peak hours....<snip>

I am not really all that concerned about how you run your databases.
You said yourself it was a digression - and therefore not related to the article under discussion.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54106 Visits: 11391
arr.nagaraj (3/11/2010)
The unfortunate thing is my apps are not well designed and I have very little control over it. And My idea is this. To play it safe and instead of regretting on missed data because of a miss on the time frame, I am better of having all the info at little cost ;-)

No. I prefer Jason's approach. Logging every small transaction is just pointless in the context of this article, as I have made clear already. You will just make it harder to find the information you need if and when a crisis occurs.

BTW, it might be nice if you acknowledged the efforts made by the author in putting this article together.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99012 Visits: 18616
Let's keep in mind that the query I provide for recording the size is for SQL 2005 and 2008 (as stated in the article). To make this work on SQL 2000, you would have to use the script (or something of that nature) that Raj provided in his second post to this thread.

That said, go ahead and continue to digress ;-)



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99012 Visits: 18616
Paul White (3/11/2010)

No. I prefer Jason's approach. Logging every small transaction is just pointless in the context of this article, as I have made clear already. You will just make it harder to find the information you need if and when a crisis occurs.


Good point. As the method currently is, and depending on volume - there may already be plenty of information to sift through to find the offending code.


BTW, it might be nice if you acknowledged the efforts made by the author in putting this article together.


Thanks again. Cool



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54106 Visits: 11391
CirquedeSQLeil (3/11/2010)
Let's keep in mind that the query I provide for recording the size is for SQL 2005 and 2008 (as stated in the article). To make this work on SQL 2000, you would have to use the script (or something of that nature) that Raj provided in his second post to this thread.
That said, go ahead and continue to digress ;-)

[troll] Does anyone use 2000 any more? [/troll]

Sadly, I cannot digress much longer - I have to review your review of my article, Jason. Back later, perhaps.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99012 Visits: 18616
Paul White (3/11/2010)
Does anyone use 2000 any more?

Sadly, I cannot digress much longer - I have to review your review of my article, Jason. Back later, perhaps.


Unfortunately, I recently took ownership of a lot of 2000 boxes. I guess I should work out a solution for them too ;-)

I won't be back for several hours - I need to get some sleep sometime before the workday begins.Doze



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

arr.nagaraj
arr.nagaraj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2722 Visits: 1596
@paul,


I am not really all that concerned about how you run your databases.
You said yourself it was a digression - and therefore not related to the article under discussion.


One thing I fail to understand is your questions about my digression are ok. But my replies arent. :-) . No problem. I have met people like this, and I am sure I will.

Regards,
Raj

http://Strictlysql.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search