I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%
The server is running SQL Server 2000 sp4
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%