DBCC SHOWCONTIG IndexID (0,255)

What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
Mike
Hi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Richard's explanation below is the correct one of the two posted.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
>
sql

DBCC SHOWCONTIG IndexID (0,255)

What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
MikeHi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Richard's explanation below is the correct one of the two posted.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> >
> > What do the output from DBCC SHOWCONTIG, IndexID values of
> > 0 and 255 correspond to?
> >
> > Thanks
> >
> > Mike
>

DBCC SHOWCONTIG (SQL 7.0)

Hi,
Can anybody tell me how can i put the output of the=20
DBCC SHOWCONTIG into a table in SQL Server 7.0
I=B4ve done yet the same to SQL Server 2000, however i cant=20
do this to run in my servers whith SQL Server 7.0, can you=20
help me.
Theres no output when i execute this:
..
..
insert into frag_tb
exec ('dbcc showcontig ('+ @.objectid + ')')=20
..
..
Best regards
CC&JM,
Yes, DBCC SHOWCONTIG in SQL7 does not have the WITH TABLERESULTS option.
You may be able to output the result into a perl script, and then parse
the results in a scripting language. It aint going to be pretty.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CC&JM wrote:
> Hi,
> Can anybody tell me how can i put the output of the
> DBCC SHOWCONTIG into a table in SQL Server 7.0
> Ive done yet the same to SQL Server 2000, however i cant
> do this to run in my servers whith SQL Server 7.0, can you
> help me.
> Theres no output when i execute this:
> .
> .
> insert into frag_tb
> exec ('dbcc showcontig ('+ @.objectid + ')')
> .
> .
> Best regards

DBCC SHOWCONTIG (SQL 7.0)

Hi,
Can anybody tell me how can i put the output of the DBCC SHOWCONTIG into a table in SQL Server 7.0
I=B4ve done yet the same to SQL Server 2000, however i cant do this to run in my servers whith SQL Server 7.0, can you help me.
Theres no output when i execute this:
.
.
insert into frag_tb
exec ('dbcc showcontig ('+ @.objectid + ')') .
.
Best regardsCC&JM,
Yes, DBCC SHOWCONTIG in SQL7 does not have the WITH TABLERESULTS option.
You may be able to output the result into a perl script, and then parse
the results in a scripting language. It aint going to be pretty.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CC&JM wrote:
> Hi,
> Can anybody tell me how can i put the output of the
> DBCC SHOWCONTIG into a table in SQL Server 7.0
> I´ve done yet the same to SQL Server 2000, however i cant
> do this to run in my servers whith SQL Server 7.0, can you
> help me.
> Theres no output when i execute this:
> .
> .
> insert into frag_tb
> exec ('dbcc showcontig ('+ @.objectid + ')')
> .
> .
> Best regardssql

DBCC SHOWCONTIG (SQL 7.0)

Hi,
Can anybody tell me how can i put the output of the=20
DBCC SHOWCONTIG into a table in SQL Server 7.0
I=B4ve done yet the same to SQL Server 2000, however i cant=20
do this to run in my servers whith SQL Server 7.0, can you=20
help me.
Theres no output when i execute this:
.
.
insert into frag_tb
exec ('dbcc showcontig ('+ @.objectid + ')')=20
.
.
Best regardsCC&JM,
Yes, DBCC SHOWCONTIG in SQL7 does not have the WITH TABLERESULTS option.
You may be able to output the result into a PERL script, and then parse
the results in a scripting language. It aint going to be pretty.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CC&JM wrote:
> Hi,
> Can anybody tell me how can i put the output of the
> DBCC SHOWCONTIG into a table in SQL Server 7.0
> Ive done yet the same to SQL Server 2000, however i cant
> do this to run in my servers whith SQL Server 7.0, can you
> help me.
> Theres no output when i execute this:
> .
> .
> insert into frag_tb
> exec ('dbcc showcontig ('+ @.objectid + ')')
> .
> .
> Best regards

dbcc show_statistics wrong output

Has anyone ever come across the rows column in dbcc show_statistics not
reporting the right number of rows( I mean way off : Actual count maybe 500
whereas the rows column indicates 50000 ) even after stats are being updated
? Could that be a bug ? Using SQL 2K and SP2 with Slammer HF ofcourseThey may well not be accurate. You need to run DBCC UPDATEUSAGE or UPDATE
STATISTICS WITH FULLSCAN to get them right. See BOL for details
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uaO6TeduDHA.560@.TK2MSFTNGP11.phx.gbl...
Has anyone ever come across the rows column in dbcc show_statistics not
reporting the right number of rows( I mean way off : Actual count maybe 500
whereas the rows column indicates 50000 ) even after stats are being updated
? Could that be a bug ? Using SQL 2K and SP2 with Slammer HF ofcourse

dbcc output to table?

Hi,
is it possible to take dbcc output to a table? , if yes, please let me know how.
Thanks in Advance.
Ram.Youo will have to play with the table column definitions, but you can do this:

