Listing running/blocking processes

This recipe lists processes in your SQL Server instance and their status.

Getting ready

In order to see blocking processes in your list, we will have to force some blocking queries.

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.

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...

Let's see how to list running and blocking processes in SQL Server using 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
   
   #replace this with your instance name
   $instanceName = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName...