Sql Server Cluster : Clustered role ‘Cluster Group’ has exceeded its failover threshold. It has exhausted the configured number of failover attempts within the failover period of time

Interesting one today:

In our lab environemnt, one of the SQL cluster environment ran into this error.

 

 

Error Message:

Clustered role 'Cluster Group' has exceeded its failover threshold.  
It has exhausted the configured number of failover attempts within 
the failover period of time allotted to it and will be left in a 
failed state.  
No additional attempts will be made to bring the role online or fail 
it over to another node in the cluster.  Please check the events 
associated with the failure.  After the issues causing the failure are 
resolved the role can be brought online manually or the cluster may 
attempt to bring it online again after the restart delay period.

The Cluster service failed to bring clustered role 'Cluster Group' 
completely online or offline. One or more resources may be in a 
failed state. This may impact the availability of the clustered role.

Resolution:

Errors like this are more common in Lab environments than in production environment. In any case, if you encounter the same error in production environment, then take extra caution before you follow these steps.

Possible Root Cause:

In lab sometimes, as part of some other effort, we inadvertently end up failing over the cluster several times within a short period of time. There is a setting in Cluster that measures the failover count.

• If that count hits a particular threshold, it flags the Resource Group as ‘Failed’ state
• And creates an entry in the the Cluster Events, that Cluster Resource Group failed after reaching the threshold (see the error message : Clustered role ‘Cluster Group’ has exceeded its failover threshold)

Resolution Steps:

According to this MSDN post, we could alter that failover count threshold to allow the resource group to come back up in a healthy state.

 

Step 1:

Go to Failover Cluster Manager >> Roles >> right click on the Resource Group and to go Properties:

• Change the Maximum failures in a specified period to a larger number to account for the repeated failovers in recent hour.

 

 

Step 2:

Go to Failover Cluster Manager >> Roles >>

In the bottom portion of the window where we the individual resources are listed, right click on the Resource that is in failed state and go Properties:

• Increase the Maximum restarts in the specified period setting to a larger number to account for recent restarts.

 

 

NOTE: This is not a standard solution for production environments.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to apply multiple conditions with the same XQuery in XML DML

Interesting one today:

In the past, we’ve seen multiple posts on XML DML covering several XML manipulation capabilities. Today, we’ll cover another aspect of XQuery : Ability to add multiple conditions within the same XQuery path.

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
ID INT NOT NULL IDENTITY(1,1)
, XML_Col XML
)
GO

--
-- Create XML value
--
DECLARE   @X XML = '

				Marco
				Polo

				Hello 16
				World 16

				Hello 17
				World 17

				Hello

'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

The sample XMl looks like this:

And in it we’ll be querying the following things:

1. Using Single Condition
   1. Query the number of ParentElement under Root
   2. Query the number of ParentElement with @Att1 = “Color”
      1. Answer: 1
   3. Query the number of ParentElement with @Att1 = “Brand”
      1. Answer: 2
   4. Retrieve element text of ParentElement with @Att1 = “Brand” for Child1
      1. Answer: Hello 16
2. Using Two conditions
   1. Retrieve element text of ParentElement with @Att1 = “Brand” and @Att2=”17″ for Child1
      1. Answer: Hello 17

--
-- XML Query to retrieve with multiple conditions in XQuery<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
SELECT	  XML_Col.value('count(/Root/ParentElement)', 'INT')													AS [NumberOf_ParentElements]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Color"])', 'INT')										AS [NumberOf_ParentElements_Color_Attribute]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Brand"])', 'INT')										AS [NumberOf_ParentElements_Brand_Attribute]

		, XML_Col.value('(/Root/ParentElement[@Att1="Brand"]/Child1/text())[1]', 'VARCHAR(30)')					AS [Child1_Text_in_Brand]
		, XML_Col.value('(/Root/ParentElement[@Att1="Brand" and @Att2="17"]/Child1/text())[1]', 'VARCHAR(30)')	AS [Child1_Text_in_Brand_and_17]

FROM dbo.SampleXML
GO

Output looks like this:

 

Hope this helps,
_Sqltimes

 

Read Full Post »