SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scope: The drastic caveat with Logon Triggers.


Scope: The drastic caveat with Logon Triggers.

Author
Message
jeffrey yao
jeffrey yao
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 887
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.



ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 8971
I didn't forget this thread !
I'm not at the office today.
I'll get back to this tomorrow.

Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Richard Polichetti
Richard Polichetti
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 151
Hi,
It takes a lot of time, effort, and courage to write about something that didn't work. We all thank you.
A) it proves we're all human and we try hard at IT but sometimes we mess it up.
B) we learn MORE about how we solve problems when we read about something that didn't go well than when something went perfectly.
C) AND we learn more about the creative ways to escape problems (like the DAC) the we never would have learned about otherwise (I tried it and wow! it works! Who knew?).

Richard
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 8971
I'm glad one of our biztalk devs is very cooperative today Wink

Scenarion 1 : add
begin tran ... commit tran


Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.
Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.

When I added a while loop Crazy

while @@trancount > 0
begin
commit tran
end


resulted in a failure of the biztalk apps.


Scenario 2:
set transaction isolation level read committed


IMO this one would be plausible, but once again, it resulted in messages like
Process ID 61 attempted to unlock a resource it does not own: DATABASE: 7 Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

and sqlserver got itself messed up once again.

Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19360 Visits: 9518
ALZDBA (12/19/2008)
Scenarion 1 : add
begin tran ... commit tran


Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.
Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.

When I added a while loop Crazy
while @@trancount > 0
begin
commit tran
end


resulted in a failure of the biztalk apps.


Try it like this:

Commit Tran
IF @@trancount = 1
Begin
Commit Tran
Begin Tran
End



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 8971
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19360 Visits: 9518
Hmm, you might want to figure out what kind of LOGON this is before you do that then...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 8971
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19360 Visits: 9518
Yeah, I think that you're right.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12526 Visits: 2048
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search