Return all rows works partially

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 CCHPossibly someone has a lock on the row which will be read as row number 72 b
y 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@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>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
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||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 execu
tion 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@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> 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