What is query causing blocking in SQL 2000

Posted on 2012-08-14
Last Modified: 2012-08-20
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!
Question by:quiTech
    LVL 11

    Accepted Solution

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

    Author Comment

    Thanks!  I will give this a shot when it happens again (presumably tomorrow)  :)
    LVL 11

    Expert Comment

    by:Larissa T
    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 =
      from YourDBName..sysobjects so
     where rsc_dbId = db_id('YourDBName')
       and = rsc_objid


    Author Closing Comment

    Thanks for a great tool!  :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now