Error in master.dbo.sysindexes

Hi!
This command:
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
yields:
Page (1:396), object ID 2, index ID 0 has been modified but is not marked
modified in the differential backup bitmap.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysindexes' (object ID 2).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (master.dbo.sysindexes ).
I would like to identify the troubled index and perhaps delete it and
recreate it to see if this will go away.
--returns 43 rows
select * from sysindexes where indid = 0
--returns 0 rows
select * from sysindexes where indid = 0 and id=2
--returns 1 row where name='sysindexes'
select * from sysobjects where id=2
What exactly do "object ID 2" and "index ID 0" in the error message refer
to?
Thanks,
JimHave you considered contacting Microsoft for this? As this sounds like a
possible corruption issue, I would contact PSS.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <please.reply@.group> wrote in message
news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
Hi!
This command:
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
yields:
Page (1:396), object ID 2, index ID 0 has been modified but is not marked
modified in the differential backup bitmap.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysindexes' (object ID 2).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (master.dbo.sysindexes ).
I would like to identify the troubled index and perhaps delete it and
recreate it to see if this will go away.
--returns 43 rows
select * from sysindexes where indid = 0
--returns 0 rows
select * from sysindexes where indid = 0 and id=2
--returns 1 row where name='sysindexes'
select * from sysobjects where id=2
What exactly do "object ID 2" and "index ID 0" in the error message refer
to?
Thanks,
Jim|||Hi Jim
Object ID 2 is the sysindexes table, and index ID 0 means the table itself,
not an index on sysindexes.
So there is no 'troubled index' that you can just drop. The problem is in
the sysindexes table itself, which keeps track of all your indexes in a
database, all the statistics, and points to the structures that keep track
of space used for all tables and indexes, in other words, this is a very
critical table and any corruption in it can impact everything else in the
database.
If you have a good recent backup of the database, you can try running DBCC
CHECKTABLE with repair_allow_data_loss, but read all about it before you run
it.
Or you might just decide to pay the fee and call Microsoft support.
--
HTH
Kalen Delaney, SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi!
> This command:
> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
> go
> yields:
> Page (1:396), object ID 2, index ID 0 has been modified but is not marked
> modified in the differential backup bitmap.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysindexes' (object ID 2).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (master.dbo.sysindexes ).
> I would like to identify the troubled index and perhaps delete it and
> recreate it to see if this will go away.
> --returns 43 rows
> select * from sysindexes where indid = 0
> --returns 0 rows
> select * from sysindexes where indid = 0 and id=2
> --returns 1 row where name='sysindexes'
> select * from sysobjects where id=2
> What exactly do "object ID 2" and "index ID 0" in the error message refer
> to?
> Thanks,
> Jim
>|||Vyas,
Thank you for the reply.
Jim
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eUMcopDpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Have you considered contacting Microsoft for this? As this sounds like a
> possible corruption issue, I would contact PSS.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jim" <please.reply@.group> wrote in message
> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi!
> This command:
> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
> go
> yields:
> Page (1:396), object ID 2, index ID 0 has been modified but is not marked
> modified in the differential backup bitmap.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysindexes' (object ID 2).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (master.dbo.sysindexes ).
> I would like to identify the troubled index and perhaps delete it and
> recreate it to see if this will go away.
> --returns 43 rows
> select * from sysindexes where indid = 0
> --returns 0 rows
> select * from sysindexes where indid = 0 and id=2
> --returns 1 row where name='sysindexes'
> select * from sysobjects where id=2
> What exactly do "object ID 2" and "index ID 0" in the error message refer
> to?
> Thanks,
> Jim
>
>|||Kalen,
I have taken your advice:
DBCC CHECKTABLE (master, repair_allow_data_loss)
The table now shows no errors. I have backed up the repaired table and will
monitor it.
Thanks!
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eMedHwDpGHA.4848@.TK2MSFTNGP03.phx.gbl...
> Hi Jim
> Object ID 2 is the sysindexes table, and index ID 0 means the table
> itself, not an index on sysindexes.
> So there is no 'troubled index' that you can just drop. The problem is in
> the sysindexes table itself, which keeps track of all your indexes in a
> database, all the statistics, and points to the structures that keep track
> of space used for all tables and indexes, in other words, this is a very
> critical table and any corruption in it can impact everything else in the
> database.
> If you have a good recent backup of the database, you can try running DBCC
> CHECKTABLE with repair_allow_data_loss, but read all about it before you
> run it.
> Or you might just decide to pay the fee and call Microsoft support.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
>|||There was nothing wrong with the sysindexes table per se - the problem was
with the diff map (the bitmap the shows which extents need to be included in
the next differential backup). A page in sysindexes had been altered since
the last diff backup but the corresponding bit for the extent containing the
page wasn't set in the diff map.
If you lookup the error number (its error 2515) in MSDN
(http://msdn.microsoft.com/library/d...serr_1_3qwp.asp)
you'll see an explanation and what you need to be aware of if you've run
repair (that you need to take a full backup before diff backups are possible
again)
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <please.reply@.group> wrote in message
news:OcDK4wEpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Kalen,
> I have taken your advice:
> DBCC CHECKTABLE (master, repair_allow_data_loss)
> The table now shows no errors. I have backed up the repaired table and
> will monitor it.
> Thanks!
> Jim
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMedHwDpGHA.4848@.TK2MSFTNGP03.phx.gbl...
>|||Paul,
Thank you for the pointer!
Jim
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ugOopBSpGHA.756@.TK2MSFTNGP05.phx.gbl...
> There was nothing wrong with the sysindexes table per se - the problem was
> with the diff map (the bitmap the shows which extents need to be included
> in the next differential backup). A page in sysindexes had been altered
> since the last diff backup but the corresponding bit for the extent
> containing the page wasn't set in the diff map.
> If you lookup the error number (its error 2515) in MSDN
> (http://msdn.microsoft.com/library/d...serr_1_3qwp.asp)
> you'll see an explanation and what you need to be aware of if you've run
> repair (that you need to take a full backup before diff backups are
> possible again)
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Jim" <please.reply@.group> wrote in message
> news:OcDK4wEpGHA.1440@.TK2MSFTNGP03.phx.gbl...
>