Click here to monitor SSC
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
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
Comments posted to this topic are about the item Scope: The drastic caveat with Logon Triggers.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 2949
Johan,

Great article. That looks like quick thinking. How long did it take to figure out the Isolation-level problem? There is a moral for everyone in this story. DAC will, some day, save your bacon.


Best wishes,

Phil Factor
Simple Talk
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6279 Visits: 1407
Nice article...



ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
Thank you for the comments.

Fortunately it didn't take me that much time to figure out the issue Whistling
I'm always using my version of sp_who to see what's going on in an instance.

That proc shows me isolationlevels, ....

I'm one of the happy few that has a mainframe background w00t,
so one of the first points of interest is the isolation level of a connection.

Also ... one of the error log messages stated : ...attempted to unlock a resource it does not own...
which may point to a transaction related problem.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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
Attachments
sp_DBA_WhoBlocks.sql.txt (26 views, 10.00 KB)
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9454 Visits: 9517
Hmm, I wonder if starting a local transaction and then committing it before the trigger exited would have resolved this?

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

Group: General Forum Members
Points: 9454 Visits: 9517
I agree with you Johan, it is important to remember that unlike their corresponding DDL events, DDL triggers do execute synchronously and in the source execution context. Consequently they do always have some effect on the source process and activity.

My general rule of thumb is that you should always prefer to use DDL Event notification over DDL triggers, unless you actually do want to have some effect on the triggering process or command (such as intentionally rejecting certain Logins).

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

Group: General Forum Members
Points: 6324 Visits: 2048
Excellent war story.

I think Rbarryyoung is right on the money. I believe that the lack of explicit transaction demarcation is the real problem.
Do you mind specifying explicit "begin tran"/"commit tran" ? It should work, right?


* Noel
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
rbarryyoung (12/15/2008)
I agree with you Johan, it is important to remember that unlike their corresponding DDL events, DDL triggers do execute synchronously and in the source execution context. Consequently they do always have some effect on the source process and activity.

My general rule of thumb is that you should always prefer to use DDL Event notification over DDL triggers, unless you actually do want to have some effect on the triggering process or command (such as intentionally rejecting certain Logins).


noeld (12/15/2008)
Excellent war story.

I think Rbarryyoung is right on the money. I believe that the lack of explicit transaction demarcation is the real problem.
Do you mind specifying explicit "begin tran"/"commit tran" ? It should work, right?




Well, I will try to test it in collaboration with my biztalk dev team.

But I think it would be over simplified to just incorporate the explicit "begin tran"/"commit tran". E.g. when using implicit transactions, one should also have to check for the @@trancount !, and then basically violate the concept of the implicit transaction itself.Ermm

Secondly, considered the absence of the need to interfere in realtime, the only valid approach is the one using sqlserver events.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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
Daniel Ruehle
Daniel Ruehle
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 86
Could you not have just added:

set transaction isolation level read committed

after the "set nocount on" in the trigger to set the isolation level for the duration of the trigger execution. Surely using the events is ultimately better, but that should have fixed it.
alexander.plepler
alexander.plepler
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 47
Very useful and dramatic story!
The asynchronous approach is always preferable for me so long as I don't like to do something immediately (e.g. rollback or blocking).
Thanks for share.
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