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!