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

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开发者_JAVA技巧 back anymore?

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

---

Has SET ROWCOUNT been applied to your session?

---

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.

---

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.

---

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