1

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!

Chris
  • 21
  • 1
  • 4

2 Answers2

3

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.

Peter Schofield
  • 1,639
  • 9
  • 11
  • 1
    Agreed, the restore operation is just killing connections to the database in the same way that restarting the instance would. – Jason Cumberland Jul 09 '10 at 19:37
  • 1
    Or how about just killing the spid of the hung process? – mrdenny Jul 10 '10 at 00: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
0

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

Check this link out.

jesusbolivar
  • 337
  • 1
  • 4
  • 12