Scope: The drastic caveat with Logon Triggers.

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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

  • 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

  • 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]

  • WOW, that sounds a bug of sql server 2k5 itself. I am glad we have SP2 + CU10. 🙂

  • 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

  • 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 35 total)

You must be logged in to reply to this topic. Login to reply