DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
Don
My guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_dbcc_30o9.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>
|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.

Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

DBCC INDEXDEFRAG

On a nightly basis, I run the 'Database Maintince Plan'
which reorganizes the pages, etc. and I believe also
rebuilds the indexes ( I hope? ).
I just read up on the DBCC INDEXDEFRAG command. Is this
useful? Or if the Database Maintinece plan is already
rebuilding the index, is it not needed?
I do have some indexes, that, depending on the query
sometimes takes a very long time. I.E. "WHERE ID = 1" is
fast but "WHERE ID = 88" is slow.
Any thoughts?
Thanks,
Jason RoozeeTake a look at the whitepaper which discusses these very issues:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
There are a bunch of tradeoffs when choosing between rebuilding and
defragging index. I wrote DBCC INDEXDEFRAG primarily to provide an online
reorg alternative to rebuilding an index.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:328901c3e1d2$59a2a940$a401280a@.phx.gbl...
> On a nightly basis, I run the 'Database Maintince Plan'
> which reorganizes the pages, etc. and I believe also
> rebuilds the indexes ( I hope? ).
> I just read up on the DBCC INDEXDEFRAG command. Is this
> useful? Or if the Database Maintinece plan is already
> rebuilding the index, is it not needed?
> I do have some indexes, that, depending on the query
> sometimes takes a very long time. I.E. "WHERE ID = 1" is
> fast but "WHERE ID = 88" is slow.
>
> Any thoughts?
> Thanks,
> Jason Roozee|||Thank you. That helped alot.

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
DonMy guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_dbcc_30o9.asp).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.
Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
DonMy guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/d...n-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/d...n-us/tsqlref/ts
_dbcc_30o9.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx
.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.
Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx
.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

DBCC INDEXDEFRAG

On a nightly basis, I run the 'Database Maintince Plan'
which reorganizes the pages, etc. and I believe also
rebuilds the indexes ( I hope? ).
I just read up on the DBCC INDEXDEFRAG command. Is this
useful? Or if the Database Maintinece plan is already
rebuilding the index, is it not needed?
I do have some indexes, that, depending on the query
sometimes takes a very long time. I.E. "WHERE ID = 1" is
fast but "WHERE ID = 88" is slow.
Any thoughts?
Thanks,
Jason RoozeeTake a look at the whitepaper which discusses these very issues:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
There are a bunch of tradeoffs when choosing between rebuilding and
defragging index. I wrote DBCC INDEXDEFRAG primarily to provide an online
reorg alternative to rebuilding an index.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:328901c3e1d2$59a2a940$a401280a@.phx.gbl...

quote:

---

> On a nightly basis, I run the 'Database Maintince Plan'
> which reorganizes the pages, etc. and I believe also
> rebuilds the indexes ( I hope? ).
> I just read up on the DBCC INDEXDEFRAG command. Is this
> useful? Or if the Database Maintinece plan is already
> rebuilding the index, is it not needed?
> I do have some indexes, that, depending on the query
> sometimes takes a very long time. I.E. "WHERE ID = 1" is
> fast but "WHERE ID = 88" is slow.
>
> Any thoughts?
> Thanks,
> Jason Roozee

---

|||Thank you. That helped alot.

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com. ..
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/ar...A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> > I have to set up a nightly job on my database to drop and reindex indexes
> > on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> > also update the statistics on the rebuilt index or do I have to then issue
> > an update statistics on the table?
> >
> >
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

dbcc dbreindex and update of statistics

I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.co
mcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreinde
x
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/a...realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:

> Dodo Lurker wrote:
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/a.....htm
l
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

DBCC CheckDB error

We run nightly jobs on all of our SQL Servers (over 70) which run
DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
following errors:
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4216) object ID 16 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4218) object ID 16 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7811) object ID 17 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7819) object ID 17 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7821) object ID 17 index ID 3 but was not detected in the scan.
[SQLSTATE 42000]
These errors happen on different servers/databases. When I try to fix these
errors with DBCC CHECKDB and any of the repair options it reports that it is
not running at the correct repair level to fix the errors. Also when you
look in the database there is no object with an id of 16 or 17.
Can anyone help with this?
This is happening on SQL Server 2K with SP3 and SP4.
Thank you.
These two tables are replication tables:
SYSARTICLES = 16, SYSPUBLICATIONS = 17,
The errors say that the metadata entries for the tables and their indexes do
not exist in sysindexes. Have you had an replication problems? Did someone
manually remove the entries from sysindexes?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
> We run nightly jobs on all of our SQL Servers (over 70) which run
> DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
> following errors:
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
> scan.
> [SQLSTATE 42000]
> These errors happen on different servers/databases. When I try to fix
> these
> errors with DBCC CHECKDB and any of the repair options it reports that it
> is
> not running at the correct repair level to fix the errors. Also when you
> look in the database there is no object with an id of 16 or 17.
> Can anyone help with this?
> This is happening on SQL Server 2K with SP3 and SP4.
> Thank you.
>
|||Paul,
Thank you for your response.
We have not had any replication problems and we did not manually remove the
entries from sysindexes. Do you know of a way we could prevent this from
happening in the future?
I did want to correct something i wrote in the original post, DBCC CheckDB
does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
not in the others.
Thanks,
Len Gustafson
"Paul S Randal [MS]" wrote:

> These two tables are replication tables:
> SYSARTICLES = 16, SYSPUBLICATIONS = 17,
> The errors say that the metadata entries for the tables and their indexes do
> not exist in sysindexes. Have you had an replication problems? Did someone
> manually remove the entries from sysindexes?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
>
>
|||I don't know why this happened so can't suggest a way to stop it happening.
You should call Product Support to help you (http://support.microsoft.com).
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...[vbcol=seagreen]
> Paul,
> Thank you for your response.
> We have not had any replication problems and we did not manually remove
> the
> entries from sysindexes. Do you know of a way we could prevent this from
> happening in the future?
> I did want to correct something i wrote in the original post, DBCC CheckDB
> does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
> not in the others.
>
> Thanks,
> Len Gustafson
>
> "Paul S Randal [MS]" wrote:
|||Paul,
Thank you for your help.
Len Gustafson
"Paul S Randal [MS]" wrote:

> I don't know why this happened so can't suggest a way to stop it happening.
> You should call Product Support to help you (http://support.microsoft.com).
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...
>
>

DBCC CheckDB error

We run nightly jobs on all of our SQL Servers (over 70) which run
DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
following errors:
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4216) object ID 16 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4218) object ID 16 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7811) object ID 17 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7819) object ID 17 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7821) object ID 17 index ID 3 but was not detected in the scan.
[SQLSTATE 42000]
These errors happen on different servers/databases. When I try to fix these
errors with DBCC CHECKDB and any of the repair options it reports that it is
not running at the correct repair level to fix the errors. Also when you
look in the database there is no object with an id of 16 or 17.
Can anyone help with this?
This is happening on SQL Server 2K with SP3 and SP4.
Thank you.These two tables are replication tables:
SYSARTICLES = 16, SYSPUBLICATIONS = 17,
The errors say that the metadata entries for the tables and their indexes do
not exist in sysindexes. Have you had an replication problems? Did someone
manually remove the entries from sysindexes?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
> We run nightly jobs on all of our SQL Servers (over 70) which run
> DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
> following errors:
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
> scan.
> [SQLSTATE 42000]
> These errors happen on different servers/databases. When I try to fix
> these
> errors with DBCC CHECKDB and any of the repair options it reports that it
> is
> not running at the correct repair level to fix the errors. Also when you
> look in the database there is no object with an id of 16 or 17.
> Can anyone help with this?
> This is happening on SQL Server 2K with SP3 and SP4.
> Thank you.
>|||Paul,
Thank you for your response.
We have not had any replication problems and we did not manually remove the
entries from sysindexes. Do you know of a way we could prevent this from
happening in the future?
I did want to correct something i wrote in the original post, DBCC CheckDB
does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
not in the others.
Thanks,
Len Gustafson
"Paul S Randal [MS]" wrote:
> These two tables are replication tables:
> SYSARTICLES = 16, SYSPUBLICATIONS = 17,
> The errors say that the metadata entries for the tables and their indexes do
> not exist in sysindexes. Have you had an replication problems? Did someone
> manually remove the entries from sysindexes?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
> > We run nightly jobs on all of our SQL Servers (over 70) which run
> > DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
> > following errors:
> >
> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> > IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
> > scan.
> > [SQLSTATE 42000]
> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> > IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
> > scan.
> > [SQLSTATE 42000]
> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> > IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
> > scan.
> > [SQLSTATE 42000]
> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> > IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
> > scan.
> > [SQLSTATE 42000]
> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> > IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
> > scan.
> > [SQLSTATE 42000]
> >
> > These errors happen on different servers/databases. When I try to fix
> > these
> > errors with DBCC CHECKDB and any of the repair options it reports that it
> > is
> > not running at the correct repair level to fix the errors. Also when you
> > look in the database there is no object with an id of 16 or 17.
> >
> > Can anyone help with this?
> >
> > This is happening on SQL Server 2K with SP3 and SP4.
> >
> > Thank you.
> >
>
>|||I don't know why this happened so can't suggest a way to stop it happening.
You should call Product Support to help you (http://support.microsoft.com).
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...
> Paul,
> Thank you for your response.
> We have not had any replication problems and we did not manually remove
> the
> entries from sysindexes. Do you know of a way we could prevent this from
> happening in the future?
> I did want to correct something i wrote in the original post, DBCC CheckDB
> does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
> not in the others.
>
> Thanks,
> Len Gustafson
>
> "Paul S Randal [MS]" wrote:
>> These two tables are replication tables:
>> SYSARTICLES = 16, SYSPUBLICATIONS = 17,
>> The errors say that the metadata entries for the tables and their indexes
>> do
>> not exist in sysindexes. Have you had an replication problems? Did
>> someone
>> manually remove the entries from sysindexes?
>> Thanks
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
>> news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
>> > We run nightly jobs on all of our SQL Servers (over 70) which run
>> > DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
>> > following errors:
>> >
>> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
>> > of
>> > IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
>> > scan.
>> > [SQLSTATE 42000]
>> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
>> > of
>> > IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
>> > scan.
>> > [SQLSTATE 42000]
>> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
>> > of
>> > IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
>> > scan.
>> > [SQLSTATE 42000]
>> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
>> > of
>> > IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
>> > scan.
>> > [SQLSTATE 42000]
>> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
>> > of
>> > IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
>> > scan.
>> > [SQLSTATE 42000]
>> >
>> > These errors happen on different servers/databases. When I try to fix
>> > these
>> > errors with DBCC CHECKDB and any of the repair options it reports that
>> > it
>> > is
>> > not running at the correct repair level to fix the errors. Also when
>> > you
>> > look in the database there is no object with an id of 16 or 17.
>> >
>> > Can anyone help with this?
>> >
>> > This is happening on SQL Server 2K with SP3 and SP4.
>> >
>> > Thank you.
>> >
>>|||Paul,
Thank you for your help.
Len Gustafson
"Paul S Randal [MS]" wrote:
> I don't know why this happened so can't suggest a way to stop it happening.
> You should call Product Support to help you (http://support.microsoft.com).
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...
> > Paul,
> >
> > Thank you for your response.
> >
> > We have not had any replication problems and we did not manually remove
> > the
> > entries from sysindexes. Do you know of a way we could prevent this from
> > happening in the future?
> >
> > I did want to correct something i wrote in the original post, DBCC CheckDB
> > does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
> > not in the others.
> >
> >
> > Thanks,
> >
> > Len Gustafson
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> >> These two tables are replication tables:
> >> SYSARTICLES = 16, SYSPUBLICATIONS = 17,
> >>
> >> The errors say that the metadata entries for the tables and their indexes
> >> do
> >> not exist in sysindexes. Have you had an replication problems? Did
> >> someone
> >> manually remove the entries from sysindexes?
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Dev Lead, Microsoft SQL Server Storage Engine
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> >> news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
> >> > We run nightly jobs on all of our SQL Servers (over 70) which run
> >> > DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
> >> > following errors:
> >> >
> >> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
> >> > of
> >> > IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
> >> > scan.
> >> > [SQLSTATE 42000]
> >> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
> >> > of
> >> > IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
> >> > scan.
> >> > [SQLSTATE 42000]
> >> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
> >> > of
> >> > IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
> >> > scan.
> >> > [SQLSTATE 42000]
> >> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
> >> > of
> >> > IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
> >> > scan.
> >> > [SQLSTATE 42000]
> >> > Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer
> >> > of
> >> > IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
> >> > scan.
> >> > [SQLSTATE 42000]
> >> >
> >> > These errors happen on different servers/databases. When I try to fix
> >> > these
> >> > errors with DBCC CHECKDB and any of the repair options it reports that
> >> > it
> >> > is
> >> > not running at the correct repair level to fix the errors. Also when
> >> > you
> >> > look in the database there is no object with an id of 16 or 17.
> >> >
> >> > Can anyone help with this?
> >> >
> >> > This is happening on SQL Server 2K with SP3 and SP4.
> >> >
> >> > Thank you.
> >> >
> >>
> >>
> >>
>
>

DBCC CheckDB error

We run nightly jobs on all of our SQL Servers (over 70) which run
DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
following errors:
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4216) object ID 16 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:4218) object ID 16 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7811) object ID 17 index ID 0 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7819) object ID 17 index ID 2 but was not detected in the scan.
[SQLSTATE 42000]
Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
IAM page (1:7821) object ID 17 index ID 3 but was not detected in the scan.
[SQLSTATE 42000]
These errors happen on different servers/databases. When I try to fix these
errors with DBCC CHECKDB and any of the repair options it reports that it is
not running at the correct repair level to fix the errors. Also when you
look in the database there is no object with an id of 16 or 17.
Can anyone help with this?
This is happening on SQL Server 2K with SP3 and SP4.
Thank you.These two tables are replication tables:
SYSARTICLES = 16, SYSPUBLICATIONS = 17,
The errors say that the metadata entries for the tables and their indexes do
not exist in sysindexes. Have you had an replication problems? Did someone
manually remove the entries from sysindexes?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
> We run nightly jobs on all of our SQL Servers (over 70) which run
> DBCC CHECKDB ('<db_name>') with ALL_ERRORMSGS. Frequently we get the
> following errors:
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4216) object ID 16 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:4218) object ID 16 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7811) object ID 17 index ID 0 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7819) object ID 17 index ID 2 but was not detected in the
> scan.
> [SQLSTATE 42000]
> Msg 2576, Sev 16: IAM page (0:0) is pointed to by the previous pointer of
> IAM page (1:7821) object ID 17 index ID 3 but was not detected in the
> scan.
> [SQLSTATE 42000]
> These errors happen on different servers/databases. When I try to fix
> these
> errors with DBCC CHECKDB and any of the repair options it reports that it
> is
> not running at the correct repair level to fix the errors. Also when you
> look in the database there is no object with an id of 16 or 17.
> Can anyone help with this?
> This is happening on SQL Server 2K with SP3 and SP4.
> Thank you.
>|||Paul,
Thank you for your response.
We have not had any replication problems and we did not manually remove the
entries from sysindexes. Do you know of a way we could prevent this from
happening in the future?
I did want to correct something i wrote in the original post, DBCC CheckDB
does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
not in the others.
Thanks,
Len Gustafson
"Paul S Randal [MS]" wrote:

> These two tables are replication tables:
> SYSARTICLES = 16, SYSPUBLICATIONS = 17,
> The errors say that the metadata entries for the tables and their indexes
do
> not exist in sysindexes. Have you had an replication problems? Did someone
> manually remove the entries from sysindexes?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:BAABD7AB-294C-4D01-85CA-3CC3312434A5@.microsoft.com...
>
>|||I don't know why this happened so can't suggest a way to stop it happening.
You should call Product Support to help you (http://support.microsoft.com).
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...[vbcol=seagreen]
> Paul,
> Thank you for your response.
> We have not had any replication problems and we did not manually remove
> the
> entries from sysindexes. Do you know of a way we could prevent this from
> happening in the future?
> I did want to correct something i wrote in the original post, DBCC CheckDB
> does fix these errors if you run it in the REPAIR_ALLOW_DATA_LOSS mode but
> not in the others.
>
> Thanks,
> Len Gustafson
>
> "Paul S Randal [MS]" wrote:
>|||Paul,
Thank you for your help.
Len Gustafson
"Paul S Randal [MS]" wrote:

> I don't know why this happened so can't suggest a way to stop it happening
.
> You should call Product Support to help you (http://support.microsoft.com)
.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
> news:631CD829-06C3-4A1C-ADF9-60799AE08077@.microsoft.com...
>
>