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


The Troubleshooting List


The Troubleshooting List

Author
Message
Philip Kelley
Philip Kelley
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1017 Visits: 232
At my current position, one of the first things I do when things start acting up is to check available hard drive space. We have a lot of processes that perform very large aggregations, and (with fairly frequent code-pushes) it's not unknown for a t-log file, or sometimes tempdb, to suddenly bloat up to fill all available space. These days I always check the alpha-characters first; I wasted 10 minutes or so of Production downtime because I only saw "23", when what it actually said was "23MB". Pinch



Mileage will of course vary depending on your code, environment, and so forth.



Philip



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37585 Visits: 9671
23 MB left instead of 23 GB? That will make a small difference w00t.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37585 Visits: 9671
So what was the root of that problem??
Philip Kelley
Philip Kelley
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1017 Visits: 232
Oh, the usual--code gone wild. There have been a number of similar situations, and they generally fall back to the fact that SQL Server isn't all that good at running queries involving dozens of tables, views, functions, and (thanks 2k5) synonyms... and that's what we've got.



Ya got some tables; then ya build some views on those tables; then a table-valued function on the views; then a view that hits the function; then a query that joins a whole bunch of them. Note that all this was built up over time, and later developers aren't necessarily aware of what the "common tools" (views and functions) that the developers of yore wrote are actually based on. Iterate over that loop several times, and--once you decipher everything--you end up with some pretty monstrous queries.



So, naturally, we buy more hardware and the problem goes away and everyone's happy. And two-three months later...



What's a production DBA to do? At least, as per the original thread, it's fairly easy to pinpoint why the system is acting up...



Philip



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37585 Visits: 9671
I'd like to see the execution plan of that thing w00t.
Normajean Bowen
Normajean Bowen
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 127
Hey Gordon;

Newbie here again...I didn't understand the upshot of your experience and I'd like to learn from it. What did the "Time out occurred while waiting for buffer latch type 2" mean? Why did the database issue that message And did you have to do something to resolve it?

Thanks, Normajean
Gordon Pollokoff
Gordon Pollokoff
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 335
Normajean,



The error is a not very well documented error message. Best guess is that either an I\O bottleneck or CPU\Memory pressure caused a situation where queries were waiting to long to acquire a latch ( a kind of light-weight lock ). In this situation I fell back on a tried and tested problem resolution: I restarted SQL Server. Sometimes, you just have to shrug your shoulders, re-boot, and move on!



Gordon

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Robert Davis
Robert Davis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3548 Visits: 1623
That specific error is actually referring to latches on memory spaces in the buffer. Basically, SQL Server is removing shared objects (cached query plans) that objects have a latch (a latch is basically declaring "I'm next" for a lock on the object). In this case, his query was trying to use a cached query plan that is no longer in the cache. This generally happens when you are using AWE.



I haven't run into this specific error myself, but I suspect it could be fixed by recompiling the queries/procedures or by clearing hte memory buffers so that SQL Server realizes that the query plans are no longer cached.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Normajean Bowen
Normajean Bowen
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 127
Thanks to Gordon and Robert for shedding light on that! Man the more I look, the blinder I feel - I don't know how people learned their way around before these communities existed... Thanks to all you experienced people who are willing to share with those of us that are grappling to get their bearings. Normajean
Robert Davis
Robert Davis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3548 Visits: 1623
These communities help all of us. I've learned a lot by helping people figure out solutions to problems they were experiencing. If all you learn is what you experience, you will learn slowly; but by being able to learn form others' experiences, I've learned much faster. Many times I've encountered a "new" problem in or systems and been able to fix it staight away because I had helped someone else diagnose and/or fix it in their own. It really made me look like more of an expert than I am to be able to pull the solution to a new problem off of the top of my head.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
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