Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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.

share|improve this answer
1  
Agreed, the restore operation is just killing connections to the database in the same way that restarting the instance would. –  Jason Cumberland Jul 9 '10 at 19:37
1  
Or how about just killing the spid of the hung process? –  mrdenny Jul 10 '10 at 0:04
    
Restarting service is not an option as there are 300 other databases currently operational on this instance. Also, killing the spid only kills the process, but does not fix the issue. Restoring the database allows the query to run successfully the next time it's executed via the web app. How would I go about identifying what is locking on this process? That may be the next step I take. I have checked the logs and there is nothing related to this issue. –  Chris Jul 12 '10 at 11:42
    
Did you get any further? sp_who2 in Query Analyser will show you if there are any blocking processes in the Blocked column. If there is no blocking, run it again and again. –  Peter Schofield Jul 21 '10 at 16:35
add comment

You can check the blocked process report to see if your query is being locked by something else.

Check this link out.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.