• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

What is query causing blocking in SQL 2000

We have an application using MS SQL Server 2000 as the database.  The application has been running without DB problems for over 5 years.

A couple of days ago, we started experiencing blocking problems.  Using sp_who and sp_who2, we found that it is generally one process executing a SELECT, that is blocking one other process (doing and INSERT or SELECT), which in turn blocks many others.

I'm hoping to find out what exactly the source process causing all the blocking is doing.  Knowing it's a SELECT doesn't help me find which stored procedure or query it is executing.  Knowing that would allow us to find the source.  Can anyone direct me at finding what that particular process is actually doing?  I tried to use Profiler, but there are so many queries running it's hard to know what's what.

Thanks in advance!
  • 2
  • 2
1 Solution
Lara FEACommented:
run attached script when you see blocking. You will see blocking queries.
Then rewrite your code to use hint  "with (nolock)" in select statements that cause problems.
here us usage
select c1, c2 from myTable with (nolock) ...
quiTechAuthor Commented:
Thanks!  I will give this a shot when it happens again (presumably tomorrow)  :)
Lara FEACommented:
make sure you modify script to use your database names.
there are ##locks table updates one for each Database
replace "YourDBName" with name of your databases
update ##locks
   set name = so.name
  from YourDBName..sysobjects so
 where rsc_dbId = db_id('YourDBName')
   and so.id = rsc_objid

quiTechAuthor Commented:
Thanks for a great tool!  :)

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now