Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Troubleshooting List


The Troubleshooting List

Author
Message
Philip Kelley
Philip Kelley
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20939 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20939 Visits: 9671
So what was the root of that problem??
Philip Kelley
Philip Kelley
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20939 Visits: 9671
I'd like to see the execution plan of that thing w00t.
Normajean Bowen
Normajean Bowen
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 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
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 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