Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cluster Service Restarts Automatically


Cluster Service Restarts Automatically

Author
Message
JeeKay
JeeKay
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 104
Hi,

I started having this issue since a couple of weeks and my quest for an answer sadly remains futile. My Windows logs show that the Cluster service was restarted at about the same time when my SQL Server logs show a couple of reports causing deadlocks due to a poorly designed application that is "business critical". Has anyone out there has any suggestions or experiences to share ?

The locking error source has a spid4 and then the error "SQL Server Service terminates due to a request from the Service Manager" with a source spid 3. These errors are in the SQL Server Error Logs.

Platform Windows Server 2003 Ent. SP2
SQL SQL 2000 Ent. SP4
Windows 2-node Active / Passive Cluster.

Thank you,
JeeKay
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
The only thing I can think of is that the cluster service fails to get a connection when trying to do an IsAlive check on SQL, concludes that the SQL instance is dead and fails it to the passive node.

You could try increasing the grqace period before it decides to fail over. Speak to whoevr's in charge of the cluster administration.

Best option, of course, would be to try and fix those deadlocks. Is that an option?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


MannySingh
MannySingh
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 777
can you post the details as in SQL Server error log.

Maninder
www.dbanation.com
JeeKay
JeeKay
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 104
Hi,
The deadlocking is due to the application being designed around a single table and there are hundreds of connections to it. I tried giving the reporting guys a different box with an SSIS package that runs a couple of minutes before that gets the data transferred across from Production into the Reporting box. Tried to force a Query Governor limit to stop long running queries. But even then this continues. I can ask the Cluster Admin to increase the time if thats the only option. Any suggestion welcome.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
Post the table design, index definitions and some of the queries that deadlock with each other?

Have you considered snapshot isolation level?

A single table design is bad, but it might be slightly fixable.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


JeeKay
JeeKay
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 104
Hi,
DO you have the different types of Isolation Levels in SQL 2 K like 2005 ? I was not aware. I will try to do some reading on that. This is on SQL 2K and to make matters worse it has this crap design. Since the design change is not an option, I have been trying to think of various options like killing a blocking transaction if its blocking for more than a period of time, query governor limits etc. Do you have any advice on how to get rid of a blocking lock with Rollback or Commit ? This should ensure DB consistency since the data is so mission critical. I did reindexing and Update Stats. many times but that was never fruitful because of the design flaw.

Thank you,
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
JeeKay (7/14/2008)
Hi,
DO you have the different types of Isolation Levels in SQL 2 K like 2005 ? I was not aware. I will try to do some reading on that.


Sorry, it doesn't. I looked at the forum this was posted in and missed your version note. (btw, SQL 2000 posts should go into the SQL 2000 frums, for precisely that reason)

This is on SQL 2K and to make matters worse it has this crap design. Since the design change is not an option, I have been trying to think of various options like killing a blocking transaction if its blocking for more than a period of time, query governor limits etc.


Post your table design, indexes and some of the worst offending queries please. I like fixing deadlocks but is hard to offer solutions without seeing the details.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ian Scarlett
Ian Scarlett
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 6255
DO you have the different types of Isolation Levels in SQL 2 K like 2005 ?


You don't have snapshot isolation in SQL 2000, but you do have "READ UNCOMMITTED".

That may be an option for any of your users who are only reading data.

This isolation level does have its drawbacks (e.g. reading data that may be rolled back if a transaction is aborted, the possibility of reading the same data twice etc), so make sure you understand them before using it.



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