Problems after running SHRINKFILE

Hi,

I have major problems after doing a SHRINKFILE on a SQL2K5 database. I
read all the articles that said that you shouldn't do it unless
absolutely necessary so was well aware of the risks, but had no choice
as there was no room on disk for the transaction log to grow, causing
major headaches with long running transactions.

The problem that I have is that all indexes are badly fragmented but a
REBUILD and a REORGANIZE is having no effect on them. The index
fragmentation has gone from 0.x to > 85% in most cases. Batch jobs
taking 20 minutes now take over 10 hours and performance has hit rock-
bottom.

When I came in on Monday morning after the SHRINKFILE (over the
weekend) and checked the index fragmentation it all still looked good
(0.x% etc). Once the overnight job on Monday evening pushed data into
the tables, fragmentation went up, but a REBUILD on the major indexes
seemed to help the job finish in a timely manner. However, Tuesday
night's batch job has pushed the fragmentation back up again and this
time, no amount of rebuilding or reorganizing has made any difference.

I've also tried creating new tables and copying the data from a table
with fragmented indexes into the new tables, then adding indexes
(before and after) to see if that fixes the problem but strangely
enough the fragmentation is exactly the same (> 85%) as on the
original table. Does this make sense?

Has anyone else come across this problem? Or any suggestions on how to
fix it?
Any help gratefully received

Many thanks,
F.

Hi Bob,

I think that the problem may be that the SHRINKFILE was done in 50MB
chunks to allow us to stop it at any point.
The previous time that we tried to run it, we ran it without any size
limit on it. It ran for over 17 hours before we had to kill it and it
didn't seem to have done much.
I suspect that it was doing stuff but because it had over 150GB to
chug through it was always going to take a long time.
By breaking it up into smaller peices we hoped to have more control
over it, by being able to see how long each database shrink took and
by being able to stop it without losing any of the work that had
already been done. We had no idea what a reasonable chunk would be so
settled on 50MB. In hindsight, perhaps it would have been better to
have made the chunks larger. What we suspect has happened is that the
database is now made of small 50MB chunks (some with data, some with
no data) that are not allowing all the index rows in a table to reside
together contiguously. So that no matter how much we rebuild, the
tables are always trying to slot into 50MB 'holes'. We could be wrong,
but it would be good if someone with more knowledge could confirm
whether this would be the case or not?

Now we just need to understand the best way to get back to where we
want to be.
At present we are only (re)building a small proportion of the indexes
that exist in the database, basically the ones that are affecting us
the most. So would it be a good idea to rebuild all indexes regardless
of whether we think we need to or not?
Or is it worth re-SHRINKING the database again but with a larger chunk
size (or even no size)? And then rebuilding indexes after this?

Many thanks,
F.