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!