querying nvarchar fields in a table pauses part way through

Question

I have a problem with one of the tables in my database. If I do a select * from table, the query will grab the first hundred or so records in under a second, pause for a few seconds, grab the next 600 or so and then not go any further (the query keeps processing but no more records are fetched. It consistently stops after record 718 - even if I let it run for more than 10 minutes.

Notes:

• I've been using this table for quite some time in this database and copies of it in other databases without any problems.

• I can do a select * on any other table.

• I can do a count(*) on this table.

• I can do a select field from this table for any non nvarchar field.

I'm assuming that there is some kind of corruption going on but DBCC CHECKDB(MyDB) WITH NO_INFOMSGS isn't reporting anything.

Answer 1

The problem was caused by a record lock on the table - in another SSMS window I had another uncommitted transaction that was locking the record. Once that was committed the queries worked successfully.

Credit to Brad D for setting me on the right path.

FYI: exec sp_who; exec sp_who2 or exec sp_lock would have identified the wayward process.