Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Scope: The drastic caveat with Logon Triggers. Expand / Collapse
Author
Message
Posted Tuesday, December 23, 2008 10:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #624861
Posted Wednesday, December 24, 2008 4:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:21 AM
Points: 6,743, Visits: 8,517
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


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 #625283
Posted Wednesday, December 24, 2008 8:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #625413
Posted Sunday, December 28, 2008 9:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:21 AM
Points: 6,743, Visits: 8,517
I'll see what I can do ...

I'm not that the office until Jan 5th ...



Johan


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 #626290
Posted Sunday, December 28, 2008 11:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:25 PM
Points: 229, Visits: 719
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




Post #626312
Posted Wednesday, January 7, 2009 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:21 AM
Points: 6,743, Visits: 8,517
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


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 #631245
Posted Wednesday, January 7, 2009 7:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
They sure don't say much about it though, do they?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #631427
Posted Wednesday, January 7, 2009 1:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:25 PM
Points: 229, Visits: 719
WOW, that sounds a bug of sql server 2k5 itself. I am glad we have SP2 + CU10.


Post #631841
Posted Wednesday, January 7, 2009 1:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:21 AM
Points: 6,743, Visits: 8,517
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


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 #631864
Posted Wednesday, January 7, 2009 1:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:21 AM
Points: 6,743, Visits: 8,517
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"


Johan


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 #631867
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse