I'm having an ongoing issue, which I've had as long as I can remember, where our SQL Server locks up and causes almost all processes to hang and return an ODBC timeout to the end user.
I try to track down what is causing the lock, using a Microsoft article that helps identify what is causing the lock but when I go to the SQL Activity monitor to match the SPID that is locked up also. So I have no way of getting that SPID information to see what's going on.
If I restart the SQL service, everything is back to normal. We are running SQL 2000 SP3 (business system requirement)
Is there a tool out there that will identify what is causing this? Or is there a better way to run down what is causing the problem? What I'm currently doing is identifying the thread, then thread ID, then running a SQL query to match the KPID to the SPID, then at that point I look up the SPID and see what it's doing.