Clustering with problem database

Question

Clustering with problem database

mart 13485

Grasshopper

Points: 16
More actions
June 20, 2016 at 5:52 pm

#327192

We have an existing SQL cluster (two-node active/passive) that's been in place for a few years and has been very stable. After the recent addition of a new database, we have had to restart the entire database server to fix a problem with that one database. The vendor is looking into the issue, but the decision has been made to remove the database from the cluster, which will require a separate SQL cluster for this one application.
I don't want to wake up in two years and find 10 unique SQL clusters in our small environment. This leads me to a couple [SQL newbie] questions:
Are there any cluster configurations that permit me to quarantine problem databases to their own nodes in a cluster proactively?
Are there any cluster configurations that permit me to reactively migrate all functioning databases to another active node so that a node reboot only impacts that already affected database?

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

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

Perry Whittle SSC Guru Points: 233846 More actions · Answer 1

what is the issue with the database, what information is in the logs?

You could stand up a new failover cluster instance in the existing cluster and move the database there. That would not require any new nodes but just the disk resources and IP address and computername to support the new instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Haven't heard this, but I agree with Perry. What's the issue?

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 3

What is 'a problem'? Why did you have to restart? Was it to fix access to a cluster disk?

Eddie Wuerch
MCM: SQL

mart 13485 Grasshopper Points: 16 More actions · Answer 4

Thanks for the responses. We had to restart SQL Server, affecting all databases, because one of the tables had a lock on it that we were unable to clear any other way. Can you tell me more about standing up a new failover cluster instance in an existing cluster?

Smendle Hall of Fame Points: 3969 More actions · Answer 5

mart 13485 (6/23/2016)
Thanks for the responses. We had to restart SQL Server, affecting all databases, because one of the tables had a lock on it that we were unable to clear any other way. Can you tell me more about standing up a new failover cluster instance in an existing cluster?

In my best Peter Griffin voice...Whoa whoa whoa....Whoa

You had a lock on a user table that could only be released by a restart of SQL Server? I would love to look at that problem a little more before saying you need to create a separate instance/server for this database because of that reason.

Did you kill all connections that had locks to the table in question? Basically that alone should have freed up locks so you could continue.

This of course does not solve the problem only the symptom.

There are MANY things you can do to alleviate locks against a table.

Two off the top of my head are.

1. Disable lock escalation (check the web on the consequences of doing this)

2. Create full page records for 1 row on this table, this will effectively disable page locking on that table as each row will be its own page and unless you have more than 1 connection dealing with the same row, no locks, downside is more space is needed per row, obviously.

Could you tell us what you did or what you looked at before restart SQL Server?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

One of the easiest and fastest ways to clear all connections to a database is to use the following code (change the DB name, of course)... read the comments because there is a danger in doing so... and, keep in mind that this kills EVERYTHING with great prejudice. Any long-running transactions will take time to rollback. It also kills connections made by jobs. I'll say it again... it KILLS EVERYTHING. Use with care.

USE [master]

GO

--===== This kills everything except the connection running this command.

ALTER DATABASE [YourDbNameHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--===== This sets it back to normal so that, if you lose the connection,

-- some app doesn't grab the only connection locking you out from it.

-- As an alternative, you could set it to restricted users but that

-- will also still let some apps in if they inappropriately have

-- the restricted privs

ALTER DATABASE [YourDbNameHere] SET MULTI_USER;

--===== Then, do your stuff immediately and without delay.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233846 More actions · Answer 7

mart 13485 (6/23/2016)
Thanks for the responses. We had to restart SQL Server, affecting all databases, because one of the tables had a lock on it that we were unable to clear any other way. Can you tell me more about standing up a new failover cluster instance in an existing cluster?

Moving the database to a new instance is not going to make the issue go away and you certainly shouldn't be restarting the whole instance to remove a troublesome object lock!

You must find out what's locking the resources and resolve this

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