|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 8,401,
Visits: 7,823
|
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 4,754,
Visits: 3,530
|
|
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.
 Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere 
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 8,401,
Visits: 7,823
|
|
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".
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 4,754,
Visits: 3,530
|
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, October 26, 2009 6:09 PM
Points: 202,
Visits: 433
|
|
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
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 4,754,
Visits: 3,530
|
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:10 PM
Points: 8,401,
Visits: 7,823
|
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, October 26, 2009 6:09 PM
Points: 202,
Visits: 433
|
|
WOW, that sounds a bug of sql server 2k5 itself. I am glad we have SP2 + CU10. 
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 4,754,
Visits: 3,530
|
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 4,754,
Visits: 3,530
|
|
|
|
|
