Archive for the ‘Sql Server 2008’ Category

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.


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,

Read Full Post »

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 XML value


				Hello 16
				World 16

				Hello 17
				World 17


-- INSERT XML value into table column

-- See the result
FROM dbo.SampleXML

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

Output looks like this:



Hope this helps,


Read Full Post »

Interesting one today:

Occasionally we need to connect to Sql Server over Windows Authentication using different Windows user. This is a good step to manually check a few things about the validity or privilege level for that other Windows Account (there are other ways too).

One of the quickest way is to Connect to Sql Server over Windows Authentication using the other Windows account.

By default, when you open SSMS (Sql Server Management Studio) under Windows Authentication, it pre-populates our current windows account. There are a couple of options to workaround that.

Solution 1: Use runas

From a windows command prompt or a Run window, run the following runas command with the other Windows user

runas /user:domain\user Ssms.exe

New SSMS window opens up with the above mentioned Windows User account pre-populated.

Solution 2: Use RunAs from windows menu

Same steps as above, but we use windows user interface to perform the same steps.

Right click on SSMS and go to pop-up menu:


On “Microsoft Sql Server Management Studio 17“, press Shift + Right click


On the new SSMS, you could specify the new Windows Account (or Domain Account) to use for login.

Hope this helps,


Read Full Post »

Quick one today

Once in a while, we need to process XML files (a.k.a. deadlock XML files) to retrieve some pertinent information to uncover the deadlock patterns.

In the past, we’ve seen some XML DML queries to parse XML files.

Here we’ll focus on filtering capability based on attribute values in elements:

Take an deadlock XML for example:


The goal is to search for the action element with attribute value as “collect_system_time“.

-- Parse & Filter XMl file data
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/action[@name="collect_system_time"]/value)[1]', 'DATETIME') AS SystemTime
FROM  dbo.Deadlocks_07262018_Step2

The [] allow us to provide the value to filter the XMl elements:



Hope this helps,


Read Full Post »

Quick one today:

Quite often, we all run into this scenario where we need to convert column data into row with either comma or some other delimiter.

Below, we’ll see a couple of ways; Each serving a different purpose:

Build data set first:

--  Convert row data into column (without using PIVOT)

-- Create tables
	, Name	VARCHAR(20)

	, ParentID	INT
	, Name		VARCHAR(25)

-- Create Parent records
INSERT INTO Parent (Name) VALUES ('Mary')
INSERT INTO Parent (Name) VALUES ('Patel')
INSERT INTO Parent (Name) VALUES ('Risvic')

-- Create child records
INSERT INTO Child (ParentID, Name) values (1, 'Jil')
INSERT INTO Child (ParentID, Name) values (1, 'Jim')
INSERT INTO Child (ParentID, Name) values (1, 'Kat')

INSERT INTO Child (ParentID, Name) values (2, 'Raja')
INSERT INTO Child (ParentID, Name) values (2, 'Kamat')


Data set looks like this:


Option 1 : Using Variable

This method gives us result in comma delimited fashion, but it only works for one parent at a time; And we cannot combine Parent name as a column next to Children column, as Sql Server does not allow combining data-retrieval with variable manipulation query.

It spits out an error like this:

Msg 141, Level 15, State 1, Line 35
A SELECT statement that assigns a value to a variable must not be 
combined with data-retrieval operations.
--	Option 1 : using @Variable
DECLARE @Children VARCHAR(8000)

SELECT @Children = COALESCE(@Children, '') + ', ' + Name
FROM dbo.Child
WHERE ParentID = 1

SELECT STUFF(@Children, 1, 2, '')


Option 2 : Using XML

XML PATH gives us more control over retrieval manipulation. It allows us to retrieve data in a tabular fashion, while keeping the children names into one column.


This below query has 3 parts:

  1. Main warp-around query (Parent)
  2. STUFF section
  3. XML PATH section
--	Using XML
SELECT    P.ID	AS [ParentID]
	, P.Name	AS [ParentName]
					SELECT ', ' + C.Name
					FROM dbo.Child AS C
					WHERE C.ParentID = P.ID
					FOR XML PATH('')
				, 1, 2, ''
		  ) AS [Children]
FROM dbo.Parent AS P

-- Clean up


We’ll see the details of each query in reverse order:


This is the most important part of this whole query. It allows us to retrieve all children of a given parent in a comma delimited fashion.

Without FOR XML PATH(”) statement, this is just a query that returns children as rows. When you add FOR XML, that data set is now converted into XML format (sort of).

PATH (”) makes sure that the the element trees notation is replaced with whatever is in between ” (empty). So we get a simple comma delimited child list.

Now we have an extra comma to remove. That’s where STUFF comes into play.

STUFF query section replaces comma with empty space.

Main wrap-around query is to bring this all together with ParentID & Parent Name to make it more usable.


Hope this helps,


Read Full Post »

Quick one today:

Earlier, while troubleshooting deadlocks, we needed a quick way to count the number of victims & processes in each deadlock incident. Since deadlock information is available in XML, we could use COUNT function to measure the occurrences.


In the past, we’ve covered some techniques to uncover details from deadlock XML data.

XML provides many tools to parse XML files. Below query makes it easy to gather such counts at each level; Be it Victim count or processes count, etc

	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT') AS [Count_Victims]
FROM dbo.DeadlocksXMLTable

We could send XML Path we want to count as a parameter to COUNT function to gather the number.

DeadLockXML.value(‘count(/event/data/value/deadlock/victim-list/victimProcess)’, ‘INT‘)
Below is an expanded TSQL query that includes more columns from deadlock XML file.

	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2')																		AS [TimeStamp]
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT')										AS [Count_Victims]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'VARCHAR(100)')							AS [Victim1]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[2]', 'VARCHAR(100)')							AS [Victim2]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[3]', 'VARCHAR(100)')							AS [Victim3]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[4]', 'VARCHAR(100)')							AS [Victim4]
FROM dbo.DeadlocksXMLTable


Hope this helps,

Read Full Post »

Interesting one today:

Earlier, when performing some cluster maintenance work, this error popped-up on the screen.

The specified disk or volume is managed by the Microsoft Failover cluster 
component. The disk must be in the cluster maintenance mode and the 
cluster resource status must be online to perform this operation

When attempting to format a new LUN to 64K allocation unit, this error popped up. Since the LUN/Drive is already added to the cluster, new format changes could not be made.


As the verbose error message suggests, assign this particular disk into “Maintenance Mode”, then perform formatting steps.

Go to Failover Cluster Manager and go to Storage > Disks; Identify the particular disk and right click and go to More Actions >> Turn On Maintenance Mode.


Once the disk is in maintenance mode, you’ll see under Status column as Online (Maintenance Mode).

Now we are free to format the disk. Computer Management >> Storage >> Disk Management go to the individual disk and right click and Format.


Now formatting works and once completed, go back to Failover Cluster Manager and set the disk back out of Maintenance Mode.


Hope this helps,

Read Full Post »

Older Posts »