Occasionally we encounter a query that hangs indefinitely on our SQL Server. This query is being called from a separate ColdFusion web server.
If I run profiler while it is hung I just see locks being allocated and released over and over again. All other queries on this database run just fine while this query hangs.
The weird thing is I can run the query from SSMS and it runs in seconds (even while the applications query is hung).
The only solution I have found so far is to backup the database and then restore the database. This resolves the problem immediately.
Any clues as to what is going on?
Thanks in advance!
Best Answer
Surely stopping and starting the SQL Server service is more convenient than doing a backup and restore operation.
Whilst the query is "hanging", have a look at activity monitor in the GUI, or sp_who2, through Query Analyser. You should be able to identify the process - have a look to see what it is waiting for, or whether it is blocked. Repeat to see that the CPU/IO is moving along.
Also check the SQL and NT logs for any clues as to what is going on.
I'd suggest all this as the first step.