<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jason Brimhall
»
Log Growing Pains
49 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Log Growing Pains
Rate Topic
Display Mode
Topic Options
Author
Message
Paul White
Paul White
Posted Thursday, March 11, 2010 2:29 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990,
Visits: 10,576
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
arr.nagaraj
arr.nagaraj
Posted Thursday, March 11, 2010 2:56 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:34 AM
Points: 483,
Visits: 1,198
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
arr.nagaraj
arr.nagaraj
Posted Thursday, March 11, 2010 3:04 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:34 AM
Points: 483,
Visits: 1,198
@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
Paul White
Paul White
Posted Thursday, March 11, 2010 3:10 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990,
Visits: 10,576
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
Paul White
Paul White
Posted Thursday, March 11, 2010 3:16 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990,
Visits: 10,576
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
SQLRNNR
SQLRNNR
Posted Thursday, March 11, 2010 3:18 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880845
SQLRNNR
SQLRNNR
Posted Thursday, March 11, 2010 3:22 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880849
Paul White
Paul White
Posted Thursday, March 11, 2010 3:24 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990,
Visits: 10,576
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
SQLRNNR
SQLRNNR
Posted Thursday, March 11, 2010 3:31 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880853
arr.nagaraj
arr.nagaraj
Posted Thursday, March 11, 2010 3:35 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:34 AM
Points: 483,
Visits: 1,198
@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 »
49 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
