dbcc reindex issue - - I dont understand!

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%

With this scan density, defragmentation may be no be very useful.

> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177

I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***

dbcc opentran with tableresults

sql2k sp3
How do I get the results of "dbcc opentran with
tableresults" into an actual table? Something along the
lines of;
create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2) values
(dbcc opentran with tableresults)
select * from #temp
drop table #temp
TIA, ChrisHi Chris
Once you have the table created, you can use the INSERT/EXEC syntax to
populate it:
insert into #temp
exec ('dbcc opentran(pubs) with tableresults')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09d701c3ce6b$37e9d640$a001280a@.phx.gbl...
> sql2k sp3
> How do I get the results of "dbcc opentran with
> tableresults" into an actual table? Something along the
> lines of;
> create table #temp(c1 varchar(50),c2 varchar(50))
> insert into #temp (c1,c2) values
> (dbcc opentran with tableresults)
> select * from #temp
> drop table #temp
> TIA, Chris|||create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2)
exec ('dbcc opentran with tableresults')
select * from #temp
drop table #temp
>--Original Message--
>sql2k sp3
>How do I get the results of "dbcc opentran with
>tableresults" into an actual table? Something along the
>lines of;
>create table #temp(c1 varchar(50),c2 varchar(50))
>insert into #temp (c1,c2) values
>(dbcc opentran with tableresults)
>select * from #temp
>drop table #temp
>TIA, Chris
>.
>

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 and Transaction Log

Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
BillBill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:
> Bill Wang wrote:
> > Hi,
> > Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> > Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> > primary key. The size of the table is about 120MB, the size of the
> > index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> > SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> > The problem is that the transaction log I backup after running DBCC
> > INDEXDEFRAG on this table is about 600MB. I did backup transaction
> > log before this operation. There is no other activities on this
> > server. And I checked there wasn't any open transaction. I don't
> > know why DBCC INDEXDEFRAG on a small index created so big
> > transaction log.
> >
> > I appreciate any information and help !
> >
> > Thanks!
> >
> >
> > Bill
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

dbcc dbreindex problems

sql2k sp3
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > Server: Msg 1505, Level 16, State 1, Line 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> > ID 1. Most significant primary key is '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > Server: Msg 1505, Level 16, State 1, Line 1
>> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
>> > ID 1. Most significant primary key is '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>

Dbcc Checkdb

Hi,

I don't succeed to do a "dbcc checkdb (BASE)".

I use SQL SERVER 8.00.760 (SP3)

1) Server properties :
NONE default connection option is set

2) Database MASTER and others :
The property "Quoted Identifiers" is NOT set

The result of "dbcc checkdb (BASE)" is always the same :
Error message 1934 "DBCC failed because the following SET options have incorrect settings : 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.

and that, either in Query Analyze or via OSQL command.

Even what is shown in article Q301292 of Microsoft doesn't works correctly ...

Thanks a lot for anyone who could help me to succeed.Can you post your set options - also, does this happen with checkdb ?|||1) OSQL - SET OPTIONS ==> See file "OSQL_DBCC_..."

dbcc checkdb (BASE) is correctly working if I enter previously

set quoted_identifier on
go
set arithabort on
go

2) QUERY ANALYZER - SET OPTIONS :

Set Option Value
----------- --------------
textsize 64512
language Franais
dateformat dmy
datefirst 1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET

(12 ligne(s) affecte(s))

dbcc checkdb (BASE) is correctly working WITHOUT any change

So, which is the best way in order "dbcc checkdb" runs OK in either case ?|||So are you saying that it is functioning properly now ? Did you make any modifications using sp_dboption ?

DBCC CHECKDB

Please Advice.
I run a SQL 2K SP3 on WIN 2K SP4 Advanced in an Active/Passive Cluster confi
guration.
I notice when I ran DBCC CHECKDB on one of my Production databases on the th
e primary node the available RAM Memory decreases command (DBCC CHCKDB) on t
he secondary node (same database) memory decreased slightly.
Please help.DBCC takes memory - so the SQL Server process takes memory from the system
on the primary node. It will not give the memory back until there is memory
pressure. This is perfectly normal. The secondary node has nothing to do
with the SQL Server process running on the primary node - something else
must be running on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timi" <anonymous@.discussions.microsoft.com> wrote in message
news:993FAEF2-A3FF-4D22-938E-055C6FD46372@.microsoft.com...
> Please Advice.
> I run a SQL 2K SP3 on WIN 2K SP4 Advanced in an Active/Passive Cluster
configuration.
> I notice when I ran DBCC CHECKDB on one of my Production databases on the
the primary node the available RAM Memory decreases command (DBCC CHCKDB) on
the secondary node (same database) memory decreased slightly.
> Please help.

DBCC CHECKDB

Please Advice.
I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
Active/Passive Cluster configuration.
I notice when I ran DBCC CheckDB on one of my Production
databases the available RAM Memory decreases and fails to
return on completion of the command.
Please help.Hi
BOL says:
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
requires checking must first be read from disk into memory. In addition,
DBCC CHECKDB uses tempdb to do sorting.
If actively performing transactions while DBCC CHECKDB is running, the
transaction log continues to grow because the DBCC command blocks log
truncation until it has finished reading the log.
It is recommended that DBCC CHECKDB be run during hours when the load is
light on the server. If DBCC CHECKDB is run during heavy peak usage time,
expect a performance hit on the transaction throughput as well as DBCC
CHECKDB completion time.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> Please Advice.
> I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> Active/Passive Cluster configuration.
> I notice when I ran DBCC CheckDB on one of my Production
> databases the available RAM Memory decreases and fails to
> return on completion of the command.
> Please help.
>|||Given that CHECKDB reads every allocated page in the database, I'm guessing
the transaction load was such that the buffer pool grew while CHECKDB was
running (i.e. decreasing available memory) and has not shrunk again since.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> Hi
> BOL says:
> DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
> requires checking must first be read from disk into memory. In addition,
> DBCC CHECKDB uses tempdb to do sorting.
> If actively performing transactions while DBCC CHECKDB is running, the
> transaction log continues to grow because the DBCC command blocks log
> truncation until it has finished reading the log.
> It is recommended that DBCC CHECKDB be run during hours when the load is
> light on the server. If DBCC CHECKDB is run during heavy peak usage time,
> expect a performance hit on the transaction throughput as well as DBCC
> CHECKDB completion time.
>
> "Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> > Please Advice.
> >
> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> > Active/Passive Cluster configuration.
> > I notice when I ran DBCC CheckDB on one of my Production
> > databases the available RAM Memory decreases and fails to
> > return on completion of the command.
> >
> > Please help.
> >
>|||Thanks Paul for your advice.
When running the DBCC CHECKDB statement there were no
open transaction. I ran the same statement on other
production databses one reported a similar behaviour i.e
the amount of memory available decreased but not much and
the others had little or no impact. What could be
responsible for this.
Your kind help appreciated.
>--Original Message--
>Given that CHECKDB reads every allocated page in the
database, I'm guessing
>the transaction load was such that the buffer pool grew
while CHECKDB was
>running (i.e. decreasing available memory) and has not
shrunk again since.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
>> Hi
>> BOL says:
>> DBCC CHECKDB is a CPU- and disk-intensive operation.
Each data page that
>> requires checking must first be read from disk into
memory. In addition,
>> DBCC CHECKDB uses tempdb to do sorting.
>> If actively performing transactions while DBCC CHECKDB
is running, the
>> transaction log continues to grow because the DBCC
command blocks log
>> truncation until it has finished reading the log.
>> It is recommended that DBCC CHECKDB be run during
hours when the load is
>> light on the server. If DBCC CHECKDB is run during
heavy peak usage time,
>> expect a performance hit on the transaction throughput
as well as DBCC
>> CHECKDB completion time.
>>
>> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
>> > Please Advice.
>> >
>> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
>> > Active/Passive Cluster configuration.
>> > I notice when I ran DBCC CheckDB on one of my
Production
>> > databases the available RAM Memory decreases and
fails to
>> > return on completion of the command.
>> >
>> > Please help.
>> >
>>
>
>.
>|||I think its still the buffer pool growing in response to CHECKDB reading all
the pages. The algorithms involved are beyond the scope of this forum to
explain. Try looking at Kalen's Inside SQL Server 2000 for details.
What's the actual difference in mem used by SQL Server before and after
running CHECKDB?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:047901c3d11c$dcdf0a40$a501280a@.phx.gbl...
> Thanks Paul for your advice.
> When running the DBCC CHECKDB statement there were no
> open transaction. I ran the same statement on other
> production databses one reported a similar behaviour i.e
> the amount of memory available decreased but not much and
> the others had little or no impact. What could be
> responsible for this.
> Your kind help appreciated.
> >--Original Message--
> >Given that CHECKDB reads every allocated page in the
> database, I'm guessing
> >the transaction load was such that the buffer pool grew
> while CHECKDB was
> >running (i.e. decreasing available memory) and has not
> shrunk again since.
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> >> Hi
> >> BOL says:
> >> DBCC CHECKDB is a CPU- and disk-intensive operation.
> Each data page that
> >> requires checking must first be read from disk into
> memory. In addition,
> >> DBCC CHECKDB uses tempdb to do sorting.
> >>
> >> If actively performing transactions while DBCC CHECKDB
> is running, the
> >> transaction log continues to grow because the DBCC
> command blocks log
> >> truncation until it has finished reading the log.
> >>
> >> It is recommended that DBCC CHECKDB be run during
> hours when the load is
> >> light on the server. If DBCC CHECKDB is run during
> heavy peak usage time,
> >> expect a performance hit on the transaction throughput
> as well as DBCC
> >> CHECKDB completion time.
> >>
> >>
> >>
> >> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
> in message
> >> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> >> > Please Advice.
> >> >
> >> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> >> > Active/Passive Cluster configuration.
> >> > I notice when I ran DBCC CheckDB on one of my
> Production
> >> > databases the available RAM Memory decreases and
> fails to
> >> > return on completion of the command.
> >> >
> >> > Please help.
> >> >
> >>
> >>
> >
> >
> >.
> >