April 2, 2012 at 11:57 am
scope_identity() as I mentioned earlier.
Jared
CE - Microsoft
April 2, 2012 at 12:02 pm
Thank you, we will check it out...
April 2, 2012 at 12:33 pm
Since it happens under heavy load, is it possible the DAL (or application if you aren't using an explicit DAL) is dropping connections before allowing the transaction to commit? Possibly repurposing shared/pooled connections?
If so, the best way to avoid that is to use stored procedures instead of inline SQL.
Check if you tran log is growing more than expected. Check for uncommitted transactions in your SPID lists.
A trace will, if I'm not mistaken, only show explicit rollbacks. Not implicit ones caused by, for example, a KILL command. You might check that too.
- 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
April 2, 2012 at 12:37 pm
Thank you, we will do that...
April 2, 2012 at 12:49 pm
eshulman (4/2/2012)
What is your suggestion to fixing the SELECT @@IDENTITY that we are using?
Scope_Identity() is definately the preferred choice here, as mentioned.
what we had - '<WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _
what we have now - <WebMethod()>
Ah, there's one of the evils. Apologies my experience with VB.NET isn't strong enough to have helped you find it, just one of those nasty 'no transactions from outside the call' scenarios. Glad you located it. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 2, 2012 at 1:07 pm
Craig, I really appreciate the time you took on this problem, your insights and those of others helped us narrow thus bugger down...
April 2, 2012 at 1:43 pm
Side note, potentially even better than scope_identity() is using an Output clause in the insert. That can handle multi-row inserts, can't cross-scope, and allows for options other than just an ID column (like if you use a GUID instead of an ID, or if you want extra columns besides just the ID).
Not critical, but often worth looking in to.
- 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
April 2, 2012 at 1:47 pm
we will check into that too, thank you very much...
Viewing 8 posts - 61 through 67 (of 67 total)
You must be logged in to reply to this topic. Login to reply