December 19, 2008 at 7:19 am
The strange thing is that I get a @@trancount = 3 at execution time ...
So that would get me to writing another
while @@trancount > 0
Begin
Commit Tran
End
Begin Tran
And then again, how would that behave in a MSDTC distributed transaction ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2008 at 8:24 am
Hmm, you might want to figure out what kind of LOGON this is before you do that then...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 20, 2008 at 3:05 am
Maybe.
However, when using windows groups to grant security, we don't have control about the actual account being used.
So finding the "exceptions" would be a trial and error thing :crazy:
and would only result in angry users and "unhappy customers".
Hence, the asynchrone solution is ideal :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2008 at 7:46 pm
Yeah, I think that you're right.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 9:41 am
jeffrey yao (12/16/2008)
Informative article.But I am still trying to understand why the transaction isolation level plays a role here. Before a connection is established, there is no isolation level to that "would-be" connection. The only thing I can think of is that the user table used in the trigger have a table-scope lock (by either another existing connection or some other applications) that prevents the table being accessed when the trigger trying to update the user table when a new connection is being established. So this may not have anything to do with the isolation level, but because of something else, like lock escalation, or simply a table-wide lock on the hot table.
++1
I still don't get why Isolation level is causing a server crash. I could guess that a "connection" crash could be possible due to timeout issues because of the table contention but "server" crash does not seems normal ...
* Noel
December 23, 2008 at 10:33 am
I didn't see anywhere that it said that the Server crashed, did it? I thought that Johan restarted the server himself in order to clear everything out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 24, 2008 at 4:32 am
Well, the instance didn't failover, but it dumped like hell.
SQLErrorlog file stating ".... a lock it does not own .... " which IMO is a symptom for the lock manager to be fairly messed up.
Serializable transactions keep locks from their beginning untill commit or rollback, causing more overhead to the lock manager.
Maybe this points to a bug, but more important, it points to use another way of handling this kind of logging.
I didn't file the bug at MS, because the workaround is actualy the way to do it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 24, 2008 at 8:26 am
It would be interesting to hear Microsoft's take on this error message though. I have some theories as to how Serializable Isolation + Logon Triggers could lead to this error message, but they all end with either "bug in SQL server" or "serious design flaw in Serializable Isolation".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 28, 2008 at 9:16 am
I'll see what I can do ...
I'm not that the office until Jan 5th ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 28, 2008 at 11:40 am
Hi Johan,
I'd suggest the following test in the test environment
1. Have a lock monitoring script running as a job on the server, the monitoring script only monitors the locks on table master..T_DBA_ConnectionTracker, and it should be run continuously against sys.dm_tran_locks (mainly look for X locks, BOL has several good examples.) and recording these X locks to another table / or simply send out the email.
2. From several other clients, try to log on to the server with the original logon trigger turned on
3. you can coordinate step 1 and 2 like this (for example): step 1 runs at 10:00am, step 2 runs at 10:01am
If failure occurs on 2, we can check what have been logged in step 1.
Another approach is in your original trigger, put a try... catch block and if there is an error occurs, just log the locks on master..T_DBA_ConnectionTracker, (However, I have not done this, i.e. whether I can put a try..catch block inside logon trigger, I will do some tests on my side as well and post back if I have time).
TIA,
JY
January 7, 2009 at 2:06 am
Searching if this bug was know by MS, I stumbled onto:
http://support.microsoft.com/kb/951188
This kb does not 100% describe the symptoms I experienced, it states the hotfix was included in Cumulative Update 7.
The instances where I experienced the dumps, were on SP2 + CU3.
For the moment I am preparing for SP3 implementation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2009 at 7:11 am
They sure don't say much about it though, do they?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 1:22 pm
WOW, that sounds a bug of sql server 2k5 itself. I am glad we have SP2 + CU10. 🙂
January 7, 2009 at 1:45 pm
jeffrey yao (1/7/2009)
WOW, that sounds a bug of sql server 2k5 itself. I am glad we have SP2 + CU10. 🙂
keep in mind, if you go to SP3, you must also apply CU1 because that contains SP2-CU10 and CU11.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2009 at 1:48 pm
RBarryYoung (1/7/2009)
They sure don't say much about it though, do they?
Indeed, looks like a moment where a dba just quickly mumbles "and there was this little other thingy we fixed while we were handling your case" :w00t::w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply