Killing a blocking process

This recipe illustrates how you can kill a blocking process in SQL Server.

Getting ready

In order to see blocking processes in your list, we will have to force some blocking queries. If you have already done the prep work in the Listing running/blocking processes section, you do not need to do this prep section. If you haven't, go ahead and perform the following steps:

1. Open SQL Server Management Studio and connect to the instance you want to test. We will assume you have AdventureWorks2014. If not, you can use a different database and table altogether.

2. Open two new query windows for that connection. Type and run the following in the two query windows:

   USE AdventureWorks2014
   GO
   
   BEGIN TRAN
   SELECT *
   FROM dbo.ErrorLog
   WITH (TABLOCKX)

How to do it...

These are the steps to kill a blocking SQL Server process in PowerShell:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking...