Our application has started having problems with SQL Server 2005 queries getting blocked. Is is possible to tell what query the blocking process is running? If it is possible how is it done?
+4
A:
If you are running an SQL Server version bigger than Express, the Activity Monitor of SQL Server Management Studio allows you to make a snapshot of the current activity on the system. There, you can see which connection is blocking which other connection. You can also see which tables (or other objects) are locked by these connections, giving you a hint on what causes the blocking.
Heinzi
2009-11-05 20:48:55
Excellent answer. Thanks!
Jim Blizard
2009-11-05 20:57:59
A:
I know that it is possible, however, not being a sql guru I can't give you a query to run that will tell you what query is blocking. I will plug an application that I am evaluating which is doing very well for us so far.
Sql Sentry has been helping my small team identify problems areas. This is noteworthy in that we only have one person who has any proficiency with sql server.
I have no affiliation with the company nor am I being compensated for the plug in any way.
Jeremy Roberts
2009-11-05 20:54:28
+2
A:
If you know the SPID of the blocking process, you can run DBCC INPUTBUFFER(xxx), where xxx is the SPID. If you're using Activity Monitor, you can achieve the same thing by double-clicking the row containing that process.
Tadmas
2009-11-05 20:54:51
Probably want to run a sp_who2 before doing this to find your blocked SPID value/s.
StarShip3000
2009-11-05 20:58:23