Close Menu

SQL-SERVER

 


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