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

Cluster Service Restarts Automatically Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:44 PM
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
Post #533452
Posted Monday, July 14, 2008 7:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #533484
Posted Monday, July 14, 2008 7:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
can you post the details as in SQL Server error log.


Maninder
www.dbanation.com
Post #533498
Posted Monday, July 14, 2008 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:44 PM
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.
Post #533521
Posted Monday, July 14, 2008 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #533525
Posted Monday, July 14, 2008 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:44 PM
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,
Post #533589
Posted Monday, July 14, 2008 11:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #534043
Posted Tuesday, July 15, 2008 2:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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.



Post #534129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse