DBCC SHOWCONTIG - what's relevant?

It's my understanding that with DBCC SHOWCONTIG WITH FAST,
TABLERESULTS I'll
get back:
ObjectName
ObjectId
IndexName
IndexId
Pages
ExtentSwitches
ScanDensity
BestCount
ActualCount
LogicalFragmentation
My question is what's relevant for a heap? I've noticed that I'll get
back values for all the columns (the ones mentioned above plus Rows,
MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
ExtentFragmentation) when it's a heap. Are any of these values "real"
for the heaps?
Also, I know that Logical Fragmentation isn't relevant for heaps- are
there any other metrics that aren't relevant? Are there any metrics
that aren't relevant for clustered tables? How about non-clustered
indexes on heap tables vs. ones on clustered tables?I think all the fields still have meaning, it is just there isn't much you
can do about bad numbers, short of creating/dropping a clustered index.
Books on line reports some of these fields as not relevant, but it is more
accurate to say that the value will always be the "best" possible value due
to the way heaps are scanned... Take logical fragmentation, it represents
the number of out of order pages.. When reading through the linked list of a
clustered index, an out of order page is where the logical order of pages in
the linked list does not matcht the physical order of the pages on disk...
When SQL reads a heap, it reads using the physical order on disk, so there
is no logical fragmentation. Scan density will be a good number for the same
reason. I would guess the best information would be relative to how full the
pages are, and how many forwarding pointers there are...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411291644.47cef884@.posting.google.com...
> It's my understanding that with DBCC SHOWCONTIG WITH FAST,
> TABLERESULTS I'll
> get back:
> ObjectName
> ObjectId
> IndexName
> IndexId
> Pages
> ExtentSwitches
> ScanDensity
> BestCount
> ActualCount
> LogicalFragmentation
> My question is what's relevant for a heap? I've noticed that I'll get
> back values for all the columns (the ones mentioned above plus Rows,
> MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
> ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
> ExtentFragmentation) when it's a heap. Are any of these values "real"
> for the heaps?
> Also, I know that Logical Fragmentation isn't relevant for heaps- are
> there any other metrics that aren't relevant? Are there any metrics
> that aren't relevant for clustered tables? How about non-clustered
> indexes on heap tables vs. ones on clustered tables?|||I see what you're saying, but there's some wierdness here. With DBCC
SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
mentioned before for clustered tables, but for heap tables I'll get
back numbers for the other columns as well- the ones that aren't
supposed to be returned. Here's an example from Northwind- I just
included results from two tables but it's the same query. (I've
retyped so it's easier to read):
ObjectName: OrderDetails
ObjectId: 325576198
IndexName: PK_Order_Details
IndexId: 1 <- A clustered table
Level: 0
Pages: 9
Rows: NULL <- not provided with FAST
MinimumRecordSize: NULL <- not provided with FAST
MaximumRecordSize: NULL <- not provided with FAST
AverageRecordSize: NULL <- not provided with FAST
ForwardedRecords: NULL <- not provided with FAST
Extents: 0 <- not provided with FAST
ExtentSwitches: 5
AverageFreeBytes: NULL <- not provided with FAST
AveragePageDensity: NULL <- not provided with FAST
ScanDensity: 33.33333333
BestCount: 2
ActualCount: 6
LogicalFragmentation: 11.11111069
ExtentFragmentation: NULL <- not provided with FAST
ObjectName: Region
ObjectId: 885578193
IndexName:
IndexId: 0 <- A heap table
Level: 0
Pages: 1
Rows: 4 <- not provided with FAST
MinimumRecordSize: 111 <- not provided with FAST
MaximumRecordSize: 111 <- not provided with FAST
AverageRecordSize: 111 <- not provided with FAST
ForwardedRecords: 0 <- not provided with FAST
Extents: 1 <- not provided with FAST
ExtentSwitches: 0
AverageFreeBytes: 7644 <- not provided with FAST
AveragePageDensity: 5.55967378616333 <- not provided with FAST
ScanDensity: 100
BestCount: 1
ActualCount: 1
LogicalFragmentation: 0
ExtentFragmentation: 0 <- not provided with FAST
Northwind isn't the best example, as on other tables I've gotten an
ExtentFragmentation value above 0 on heaps, but let's ignore that for
now. My real point is that on heap tables, it's returning values that
it shouldn't, by definition of WITH FAST. So... are these "real"
values for the heaps? Is this a SQL bug?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#QMjD4t1EHA.824@.TK2MSFTNGP11.phx.gbl>...
> I think all the fields still have meaning, it is just there isn't much you
> can do about bad numbers, short of creating/dropping a clustered index.
> Books on line reports some of these fields as not relevant, but it is more
> accurate to say that the value will always be the "best" possible value due
> to the way heaps are scanned... Take logical fragmentation, it represents
> the number of out of order pages.. When reading through the linked list of a
> clustered index, an out of order page is where the logical order of pages in
> the linked list does not matcht the physical order of the pages on disk...
> When SQL reads a heap, it reads using the physical order on disk, so there
> is no logical fragmentation. Scan density will be a good number for the same
> reason. I would guess the best information would be relative to how full the
> pages are, and how many forwarding pointers there are...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>|||As Wayne points out some things like the average Page density and Free Bytes
may be accurate. But the bottom line is that with a heap the rest is
meaningless or even useless for the most part. Without a clustered index
there is nothing you can do about fragmentation unless you BCP out all the
data (in some order), truncate the table and BCP it back in. And even then
you have absolutely no control over how it gets loaded back into the
database file(s). With very few exceptions, every table should have a
clustered index if for no other reason so you can control fragmentation. Of
coarse there are lots of others as well.
--
Andrew J. Kelly SQL MVP
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411301340.20190b5f@.posting.google.com...
>I see what you're saying, but there's some wierdness here. With DBCC
> SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
> mentioned before for clustered tables, but for heap tables I'll get
> back numbers for the other columns as well- the ones that aren't
> supposed to be returned. Here's an example from Northwind- I just
> included results from two tables but it's the same query. (I've
> retyped so it's easier to read):
> ObjectName: OrderDetails
> ObjectId: 325576198
> IndexName: PK_Order_Details
> IndexId: 1 <- A clustered table
> Level: 0
> Pages: 9
> Rows: NULL <- not provided with FAST
> MinimumRecordSize: NULL <- not provided with FAST
> MaximumRecordSize: NULL <- not provided with FAST
> AverageRecordSize: NULL <- not provided with FAST
> ForwardedRecords: NULL <- not provided with FAST
> Extents: 0 <- not provided with FAST
> ExtentSwitches: 5
> AverageFreeBytes: NULL <- not provided with FAST
> AveragePageDensity: NULL <- not provided with FAST
> ScanDensity: 33.33333333
> BestCount: 2
> ActualCount: 6
> LogicalFragmentation: 11.11111069
> ExtentFragmentation: NULL <- not provided with FAST
> ObjectName: Region
> ObjectId: 885578193
> IndexName:
> IndexId: 0 <- A heap table
> Level: 0
> Pages: 1
> Rows: 4 <- not provided with FAST
> MinimumRecordSize: 111 <- not provided with FAST
> MaximumRecordSize: 111 <- not provided with FAST
> AverageRecordSize: 111 <- not provided with FAST
> ForwardedRecords: 0 <- not provided with FAST
> Extents: 1 <- not provided with FAST
> ExtentSwitches: 0
> AverageFreeBytes: 7644 <- not provided with FAST
> AveragePageDensity: 5.55967378616333 <- not provided with FAST
> ScanDensity: 100
> BestCount: 1
> ActualCount: 1
> LogicalFragmentation: 0
> ExtentFragmentation: 0 <- not provided with FAST
> Northwind isn't the best example, as on other tables I've gotten an
> ExtentFragmentation value above 0 on heaps, but let's ignore that for
> now. My real point is that on heap tables, it's returning values that
> it shouldn't, by definition of WITH FAST. So... are these "real"
> values for the heaps? Is this a SQL bug?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:<#QMjD4t1EHA.824@.TK2MSFTNGP11.phx.gbl>...
>> I think all the fields still have meaning, it is just there isn't much
>> you
>> can do about bad numbers, short of creating/dropping a clustered index.
>> Books on line reports some of these fields as not relevant, but it is
>> more
>> accurate to say that the value will always be the "best" possible value
>> due
>> to the way heaps are scanned... Take logical fragmentation, it represents
>> the number of out of order pages.. When reading through the linked list
>> of a
>> clustered index, an out of order page is where the logical order of pages
>> in
>> the linked list does not matcht the physical order of the pages on
>> disk...
>> When SQL reads a heap, it reads using the physical order on disk, so
>> there
>> is no logical fragmentation. Scan density will be a good number for the
>> same
>> reason. I would guess the best information would be relative to how full
>> the
>> pages are, and how many forwarding pointers there are...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org

Dbcc Showcontig

DBCC SHOWCONTIG scanning 'MYTAB' table...
Table: 'MYTAB'(50099219); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 150
- Extents Scanned.......................: 25
- Extent Switches.......................: 25
- Avg. Pages per Extent..................: 6.0
- Scan Density [Best Count:Actual Count]......: 73.08% [19:26]
- Logical Scan Fragmentation ..............: 1.33%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 878.5
- Avg. Page Density (full)................: 89.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since Extents Scanned and Extent Switches r equal, why the density is slow, what about pages per Extent, can it be manaully increased?

HowdyI think the reason lies in the fact that you have 2 pages per extent occupied by different objects. If you had 8 pages per extent, the table would take up approx. 19 extents (19*8-Best count). Look up DBCC DBREINDEX and DBCC INDEXDEFRAG in BOL. mojza

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> > Hi all
> >
> > I include the 'Reorganize pages with the original amount of free space'
> > option on my database maintenance plan. On some of my databases this makes
> > the transaction log balloon. What's the mechanics of this option?
> >
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
>sql

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command wil
l first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only
reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pr...r />
idbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX
or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this make
s
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command w
ill first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will onl
y reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pr.../>
2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREIND
EX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>
sql

DBCC INDEXDEFRAG

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

DBCC INDEXDEFRAG

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

quote:

---

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

---

|||Thank you. That helped alot.

dbcc error 2511

We had a dbcc error 2511 on a user table - non-clustered
index.
>>There are 0 rows in 1 pages for
object 'sched_order_item'.
Msg 2511, Level 16, State 1, Server DCECANP1, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Table
Corrupt: Object
ID 1421716613, Index ID 5. Keys out of order on page
(1:758631), slots
120 and 121.<<
dbcc checktable(table_name,repair_rebuild) in single user
mode was run, but the error is back the night after. By
the way, table doesn't have any data in it. if anybody
have any previous experiance with it and could share it
with me, I would appreciate it. Thanks very muchBOL shows it to be an index out of order problem. Strange
as you have no rows. I think I have come across this where
table changes are made and not reflected in the index
because there is no data.
I would try scripting out the table, drop it and then
recreate it.
Regards
John

DBCC DBREINDEX

DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).

Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.

DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
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 'type 24, len 16'.
The statement has been terminated.

PLEASE HELP!;

GO

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

|||

Gary Wells wrote:

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

Hi Gary,

Yes, of course.

Thanks & Regards,

Melih

|||

I ran the query below to find the duplicate values and I got the error message.

select Msg_ID, count(Msg_ID)
from AgentsStateChanges
group by Msg_ID
having count(Msg_ID)>1

Server: Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

|||
Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.
|||

Sankar Reddy wrote:

Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.

Yes, Sankar Reddy

We are using Sql2k. So, how can I solve this issue?

Thanks&Regards,

Melih

|||

OK. No dublicate values:
select Msg_ID, count(*)
from AgentsStateChanges
group by Msg_ID
having count(*)>1
result:
(0 row(s) affected)

But, I'm trying this and I'm finding 79 pieces dublicate values

select cast(Msg_ID as varchar(36)), count(*)
from AgentsStateChanges
group by cast(Msg_ID as varchar(36))
having count(*)>1

result:

(79 row(s) affected)

I'm trying some values from duplicate values and I get 0 row message interestingly

select * FROM AgentsStateChanges where Msg_ID=

'100CEB2E-6B69-0058-0001-000000000001'

result:
(0 row(s) affected)

I dont understood anything.

Melih

DBCC DBREINDEX

DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).

Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.

DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
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 'type 24, len 16'.
The statement has been terminated.

PLEASE HELP!;

GO

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

|||

Gary Wells wrote:

Hi,

Have you used DBCC CHECKDB to try repairing the consistency errors being using DBCC DBREINDEX?

Regards,

Gary.

Hi Gary,

Yes, of course.

Thanks & Regards,

Melih

|||

I ran the query below to find the duplicate values and I got the error message.

select Msg_ID, count(Msg_ID)
from AgentsStateChanges
group by Msg_ID
having count(Msg_ID)>1

Server: Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

|||
Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.
|||

Sankar Reddy wrote:

Looks like you are using Sql Server 2000. You can't do count or group by on uniqueidentifier columns in version 2000. This can be done in 2005 though.

Yes, Sankar Reddy

We are using Sql2k. So, how can I solve this issue?

Thanks&Regards,

Melih

|||

OK. No dublicate values:
select Msg_ID, count(*)
from AgentsStateChanges
group by Msg_ID
having count(*)>1
result:
(0 row(s) affected)

But, I'm trying this and I'm finding 79 pieces dublicate values

select cast(Msg_ID as varchar(36)), count(*)
from AgentsStateChanges
group by cast(Msg_ID as varchar(36))
having count(*)>1

result:

(79 row(s) affected)

I'm trying some values from duplicate values and I get 0 row message interestingly

select * FROM AgentsStateChanges where Msg_ID=

'100CEB2E-6B69-0058-0001-000000000001'

result:
(0 row(s) affected)

I dont understood anything.

Melih

DBCC DBREINDEX

There is a parameter in DBCC DBREINDEX that allows you to specify the
freespace on leaf level pages (fillfactor). This is only use when the index
is created and when the DBREINDEX command is run.
In SQL2005, there is a new parameter in the create index which is
'PAD_INDEX' this determines the amount of free space on the intermediate
pages of the index. When you run DBREINDEX the fill factor applies to the
leaf levels but does it also apply to the intermediate pages, or is there
another parameter I may be missing here?
Thanks in Advance
Dave
Dave
You are probably talking about ALTER INDEX operation
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by FILLFACTOR is applied to
the intermediate-level pages of the index. If FILLFACTOR is not specified at
the same time PAD_INDEX is set to ON, the fill factor value stored in
sys.indexes is used.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity. This leaves
sufficient space for at least one row of the maximum size that the index can
have, based on the set of keys on the intermediate pages.
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Hi Uri,
I understand that but my question is does this 'PAD_INDEX' facility also
work with DBREINDEX?
Dave
"Uri Dimant" wrote:

> Dave
> You are probably talking about ALTER INDEX operation
> PAD_INDEX = { ON | OFF }
> Specifies index padding. The default is OFF.
> ON
> The percentage of free space that is specified by FILLFACTOR is applied to
> the intermediate-level pages of the index. If FILLFACTOR is not specified at
> the same time PAD_INDEX is set to ON, the fill factor value stored in
> sys.indexes is used.
> OFF or fillfactor is not specified
> The intermediate-level pages are filled to near capacity. This leaves
> sufficient space for at least one row of the maximum size that the index can
> have, based on the set of keys on the intermediate pages.
>
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||Dave
I have no idea, but what I do know that DBCC DBREINDEX is deprecated
and will be removed in the future
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:5E727125-DDB0-44CD-8287-886D9D1436E2@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
>
> "Uri Dimant" wrote:
|||PAD_INDEX has always been there it is not a 2005 feature. But it does not
take a value for the space. Instead it is either on or off. If it is on it
will inherit the fill factor setting and use it for the intermediate levels
of the index just like the fill factor is for the leaf level. It is not
maintained except for rebuilds and creation.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
change this if required?
Thanks in Advance
Dave
Dave
"Andrew J. Kelly" wrote:

> PAD_INDEX has always been there it is not a 2005 feature. But it does not
> take a value for the space. Instead it is either on or off. If it is on it
> will inherit the fill factor setting and use it for the intermediate levels
> of the index just like the fill factor is for the leaf level. It is not
> maintained except for rebuilds and creation.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||It is OFF by default and if you want to change it you need to specify it in
the create or rebuild index commands.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...[vbcol=seagreen]
> Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
> What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
> change this if required?
> Thanks in Advance
> Dave
>
> --
> Dave
>
> "Andrew J. Kelly" wrote:
|||Thanks Andrew
Dave
"Andrew J. Kelly" wrote:

> It is OFF by default and if you want to change it you need to specify it in
> the create or rebuild index commands.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...
>
>

DBCC DBREINDEX

There is a parameter in DBCC DBREINDEX that allows you to specify the
freespace on leaf level pages (fillfactor). This is only use when the index
is created and when the DBREINDEX command is run.
In SQL2005, there is a new parameter in the create index which is
'PAD_INDEX' this determines the amount of free space on the intermediate
pages of the index. When you run DBREINDEX the fill factor applies to the
leaf levels but does it also apply to the intermediate pages, or is there
another parameter I may be missing here?
Thanks in Advance
--
DaveDave
You are probably talking about ALTER INDEX operation
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by FILLFACTOR is applied to
the intermediate-level pages of the index. If FILLFACTOR is not specified at
the same time PAD_INDEX is set to ON, the fill factor value stored in
sys.indexes is used.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity. This leaves
sufficient space for at least one row of the maximum size that the index can
have, based on the set of keys on the intermediate pages.
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave|||Hi Uri,
I understand that but my question is does this 'PAD_INDEX' facility also
work with DBREINDEX?
--
Dave
"Uri Dimant" wrote:

> Dave
> You are probably talking about ALTER INDEX operation
> PAD_INDEX = { ON | OFF }
> Specifies index padding. The default is OFF.
> ON
> The percentage of free space that is specified by FILLFACTOR is applied
to
> the intermediate-level pages of the index. If FILLFACTOR is not specified
at
> the same time PAD_INDEX is set to ON, the fill factor value stored in
> sys.indexes is used.
> OFF or fillfactor is not specified
> The intermediate-level pages are filled to near capacity. This leaves
> sufficient space for at least one row of the maximum size that the index c
an
> have, based on the set of keys on the intermediate pages.
>
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>|||Dave
I have no idea, but what I do know that DBCC DBREINDEX is deprecated
and will be removed in the future
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:5E727125-DDB0-44CD-8287-886D9D1436E2@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
>
> "Uri Dimant" wrote:
>|||When the documentation does not speak, it is time to experiment...
Based on the repro script (see below) I conclude that (only) if
PAD_INDEX was used upon index creation, then the index intermediate
levels are padded when reindexing.
HTH,
Gert-Jan
create table Test (id char(900) not null)
go
insert into Test values (1)
insert into Test values (2)
insert into Test values (3)
insert into Test select cast(id as int)+3 from Test
insert into Test select cast(id as int)+6 from Test
insert into Test select cast(id as int)+12 from Test
insert into Test select cast(id as int)+24 from Test
insert into Test select cast(id as int)+48 from Test
insert into Test select cast(id as int)+96 from Test
insert into Test select cast(id as int)+192 from Test
insert into Test select cast(id as int)+384 from Test
insert into Test select cast(id as int)+768 from Test
insert into Test select cast(id as int)+1536 from Test
insert into Test select cast(id as int)+3072 from Test
insert into Test select top 3856 cast(id as int)+6144 from Test
go
create unique clustered index IX_Test on Test(id)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 18312 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 91464 KB
go
drop index Test.IX_Test
go
create unique clustered index IX_Test on Test(id)
with (PAD_INDEX=ON, FILLFACTOR=50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',100)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 160072 KB
go
drop table Test
SkyDave wrote:[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
> "Uri Dimant" wrote:
>|||PAD_INDEX has always been there it is not a 2005 feature. But it does not
take a value for the space. Instead it is either on or off. If it is on it
will inherit the fill factor setting and use it for the intermediate levels
of the index just like the fill factor is for the leaf level. It is not
maintained except for rebuilds and creation.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave|||Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
change this if required?
Thanks in Advance
Dave
Dave
"Andrew J. Kelly" wrote:

> PAD_INDEX has always been there it is not a 2005 feature. But it does not
> take a value for the space. Instead it is either on or off. If it is on it
> will inherit the fill factor setting and use it for the intermediate level
s
> of the index just like the fill factor is for the leaf level. It is not
> maintained except for rebuilds and creation.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>|||It is OFF by default and if you want to change it you need to specify it in
the create or rebuild index commands.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...[vbcol=seagreen]
> Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
> What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
> change this if required?
> Thanks in Advance
> Dave
>
> --
> Dave
>
> "Andrew J. Kelly" wrote:
>|||Thanks Andrew
Dave
"Andrew J. Kelly" wrote:

> It is OFF by default and if you want to change it you need to specify it i
n
> the create or rebuild index commands.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...
>
>

DBCC CHECKDB

It looks like I have to setup maintenance for my 24x7 SQL Server 2000
manually. Most of the relevant pages in BOL are clear enough, but DBCC
CHECKDB isn't so clear.
I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
haven't seen anything that would alter DDL at night, which is the only
restriction I see. I also have no intent of actually doing a repair
initially.
Is this the right routine? Is this the right usage?
Thanks,
JayHi Jay
"Jay" wrote:
> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> CHECKDB isn't so clear.
> I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
> haven't seen anything that would alter DDL at night, which is the only
> restriction I see. I also have no intent of actually doing a repair
> initially.
> Is this the right routine? Is this the right usage?
> Thanks,
> Jay
>
You may want to check out Paul Randals blog posts on this at
https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
How often you run it will depend on many factors, but I don't know of anyone
that does it nightly! If you have scheduled other DBCC commands you may be
running some checks more than once, but you may want to run the these
commands rather than DBCC CHECKDB to reduce the time/workload on your servers.
John|||After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
(anything else?).
Since the runtime for CHECKDB is under 3 minutes for the primary database on
the dev system, even when expanded out I don't think it's that big a deal to
just use it.
Still, if I can do everything CHECKDB does (maybe even a little more) in an
application that spreads the load out, I should at least consider it.
So, what else is DBCHECK doing?
What else SHOULD it be doing?
Thanks,
Jay
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> Hi Jay
> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
> You may want to check out Paul Randals blog posts on this at
> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> How often you run it will depend on many factors, but I don't know of
> anyone
> that does it nightly! If you have scheduled other DBCC commands you may be
> running some checks more than once, but you may want to run the these
> commands rather than DBCC CHECKDB to reduce the time/workload on your
> servers.
> John|||Include DBCC CHECKCATALOG as well. SQL2005 includes this in CheckDB but 2000
omitted it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:uowYuIc5HHA.2380@.TK2MSFTNGP02.phx.gbl...
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and
> CHECKTABLE (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database
> on the dev system, even when expanded out I don't think it's that big a
> deal to just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in
> an application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
>> Hi Jay
>> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
>> You may want to check out Paul Randals blog posts on this at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
>> How often you run it will depend on many factors, but I don't know of
>> anyone
>> that does it nightly! If you have scheduled other DBCC commands you may
>> be
>> running some checks more than once, but you may want to run the these
>> commands rather than DBCC CHECKDB to reduce the time/workload on your
>> servers.
>> John
>|||Hi Jay
DBCC CHECKDB can take a long time, so as you move forward you would have to
keep an eye on the duration and workload it places on the server.
John
"Jay" wrote:
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
> (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database on
> the dev system, even when expanded out I don't think it's that big a deal to
> just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in an
> application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> > Hi Jay
> >
> > "Jay" wrote:
> >
> >> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> >> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> >> CHECKDB isn't so clear.
> >>
> >> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
> >> I
> >> haven't seen anything that would alter DDL at night, which is the only
> >> restriction I see. I also have no intent of actually doing a repair
> >> initially.
> >>
> >> Is this the right routine? Is this the right usage?
> >>
> >> Thanks,
> >> Jay
> >>
> > You may want to check out Paul Randals blog posts on this at
> > https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> >
> > How often you run it will depend on many factors, but I don't know of
> > anyone
> > that does it nightly! If you have scheduled other DBCC commands you may be
> > running some checks more than once, but you may want to run the these
> > commands rather than DBCC CHECKDB to reduce the time/workload on your
> > servers.
> >
> > John
>
>