December 8, 2010 at 10:01 pm
Our SQL Server stopped processing deadlocks.
We are working to implement a third party application that currently generates in the region of 20-30 deadlocks a minute. Obviously we're not happy with that and have been monitoring the deadlocks closely as different attempts are made to resolve the issue. One night (12:37am), the server stopped reporting deadlocks (we use Event Notification via Service Broker). There was no maintenance, admin or unusual activity that we can relate to this time - and no error messages logged.
Looking into this further, we noticed that the processes normally involved in the deadlock were hung in a suspended state. Terminating the hung processes and restarting the offending job resulted in a repeat....processes hung, suspended state. To test this further, I connected remotely with SSMS and generated a 'test' deadlock. Same result - two hung processes, both with status of suspended. The hung processes will stay in this state permanently (we've them them go for 12hrs+). It's as though the deadlock monitor thread was not processing them. There are no errors in the server event logs, no errors in the SQL Error logs..etc
Eventually we restarted the SQLServer service......and everything is back to 'normal'. Our environment is SQL Server 2005 x64 Enterprise SP3 (9.0.4053) on Windows 2003 Server Enterprise x64 SP2.
I'm interested if any one has experienced this, or knows of a resolution other than restarting the SQLServer service.
Cheers
December 10, 2010 at 5:26 am
You use "Snapshot Isolation"
http://msdn.microsoft.com/library/tcbchxcb%28v=VS.80%29.aspx
December 12, 2010 at 12:45 pm
Thanks egjorda - interesting thought, but in this case I don't think that snapshot isolation is the cause.
The 'issue' started just after midnight with no user intervention, and reverted back after we restarted the SQL Server service.
Also, ALLOW_SNAPSHOT_ISOLATION is OFF.
Cheers
December 12, 2010 at 1:23 pm
I would suspect some malfunctions in your SSB environment and start SSB debugging actions.
- did ERRORLOG indicate a queue shutdown or something you can link to event notifications ?
- have a look at 'troubleshooting ssb' at http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
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
December 12, 2010 at 7:06 pm
Hi ALZDBA
My first thoughts were also that there was a problem with the SQL Service Broker(SSB). However, in addition to the deadlock events, we also use SSB to notify on other events such as auto grow/shrink, login failures...etc. These other events continued to be notified and processed as normal.
I also suspect that an SSB issue is unlikely to prevent SQL Server recognising deadlocks. SQL would continue to manage the deadlocks but the notifications wouldn't occur. In my case - when they occured, the deadlocks weren't recognised and the processes simply entered a permament 'hung' state. SQL Server never chose a 'victim' for termination.
Thanks for the suggestion
December 13, 2010 at 12:29 am
- So, do your apps still receive deadlock errormessages ?
- can you enable the traceflags to write the deadlock info into sqlservers errorlog ( 1222,.. ) ?
dbcc traceon(1222, -1)
btw todays article handles deadlock capture as well: http://www.sqlservercentral.com/articles/deadlock/71374/
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
December 13, 2010 at 1:00 pm
Thanks for your suggestions, but at this stage there really isn't a problem that needs solving. In fact, I'm not aware of any way to reproduce it. I suspect I encountered some sort resource limitation, configuration threshold, or possibly a bug. I was hoping someone may have recognised the symptoms.
Fortunately, after restarting the SQL Server service, everything is back behaving as it should. A shame in some ways though as it would have been great to track down exactly was going on.
The article you mentioned certainly offers a great way to recieve deadlock information, but as we already have an Event Notification/Service Broker solution in place it would be 'double doing'....
Cheers
December 14, 2010 at 4:54 am
No problem. 😉
In many cases production continuity is more important than going for the bug.
However, if this re-occurs, you may have to invest time to figure out why it stops reporting deadlocks.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply