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 ««123»»

The Troubleshooting List Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2007 12:34 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 649, Visits: 210
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".

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

Philip



Post #406369
Posted Wednesday, October 3, 2007 12:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
23 MB left instead of 23 GB? That will make a small difference .
Post #406373
Posted Wednesday, October 3, 2007 12:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
So what was the root of that problem??
Post #406388
Posted Wednesday, October 3, 2007 6:19 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 649, Visits: 210
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



Post #406555
Posted Wednesday, October 3, 2007 6:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
I'd like to see the execution plan of that thing .
Post #406559
Posted Wednesday, October 3, 2007 6:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 10, 2010 4:35 PM
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
Post #406561
Posted Thursday, October 4, 2007 6:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 18, 2012 8:55 AM
Points: 162, Visits: 334
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
Post #406718
Posted Thursday, October 4, 2007 9:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:14 PM
Points: 1,618, Visits: 1,552
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #406927
Posted Thursday, October 4, 2007 12:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 10, 2010 4:35 PM
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
Post #407033
Posted Thursday, October 4, 2007 1:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:14 PM
Points: 1,618, Visits: 1,552
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #407052
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse