rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on thi
s
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should retu
rn
> row counts in the table. There is no issue with all data manipulation on t
his
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on this
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should return
> row counts in the table. There is no issue with all data manipulation on this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100sql

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
Ranga
Ranga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga
|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero afte
r
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero af
ter
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know wha
t
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For t
he
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
>sql

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:
> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
> > why rowmodctr in sysindexes table shows negative values instead of zero after
> > a index rebuild in sql 2000 ?
> >
> > Thanks,
> > Ranga