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!