Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I don't quite know how to formulate the question because we don't know much yet, but I would like to ask sooner than later because this looks like something that should not be neglected.

This week we started having issues with our database server. It appears to be a data consistency problem and it manifests by timeouts even on very simple queries and small tables. We "fixed" the problem by restarting the server earlier this week and it went away, but now it seems that it is coming back and this time on more crucial tables. For instance, I just did some investigation and I'm looking at a query like this:

SELECT * FROM table WHERE id = 1234

for a particular ID. The table has about 30+ million rows. But it seems that it happens only for a small fraction of records. I bet when I restart the server or backup and restore the database on another server, it will all be fine. But I will try.

At this point, I'm running:


but it seems it's going to run forever. When we hit the issues the first time, I checked the offending table and it was fine. This new table is much bigger.

Some background technical information:

  • SQL Server 2008 R2, Windows Server 2008 R2
  • AWS/EC2, m2.2xlarge, 32 GB RAM, 4 x 1TB RAID 0
  • almost no disk IO, it seems that most of the db is in memory
  • total db size: 100GB

The ELB volumes are "brand" new. We created them this week.

Edit: I just used the following command:

    sys.dm_exec_requests req
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

and found that my query it waiting for a shared lock (LCK_M_S) where the blocking session is waiting for another shared lock blocked by a session that does not exist.

Edit 2: OK, the session exists (I found it using sys.dm_exec_sessions), but it does not appear to do anything now.

Edit 3: I can't find anything interesting about the session. I see what web server it comes from, but not much else.

Edit 4: Edit 4: We found a possible bug in our code: a function that was not making sure a database connection is closed. On the other hand, it looked like the transaction the function was using used to be correctly disposed in which case all locks should have been cleared. It's still not very clear to me, but it seems to be the likely reason. We are going to fix the bug and and keep an eye on it.

share|improve this question

closed as too broad by Paul White Oct 10 at 12:23

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

4 Answers 4

What's the wait type for the query when it is hung? This will tell you exactly what it is waiting for. Go find and install sp_whoisactive on the server and run the stored procedure when you have the problem. This will show you the spid and give you the wait type.

Odds are you are being blocked by someone writing to that row, or another row on that page or you are waiting for the disk to respond.

share|improve this answer

For me this problem was caused by a database connection left open by Visual Studio paused in debug mode. Stopping the debug process allowed the query to complete immediately.

share|improve this answer

Try to run DBCC CHECKDB on the problem DB and wait until it finishes. If there is a physical data inconsistency which produces such a strange behavior, then it is too dangerous to work with this DB as you may lose all your data.

  1. Do the backup as soon as possible.
  2. Check the DB.


If the table has BLOB columns with relatively large amounts of data, it is absolutely normal that checks against this table should take a long amount of time.

share|improve this answer

not really sure what you expect as an answer but if the table has 30+ million rows, its expected that DBCC comand would run for a long time.

When you say:

But it seems that it happens only for a small fraction of records

you mean that you are worried that the whole table are not beeing scanned? That's normal if you have a index on ID, the index would be scanned which would result in fewer reads.

share|improve this answer

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