insert into table1
exec ('dbcc sqlperf(logspace)')

some DBCC commands even have a table format option (lookup DBCC showcontig) Hope this helps.|||thanks ! worked fine.

dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:

> I'm not sure which command you're trying to capture results for, but pleas
e
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>

dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com
|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:

> I'm not sure which command you're trying to capture results for, but please
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>

dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
>> Hi
>> Is there a way to capture the dbcc output into a t-sql variable
>> or in table.
>>
>> Thanks
>> Mangesh
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:
> I'm not sure which command you're trying to capture results for, but please
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> >> Hi
> >>
> >> Is there a way to capture the dbcc output into a t-sql variable
> >> or in table.
> >>
> >>
> >> Thanks
> >> Mangesh
> >
> > Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> > return the information as a result set. You can insert into a temp table
> > using:
> >
> > create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> >
> > insert into #dbcc_test
> > Exec ('dbcc opentran (''pubs'') with tableresults')
> >
> > Select * from #dbcc_test
> >
> > Drop table #dbcc_test
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
>
>sql

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?
Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>
|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>

dbcc memorystatus update for 2005

Is there any update for the details in the output for DBCC memorystatus for
2005 ?Do you mean like this:
http://support.microsoft.com/kb/907877/en-us
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Is there any update for the details in the output for DBCC memorystatus
> for 2005 ?
>|||Yes.. thanks.. I had reference to this old one :
http://support.microsoft.com/kb/271624
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u$6x9pXLHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Do you mean like this:
> http://support.microsoft.com/kb/907877/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23FPXVvWLHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> Is there any update for the details in the output for DBCC memorystatus
>> for 2005 ?
>

dbcc indexdefrag not doing anything

sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>|||Yes. Thats the index Im trying to defragment.
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> sql2k sp3
>> I keep running dbcc indexdefrag on a small(236,492 rows)
>> table and it doesnt change the output of the Scan
Density
>> (best count:actual count) from dbcc showcontig at all.
>>
>> Any ideas?
>> TIA, Chris
>
>.
>|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> Yes. Thats the index Im trying to defragment.
> >--Original Message--
> >Does your table have a clustered index?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> I keep running dbcc indexdefrag on a small(236,492 rows)
> >> table and it doesnt change the output of the Scan
> Density
> >> (best count:actual count) from dbcc showcontig at all.
> >>
> >>
> >> Any ideas?
> >>
> >> TIA, Chris
> >>
> >
> >
> >.
> >|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
>allocations. Therefore the physical page/extent
fragmentation will remain,
>even though the rows are now in physical order on the
pages. One of the
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
>> Yes. Thats the index Im trying to defragment.
>> >--Original Message--
>> >Does your table have a clustered index?
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >"chris" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> I keep running dbcc indexdefrag on a small(236,492
rows)
>> >> table and it doesnt change the output of the Scan
>> Density
>> >> (best count:actual count) from dbcc showcontig at
all.
>> >>
>> >>
>> >> Any ideas?
>> >>
>> >> TIA, Chris
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
> >--Original Message--
> >Ok, DBCC IndexDefrag does an in-place defrag thus there
> are no new
> >allocations. Therefore the physical page/extent
> fragmentation will remain,
> >even though the rows are now in physical order on the
> pages. One of the
> >hidden limitations of INDEXDEFRAG vs. DBREINDEX.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> >> Yes. Thats the index Im trying to defragment.
> >>
> >> >--Original Message--
> >> >Does your table have a clustered index?
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> >> sql2k sp3
> >> >>
> >> >> I keep running dbcc indexdefrag on a small(236,492
> rows)
> >> >> table and it doesnt change the output of the Scan
> >> Density
> >> >> (best count:actual count) from dbcc showcontig at
> all.
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> TIA, Chris
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
--
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan|||And in addition to that <g>, scan density is useless in the first place if > 1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
> > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> > So if your database has much locking activity during defragmentation,
> > the DBCC command will be less effective.
> >
> > Gert-Jan
>|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > And in addition to that Indexdefrag only works on 1 file at a time.
This
> > means extent fragmentation is useless on multiple files.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:4016C868.AD86E998@.toomuchspamalready.nl...
> > > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked
rows.
> > > So if your database has much locking activity during defragmentation,
> > > the DBCC command will be less effective.
> > >
> > > Gert-Jan
> >
> >
>

dbcc indexdefrag not doing anything

sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...

quote:

---

> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>

---

|||Yes. Thats the index Im trying to defragment.

quote:

---

>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:556301c3e508$94bae040$a501280a@.phx.gbl...
Density[QUOTE]
>
>.
>

---

|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...[QUOTE]
> Yes. Thats the index Im trying to defragment.
>
> message
> Density|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?

quote:

---

>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there

---

are no new

quote:

---

>allocations. Therefore the physical page/extent

---

fragmentation will remain,

quote:

---

>even though the rows are now in physical order on the

---

pages. One of the

quote:

---

>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
rows)[QUOTE]
all.[QUOTE]
>
>.
>

---

|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...[QUOTE]
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
>
> are no new
> fragmentation will remain,
> pages. One of the
> message
> rows)
> all.|||Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 5
NNTP-Posting-Date: 27 Jan 2004 21:22:04 CET
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1075234924 news.xs4all.nl 316 [::ffff:194.109.252.208]:2369
X-Complaints-To: abuse@.xs4all.nl
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!npeer.de.k
pn-eurorings.net!news.cambrium.nl!news.cambrium.nl!news.cambrium.nl!newsgate
.cistron.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:327667
In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...

quote:

---

> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan

---

|||And in addition to that <g>, scan density is useless in the first place if >
1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
>

---

|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...

quote:

---

> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
This[QUOTE]
rows.[QUOTE]
>

---

dbcc checkdb to output file

I've a job set up to run every sunday night, to check the consistency of our
databases.
Unfortunately, when writing a script to check the contents of this file, it
seems that the
file is in unicode.
Does anybody know which unicode format is used for the output file, it looks
like ucs2
but perl seems to recognise it as utf-8.
Any ideas?I don't know, perhaps someone else does, but another option can be to use OSQL through a CmdExec job
instead. I have a feeling that OSQL outputs ANSI...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stressed" <k@.c.co.uk> wrote in message news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> I've a job set up to run every sunday night, to check the consistency of our
> databases.
> Unfortunately, when writing a script to check the contents of this file, it
> seems that the
> file is in unicode.
> Does anybody know which unicode format is used for the output file, it looks
> like ucs2
> but perl seems to recognise it as utf-8.
> Any ideas?
>|||Stressed,
use osql to do this, This is command line utitlity therefore if you want to run it from T-SQL use
xp_cmdshell extended stored procedure.
see following example
--osql
osql /S<server> /Usa /P /dmaster /Q"dbcc checkdb" >> c:\testthis.txt
--using xp_cmdshell
exec master..xp_cmdshell 'osql /S<server> /Usa /P /dmaster /Q"dbcc checkdb" >> c:\testthis1.txt'
--
- Vishal|||or you can use -o parameter of osql as well.
Ex;
osql /S<server> /Usa /P /dmaster /Q" dbcc checkdb" -o c:\testthis2.txt
--
- Vishal|||After looking into it, it appears to be UCS2 little endian.
In case anybody else falls into the same problems.
"Stressed" <k@.c.co.uk> wrote in message
news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> I've a job set up to run every sunday night, to check the consistency of
our
> databases.
> Unfortunately, when writing a script to check the contents of this file,
it
> seems that the
> file is in unicode.
> Does anybody know which unicode format is used for the output file, it
looks
> like ucs2
> but perl seems to recognise it as utf-8.
> Any ideas?
>|||sorry, ucs2 big endian. i lied.
"Stressed" <k@.c.co.uk> wrote in message
news:uTzDi3HmDHA.2528@.TK2MSFTNGP12.phx.gbl...
> After looking into it, it appears to be UCS2 little endian.
> In case anybody else falls into the same problems.
>
> "Stressed" <k@.c.co.uk> wrote in message
> news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> > I've a job set up to run every sunday night, to check the consistency of
> our
> > databases.
> >
> > Unfortunately, when writing a script to check the contents of this file,
> it
> > seems that the
> > file is in unicode.
> >
> > Does anybody know which unicode format is used for the output file, it
> looks
> > like ucs2
> > but perl seems to recognise it as utf-8.
> >
> > Any ideas?
> >
> >
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the paren
t
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the paren
t
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
--
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>>I have a customer who has reported some database corruption. The output of
>> DBCC CHECKDB ('CheckDB') is as follows:
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'CheckDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB ).
>> (1 row(s) affected)
>> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the
>> following
>> results:
>> Server: Msg 1505, Level 16, State 1, Line 1
>> CREATE UNIQUE INDEX terminated because a duplicate key was found for
>> index
>> ID 2. Most significant primary key is '312037'.
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> Could not repair this error.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'LincsDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB repair_rebuild).
>> The statement has been terminated.
>> (1 row(s) affected)
>> I can resolve the issue by dropping and recreating index id: 2 but this
>> involves dropping and re-creating a lot or related constraints (this is a
>> primary key).
>> Can anyone give me any pointers as to why this corruption may have
>> occurred.
>> I have not seen this on any other customer databases so I don't think it
>> is
>> an application error? Also, any suggestions on a better (simpler) way to
>> correct this problem?
>> Unfortunatley, the customers last viable backup is a couple of weeks out
>> of
>> date and they are reluctant to restore and re-enter all the new data
>> again.
>> Any help greatly appreciated,
>> Dave.
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.
I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.
|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>