Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Log Growing Pains Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 2:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880819
Posted Thursday, March 11, 2010 2:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
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

Strictlysql.blogspot.com
Post #880831
Posted Thursday, March 11, 2010 3:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
@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

Strictlysql.blogspot.com
Post #880834
Posted Thursday, March 11, 2010 3:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880837
Posted Thursday, March 11, 2010 3:16 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880843
Posted Thursday, March 11, 2010 3:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #880845
Posted Thursday, March 11, 2010 3:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #880849
Posted Thursday, March 11, 2010 3:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880850
Posted Thursday, March 11, 2010 3:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #880853
Posted Thursday, March 11, 2010 3:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
@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

Strictlysql.blogspot.com
Post #880855
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse