How to stimulate a SQL Server resource  failure in cluster?

If you like to perform a check in SQL Server cluster to identify if the SQL Server resources are failing over properly follow the below steps. you can use Step 2 to create a SQL Server hang and deadlocked scheduler dump in standalone server as well.

1.       Create a instance failures scenario by  issuing “shutdown” command to SQL Server which will terminate/Shutdown SQL Server. SQL Server resource DLL in cluster should detect that the SQL Server have failed and start the SQL Server resource automatically.

Steps

a.       Connect to SQL Server using an account which has Sysadmin privilege and execute Shutdown command.

2.       Create a SQL Server hang scenario by creating excessive blocking, once SQL Server exhaust all the available threads SQL Server will stop accepting connections and will go unresponsive. SQL Server resource in the cluster should detect that the SQL Server resource have failed and restart/failover SQL Server.

Steps

a.        Connect to SQL Server and below SQL Statements. (Do not close the session)

Create database test;
use test;
create table block  (a char(10));
insert into block values ('Test');
go 10
begin transaction;
update block set a ='Test1'

b. Download the Ostress.exe

c. Execute the following command in ostress.exe

ostress.exe -Sservername\Instancename -E -d test -Q "select * from test..block" -n 2000

Note: Replace servername\instancename with your server name and instance name. Above Ostress command creates 2000 connections and execute “select * from tempdb..block” which will in turn cause excessive blocking and make SQLServer exhaust all the available worker threads.

Is Alive check will fail once the threads are exhausted causing SQL Server to reach hung state. SQL Server resource DLL’s should detect that SQL Server is hung and restart/failover SQL Server.

 If you do not know what is SQL Server LooksAlive and IsAlive Check follow This blog