A specific record in a table causes table to hang

Asked May 25 '16 at 03:13

Active May 25 '16 at 03:13

Viewed 47 times

I've just run into a table that I can't delete the contents of suddenly. SELECT and DELETE operations on this table hang, but if I abort the SELECT I get some of the rows returned (29, to be exact).

I tested using SELECT TOP N and found a point after which the query hangs.

select  top 37 [Id] from [foo]
go

select  top 38 [Id] from [foo]
go

results in this: (Timeout of 5 minutes)

The execution plans:

The Index is on the Id column which is the primary key.

Is there any way I can delete the contents of the table? I haven't tried using TRUNCATE TABLE yet.

asked May 25 '16 at 03:13

TJennings

You mean top 37 work, but top 38 doesnt? – Juan Carlos Oropeza May 25 '16 at 03:14
@JuanCarlosOropeza Yep, exactly that. Top 37 returns 37 rows. Top 38 hangs. – TJennings May 25 '16 at 03:15
how many rows are in your table? We need much more information so we can try reproduce the problem, otherwise will be a guessing game. – Juan Carlos Oropeza May 25 '16 at 03:15
can you post execution plans xml in a link – TheGameiswar May 25 '16 at 03:16
@TheGameiswar Usually is better if you include a link showing how to do it. – Juan Carlos Oropeza May 25 '16 at 03:17
@JuanCarlosOropeza - According to sys.dm_db_partition_stats, 38 – TJennings May 25 '16 at 03:17
http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – TheGameiswar May 25 '16 at 03:18
I dont have idea what can be. I suggest you try SELECT without top. Trying to create a new table with `CREATE TABLE AS SELECT`... – Juan Carlos Oropeza May 25 '16 at 03:19
@TheGameiswar http://pastebin.com/7UnqRdAb - Top 37 http://pastebin.com/7PVJ5J19 - Top 38 – TJennings May 25 '16 at 03:24
@JuanCarlosOropeza - Select without top also hangs – TJennings May 25 '16 at 03:25
Something's probably got a row-lock. Look at the activity monitor while your query is running, it should show blocking/blocked queries. As a quick hack, you could restart SQL Server. – Blorgbeard May 25 '16 at 03:27
@Blorgbeard - Not sure what was locking it, but killing the head process cleared it up. I think it might've been an incomplete insert, since now it works, but there's only 37 rows in the table. – TJennings May 25 '16 at 03:35
Run the select and run below query as well in parallel and update the question:select txt.text,ec.blocking_session_id,ec.last_wait_type,ec.wait_type from sys.dm_exec_requests ec cross apply sys.dm_exec_sql_text(ec.sql_handle) txt where session_id=<> – TheGameiswar May 25 '16 at 03:36
can you try using nolock with select query?. There could be many reason for this error. – Vivek Khandelwal May 25 '16 at 06:07

A specific record in a table causes table to hang

0 Answers0