Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

How to know who is blocking a query in Sql Server

Posted on September 15, 2019June 14, 2020 By

It’s been a while since we last heard… The Dummy Programmer was really really busy and couldn’t write any post. But now I’m back to talk about a problem I came across.

One of my applications in the production environment seemed to be stuck doing a series of database queries.

My suspicion is that some other database process was blocking my queries by opening a transaction on the tables I was using.

How could you check this?

One simple way is executing the system stored procedure “sp_who2”, which provides information about the processes currently running in Sql Server and of course if some process is being blocked by someone else process.

So after you run this command in a Sql Server query window:

exec sp_who2

You could see something similar (I left only interesting rows and columns….):

SPID  Status                         Login                                                                              HostName      BlkBy DBName            
----- ------------------------------ ----------------------------------------------------------------------             ------------- ----- ------------------
53    sleeping                       MicrosoftAccount\myaccount                                    			LAPTOPSSD2018   .   master            
54    SUSPENDED                      MicrosoftAccount\myaccount                                    			LAPTOPSSD2018 57    MYDBPROD 		  
57    sleeping                       MicrosoftAccount\myaccount                                    			LAPTOPSSD2018   .   MYDBPROD

The column “BlkBy” indicates the number of the process that is blocking the process identified by the column “SPID”. In the example above the process 54 is blocked by the process 57.

Now you only have to understand what to do with the blocking process…. but this is up to you… maybe a kill?

Bye bye!

 

Sql Server, Sql Server 2016

Post navigation

Previous Post: Google Chrome OnBlur endless loop
Next Post: Configure access permission to a folder for an IIS Application pool

Related Posts

Search a string in stored procedures, functions and views in a SQL Server database Sql Server
Sql Server add constraint WITH NOCHECK Sql Server
Oracle JRE 7 Update 51 (64-Bit) or higher is required for Polybase Sql Server 2016
Multi-tenant database in SQL Server Sql Server 2016
Sql Server error using a database with a CLR assembly CLR
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Excel

Search

Related posts

  • Get SQL Server version in T-SQL
  • Reset Sql Server cache
  • SQL Server and SARGables predicates
  • Getting SQL Server sample databases

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark