Return all rows works partially

#1

I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.

In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.

I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"

Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?

Any idea how to solve this?

Thanks.

Peter CCH

#2

Possibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"Peter CCH" <petercch.wodoy (AT) gmail (DOT) com> wrote

Quote:

I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.

In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.

I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"

Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?

Any idea how to solve this?

Thanks.

Peter CCH

#3

But I'm the only one user who access to that database while I doing
that.

Peter CCH

#4

I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:

73 vs 72 rows lead to different execution plans. Check using estimated execution plan.

Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"Peter CCH" <petercch.wodoy (AT) gmail (DOT) com> wrote

Quote:

But I'm the only one user who access to that database while I doing
that.

Peter CCH

#5

Tibor Karaszi wrote:

Quote:

I'd still check for blocking. It could be an open transaction hanging
around or something, you never know. Other possible reasons:

73 vs 72 rows lead to different execution plans. Check using
estimated execution plan.

Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.

Could as well be a too low timeout value, couldn't it?

robert

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Return all rows works partially

microsoft.public.sqlserver.server microsoft.public.sqlserver.server

Return all rows works partially - 12-02-2005 , 03:44 AM

Re: Return all rows works partially - 12-02-2005 , 04:23 AM

Re: Return all rows works partially - 12-02-2005 , 04:59 AM

Re: Return all rows works partially - 12-02-2005 , 05:45 AM

Re: Return all rows works partially - 12-02-2005 , 08:58 AM