<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Johan Bijnens
»
Scope: The drastic caveat with Logon...
34 posts, Page 1 of 4
1
2
3
4
»
»»
Scope: The drastic caveat with Logon Triggers.
Rate Topic
Display Mode
Topic Options
Author
Message
ALZDBA
ALZDBA
Posted Saturday, December 06, 2008 6:39 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 6,862,
Visits: 8,049
Comments posted to this topic are about the item
Scope: The drastic caveat with Logon Triggers.
Johan
Jul 13
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
-
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"
Need a bit of Powershell? How about
this
Who am I ?
Sometimes this is me
but
most of the time this is me
Post #615129
Phil Factor
Phil Factor
Posted Monday, December 15, 2008 3:29 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533,
Visits: 2,285
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
Post #619449
Anipaul
Anipaul
Posted Monday, December 15, 2008 3:50 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 4,787,
Visits: 1,336
Nice article...
Post #619460
ALZDBA
ALZDBA
Posted Monday, December 15, 2008 3:52 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 6,862,
Visits: 8,049
Thank you for the comments.
Fortunately it didn't take me that much time to figure out the issue
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
,
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
Jul 13
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
-
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"
Need a bit of Powershell? How about
this
Who am I ?
Sometimes this is me
but
most of the time this is me
Post Attachments
sp_DBA_WhoBlocks.sql.txt
(
24 views,
10.37 KB
)
Post #619463
RBarryYoung
RBarryYoung
Posted Monday, December 15, 2008 11:15 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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."
Post #619830
RBarryYoung
RBarryYoung
Posted Monday, December 15, 2008 11:21 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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."
Post #619835
noeld
noeld
Posted Monday, December 15, 2008 11:21 AM
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260,
Visits: 1,977
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
Post #619836
ALZDBA
ALZDBA
Posted Monday, December 15, 2008 12:16 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 6,862,
Visits: 8,049
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.
Secondly, considered the absence of the need to interfere in realtime, the only valid approach is the one using sqlserver events.
Johan
Jul 13
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
-
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"
Need a bit of Powershell? How about
this
Who am I ?
Sometimes this is me
but
most of the time this is me
Post #619885
Daniel Ruehle
Daniel Ruehle
Posted Monday, December 15, 2008 12:39 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:21 AM
Points: 158,
Visits: 83
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.
Post #619898
alexander.plepler
alexander.plepler
Posted Tuesday, December 16, 2008 2:10 AM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, August 05, 2012 12:55 AM
Points: 1,
Visits: 26
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.
Post #620234
« Prev Topic
|
Next Topic »
34 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
