I'm at my wit's end with this issue - usually I'm pretty good with SQL2k servers but this is really stumping me.
I have a SQL2000 database on the same machine with my webserver. I am running DotNetNuke (4.9.4 - recent multi-step upgrade from 3.1.1). I noticed that the removal of a module definition started timing out (we had other timeout issues when removing child portals, etc prior to the upgrade as well).
Eventually I tracked down the sproc that caused the timeout and determined that the point at which the sproc hangs is at a simple delete statement (DELETE FROM dbo.DesktopModules
WHERE DesktopModuleId = @DesktopModuleId). I grabbed the DesktopModuleId of the module I was trying to delete and passed it to the above query in query analyzer. I let it run overnight and it still didn't complete! Here are the steps I took to diagnose the issue so far:
1) Checked for blocking queries via sp_who2 while running query. None found. Doesn't seem to be a deadlock scenario. Made sure there weren't a zillion connections being established as well - only 2 on that database.
2) Tried checking execution plan and estimated execution plan in query analyzer. Both run indefinitely and don't return anything when cancelled.
3) Ran every table in the database through DBCC DBREINDEX thinking maybe the issue was corrupted or terribly fragmented indexes. No help.
4) Truncated, backed up transaction log. Eventually switched to Simple Recovery Model just to be absolutely sure the transaction log wasn't the cause. Still no help. Plenty of diskspace on the drive.
5) Took a look at foreign key relationships on the DesktopModules table. There is only 1 referencing a table with around 60 records. The foreign key column has an index on it (these are core tables so I'm reasonably certain they were designed properly anyways).
Note that when running sp_who2 the only anomaly I saw was that while the CPUTime increases the DiskIO remains at 0. Not sure what that could mean.
Can anyone suggest any other possible solutions/diagnostic courses?