CREATE BLOCKING IN SQL-SERVER
using T-SQL Script

Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.

Let us use the database AdventureWorksLT
Let us select the table "[SalesLT].[Address]"
The below image shows what all collums are listed in the table when we sun a select statement.

Step 1:Create blocking

Now Let us create blocking on this by running the below script

BEGIN TRAN
UPDATE [AdventureWorksLT].SalesLT.Address 
SET City = 'Dallas'
WHERE AddressID = 9

we did not commit the transaction so the SQl-server is expecting the commit to release the lock

Step 2: Access the same table in a different query window

Try to access the same table
At this point when one table is under updation the table gets locked for updation inorder to simulate that we need to open a new query window and run the below script which will keep waiting.

SELECT * FROM [AdventureWorksLT].[SalesLT].[Address]

Step 3:

Now if you run a select statement in a new window it will keep waiting for ever as shown below

Step 4:

Lets check for blocking by running the below statement's

DBCC OPENTRAN

From the image we can see that the Sometimes this does does not work and is currently an open issue with the below versions of SQL-Server

SQL-server 2005
SQL-server 2008
SQL-SERVER 2012

Step 5:

hence instead of depending on the "DBCC OPENTRAN" Command we can use alternatives like the below for locating the blocker
We can run the below command

Select * from sys.sysprocesses where open_tran>0;

when we run the below comannad we can clearly identify
1)The SPID which has locked the tabele.
2)The SPID which is waiting for the lock to be released
so The image below clearly shows that SPID 53 is the lead blocker which blocked SPID 56

Step 6:Verifying

let us verify whatt SPID Belong's to what


dbcc inputbuffer (56)
DBCC INPUTBUFFER (53)

Step 7:

Now before we clear that blocking by "KILL" COMMAND
let us verify the following by running the below queries.

from the above Image its clearly evident that SPID 56 is the main culprit open transaction which is causing the blocking.

Step 7:kill

now lets kill the process by running the below code

KIll (56)

Step 3:Verifying the block removal

After we kill the process we will automatically see the the query getting executed

you can download the Script here here

SQL-Server Scripts

Object Level Permissions

BLOCKING

View wait stats for all block processes on SQL Server

SQL-SERVER

CREATE BLOCKING IN SQL-SERVER using T-SQL Script