October 2, 2008 at 11:58 am
I am running a server side trace that captures exceptions. I have been getting a lot of 1222 errors --mainly coming from tempdb. I have not found a pattern of time that these exceptions occurs.
Does this error mean that the timeout limit was reached for a query and therefore was rolled back? I've been trying to read up on this but haven't gotten a full understanding of what is happening.
I did perform the steps to reproduce the behavior from http://support.microsoft.com/kb/286286/en-us#appliesto. I received -- "Error 1222 was NOT trapped."
Do I need to just trace more so I can determine what is causing this to occur? This database is highly transactional, so I can't run large traces all day. Any suggestions on the next steps I should take to try to eliminate these errors from occurring?
I have asked the vendor of the application if they trap 1222 errors, but, I don't believe they do...so, from what I understand, could be a huge concern. That would mean that the application would most likely assume the transaction is successful just because it is complete -- even though there was an error. Is that correct?
Thank you for your time! I look forward to your feedback.
January 28, 2009 at 2:56 pm
I am getting a lot of these from the msdb database as well. Does anyone know what this means?
January 28, 2009 at 3:01 pm
That error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type.
Do you have any particularly long-running queries that might be associated with it? Perhaps a heavy duty report, or something similar to that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 3:10 pm
GSquared (1/28/2009)
That error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type.Do you have any particularly long-running queries that might be associated with it? Perhaps a heavy duty report, or something similar to that?
GSquared,
Thanks for responding. There are some bigger queries that run against this instance, but, they should be against another database...not msdb. And, if there was a lot of sort and temp table usage - that would reference dbid=2 (tempdb) correct.
My original post mentioned this error with tempdb - but, I am mainly concerned with msdb as those are what I am currently receiving in production. There were a few issues and I want to make sure that this error has nothing to do with what is happening in one of the user databases. Am I thinking of this correctly....that if the error is just for msdb, that it would have to do with one of the tables on msdb??
January 29, 2009 at 8:36 am
Yeah, I think I mixed two answers into one statement. Wasn't very clear.
It's a lock-timeout error. If it's happening in msdb, then it's probably something to do with jobs and/or SSIS packages. If it's in tempdb, then it's temporary tables/table variables/work tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 8:39 am
GSquared (1/29/2009)
Yeah, I think I mixed two answers into one statement. Wasn't very clear.It's a lock-timeout error. If it's happening in msdb, then it's probably something to do with jobs and/or SSIS packages. If it's in tempdb, then it's temporary tables/table variables/work tables.
Thanks GSquared! I'll run a trace on msdb to try to figure out is timing out. Have a great day!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply