Split-Brain/tiebreaker in Cluster

HA clusters usually use a heartbeat private network connection which is used to monitor the health and status of each node in the cluster. One difficult, but serious condition every clustering software must be able to handle is split-brain. Split-brain occurs when all of the private links go down simultaneously, but the cluster nodes are still running. If that happens, each node in the cluster may mistakenly decide that every other node has gone down and attempt to start services that other nodes are still running. Having duplicate instances of services may cause data corruption on the shared storage.

It's also used as a tiebreaker when nodes can no longer communicate (that is, are "split-brain"). When it cannot communicate with the nodes, Cluster Service cannot really detect the problem: It's possible that the nodes are dead, but it may also be possible that just the communication links are. In this situation, to prevent each node from thinking that it is the sole survivor and bringing your database online, they go into arbitration, using the quorum resource.

The node that owns the quorum resource puts a reservation on the device every three seconds; this guarantees that the second node cannot write to the quorum resource. When the second node determines that it cannot communicate with the quorum-owning node and wants to grab the quorum, it first puts a reset on the bus.

The reset breaks the reservation, waits for about 10 seconds to give the first node time to renew its reservation at least twice, and then tries to put a reservation on the quorum for the second node. If the second node's reservation succeeds, it means that the first node failed to renew the reservation. And the only reason for the failure to renew is because the node is dead. At this point, the second node can take over the quorum resource and restart all the resources.

Reference: http://technet.microsoft.com/en-us/library/bb742593.aspx

Sign Up Procedure for Interview Agent

Interview Agent

[caption id="attachment_616" align="aligncenter" width="600" caption="Call on +91-9881710142 for more information"][/caption]

SQLDBPool has now its own domain

Dear Readers,

Now you dont have to write the long http://sqldbpool.wordpress.com/ URL, We have now our own domain. You can now check your favourite articles by just typing http://sqldbpool.com/

Thank You

Best Regards,
Jugal Shah
jugal.shah@sqldbpool.com

Error: 1101, Severity: 17, State: 2.

Error: 1101, Severity: 17, State: 2.
Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

We have configured the tempdb on J:\ which has only 10GB space. TempDB has occupied the 10GB space. We have tried to shrink the tempdb but no luck, after sometime we are not able to even right click the TempDB and neither execute the some system stored procedures (sp_who2)

Solution 1
If you don’t want to restart the SQL Server you can add the another data file to different drive in tempdb

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev1,
FILENAME = 'E:\tempdb1.mdf',
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
GO

Solution 2
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.

Solution 3
Restart the SQL Services, it will create the new instance of tempdb

Solution 4
For user databases with restricted growth you can use the AUTO GROWTH option true or increase the file max size

Note: You can't move the tempdb files, if you execute the ALTER command to move the tempdb files, it will just mark the file to diffrent drive and will create the file the target drive on SQL Server restart only

How to move data and log file using Alter statement

You can use Alter command to move data and log file.

Sample Script

-- Moving data file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = tempdev,FILENAME = 'E:\tempdb.mdf')
-- Moving log file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = templog,FILENAME = 'E:\templog.ldf')