DBCC ShrinkFile() & Logical Fragmentation

Ahhh, this is frightening shit.

What I'm doing is setting up a database with extra data so that I can simulate dropping a table and subsequently shrinking the database, then comparing the results of DBCC SHOWCONTIG.

Create a empty test database, create a couple of tables to hold a fair amount of test data, 2 tables are enough to demonstrate this.

create database testfrag
go

use testfrag
go

create table mytable1
(
f1 uniqueidentifier not null primary key clustered,
f2 varchar(5000),
f3 varchar(2000)
)

create table mytable2
(
f1 uniqueidentifier not null primary key clustered,
f2 varchar(5000),
f3 varchar(2000)
)

Now populate them with some data, run this statement for both tables

declare @i int
set @i = 0
while @i < 10000
begin
insert mytable1(f1, f2, f3)
values (newid(), replicate('K', 4500), replicate('S', 2000))
set @i=@i+1
end

Ensure that they're perfectly *defragmented*

dbcc indexdefrag(0,mytable2, 1)
dbcc dbreindex(mytable2, '')

Check numbers from DBCC SHOWCONTIG

Output
----------------------------------------------------------------------
TABLE level scan performed.
- Pages Scanned................................: 1000
- Extents Scanned..............................: 125
- Extent Switches..............................: 124
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [125:125]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 1565.0
- Avg. Page Density (full).....................: 80.66%

Drop a table, and shrink the database

drop table mytable1
dbcc shrinkfile(testfrag)

Now check the numbers provided by SHOWCONTIG again

Output
----------------------------------------------------------------------
TABLE level scan performed.
- Pages Scanned................................: 1000
- Extents Scanned..............................: 128
- Extent Switches..............................: 127
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.66% [125:128]
- Logical Scan Fragmentation ..................: 98.80%
- Extent Scan Fragmentation ...................: 0.78%
- Avg. Bytes Free per Page.....................: 1565.0
- Avg. Page Density (full).....................: 80.66%

WTF? Its gone from perfectly defragemented, to perfectly fragemented.
Thats bad, right.