Cluster Service Restarts Automatically

  • 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

  • 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
  • can you post the details as in SQL Server error log.

  • 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.

  • 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
  • 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,

  • 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
  • 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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply