A table in SQL Server will not return more than a few thousand rows

Question

Using SQL Server 2005. I made a few simple queries in a table containing about 200 thousand records. Today, when I got to work, a simple SELECT * FROM is executed until it extracts about 20 thousand lines. Then it stops. It will not go through 20k lines. If I try to select only one row when using ORDER BY Created DESC, the query runs endlessly. I have never encountered this before. All other tables operate normally. Is it possible that my table has become damaged? It literally happened in one night. The table does the data in real time, but does it (through the form) for several months without problems. Maybe some erroneous entry violates the request? If so, how could I find it ... since I can no longer get the result?

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

Answer 1

Has SET ROWCOUNT been applied to your session?

Answer 2

Does SELECT COUNT (*) return anything? Is it accurate (for example, about 200 thousand)? How long does it take?

You should try to make a full backup of the database and then restore the new database name. After that, check if you have the same problem with the restored database.

Answer 3

DBCC CHECKTABLE .

"SET ROWCOUNT" , , , . / -; SSMS (Tools/Options/Query Execution) (Query/Query Options/Execution).

(, , ) . ( ) SQL Profiler .

Answer 4

, , , SELECT.

To fix this problem, you can execute your request in one SSMS window SELECT ....

Then in the second window, when the first request is still running and blocking, execute the following script .

This should show you offensive SQL along with sufficient troubleshooting details.

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