October 3, 2007 at 6:35 pm
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
October 4, 2007 at 6:20 am
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
October 4, 2007 at 9:57 am
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.
October 4, 2007 at 12:49 pm
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
October 4, 2007 at 1:27 pm
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.
October 5, 2007 at 8:01 am
I guess, you will need 36+'' monitor to see it ;-).
Ninja's_RGR'us (10/3/2007)
I'd like to see the execution plan of that thing :w00t:.
There is a good practise to check execution plan when using complicated objects.
October 5, 2007 at 8:09 am
I have been in sooooooooo mmmmmmaaaaannnnyyyyyy war stories that I could put probably a book together but definitely the usual steps take you to the root of the problem. In my experience step #1 is ALWAYS check SQL Server Error log First then move onto the next stuff...
* Noel
October 5, 2007 at 8:31 am
Igor Kutsyy (10/5/2007)
I guess, you will need 36+'' monitor to see it ;-).Ninja's_RGR'us (10/3/2007)
I'd like to see the execution plan of that thing :w00t:.There is a good practise to check execution plan when using complicated objects.
It's a best practice to ALWAYS check the execution plan.
October 5, 2007 at 9:43 am
noeld (10/5/2007)
I have been in sooooooooo mmmmmmaaaaannnnyyyyyy war stories that I could put probably a book together but definitely the usual steps take you to the root of the problem. In my experience step #1 is ALWAYS check SQL Server Error log First then move onto the next stuff...
A good argument for checking sp_who2 before the SQL Server error log is that checking sp_who2 is about the only thing many of the other people around you know how to check, and in times like these when people ask "did you check sp_who2," if you say anything other than "yes," they will doubt that you know what you're doing. They'll be wrong, but they'll still think that.
Personally, you'll learn very different things from each one, and I generally check both at the same time. I do sp_who2 because it is quicker and then I open my sql script to check the error logs and run it. Then I look at the output of both, usually the error log first because its window is on top.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply