Table in SQL Server won't return more than a few thousand rows

Question

Using SQL Server 2005. I was doing some simple queries on a table that has about 200k records. As of today when I got to work, a simple SELECT * FROM executes till it retrieves about 20k rows...then stops. It won't go past 20k rows. If I try to select just ONE row while using ORDER BY Created DESC, the query runs indefinitely. I've never encountered this before. All other tables are acting normally. Is it possible for my table to have become corrupted? This literally happened overnight. The table does take live data, but has been doing so (via a form) for several months without issue. Is it possibly some errant record that's breaking the query? If so, how could I even find it...since I can't get a result set back anymore?

I apologize if this is vague, but I'm not sure how else to word it.

Answer 1

Has SET ROWCOUNT been applied to your session?

Answer 2

Does a SELECT COUNT(*) return anything? Is it accurate (e.g. roughly 200k)? How long does it take?

You should try doing a full backup of the database, then restore to a new database name. After that, see if you are having the same issue with the restored database.

Answer 3

DBCC CHECKTABLE can be used to check the integrity of a single table and its indexes.

'SET ROWCOUNT' seems the most likely culprit, but presumably you are not deliberately setting it. This value/setting might be being set in the background for you somehow; it can be set within SSMS to apply to all windows (Tools / Options / Query Execution), or even to just the current Window (Query / Query Options / Execution).

Other (and, frankly, ridiculous) possibilities exist. Tracking your session (from Login to submitted query) via SQL Profiler may reveal more and subtler information.

Answer 4

I would assume that an uncommitted transaction is holding a lock on at least one of the rows or pages that is incompatible with the shared locks required by your SELECT query.

To troubleshoot this you can in one SSMS window do your SELECT ... query that fails.

Then in a second window while the first query is still executing and blocked run the following script.

This should show you the offending SQL along with sufficient details to troubleshoot it.

SELECT  
 Blocking.session_id as BlockingSessionId 
, Sess.login_name AS BlockingUser  
, BlockingSQL.text AS BlockingSQL 
, Waits.wait_type WhyBlocked 
, Blocked.session_id AS BlockedSessionId 
, USER_NAME(Blocked.user_id) AS BlockedUser 
, BlockedSQL.text AS BlockedSQL 
, DB_NAME(Blocked.database_id) AS DatabaseName 
FROM sys.dm_exec_connections AS Blocking 
INNER JOIN sys.dm_exec_requests AS Blocked  
ON Blocking.session_id = Blocked.blocking_session_id 
INNER JOIN sys.dm_os_waiting_tasks AS Waits  
ON Blocked.session_id = Waits.session_id 
RIGHT OUTER JOIN sys.dm_exec_sessions Sess  
ON Blocking.session_id = Sess.session_id   
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL 
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL 
ORDER BY BlockingSessionId, BlockedSessionId