Fragmented Files - too fragmented!

[Sean]

Hi

We've got a Server running SQL Server 2000. The disk is 120GB, and one
of the databases is 50GB. For the last several months, the disk has
been running at 90-98% full, and the database has been growing daily,
thus fragmenting the drive quite considerably. (Raid 5)

We tried to defragment the drive but the defrag program would end
"successfully" after about 2 mins, obviously having done nothing (the
volume was shown as almost completely red). It would seem that we dont
have enough free, contiguous space on the drive for the defrag program
to successfully defrag this drive, even though we have now cleared
about 55GB of free space (free, but not contiguous, thus the file
could not be defragged into free space without it being immediately
fragmented again!).

We're struggling to solve this, but we have had a few ideas. This is
one theory:

If we back up the database to another server, then delete the database
and run a full defrag, the drive should be in a nice, neat condition.
If we then restore the database back onto the server, would the file
system re-create the database in a nice, orderly fashion (i.e.
defragged), or would it be put back onto the server in the original
condition?

My theory is that if you copy a file off a fragmented server, clear
the entire volume, and then copy the file back onto the server, then
Win2K would re-write the file on the server in a defragged state. Or
am I missing something??

Thanks for any help/suggestions.
Sean

 

[Leythos]

We tried to defragment the drive but the defrag program would end
"successfully" after about 2 mins, obviously having done nothing (the
volume was shown as almost completely red). It would seem that we dont
have enough free, contiguous space on the drive for the defrag program
to successfully defrag this drive, even though we have now cleared
about 55GB of free space (free, but not contiguous, thus the file
could not be defragged into free space without it being immediately
fragmented again!).

We're struggling to solve this, but we have had a few ideas. This is
one theory:

You need to do two things:

1) You can not defrag a database file while it's online. You need to
stop the SQL Service to defrag the space. This does not have anything to
do with clustered indexes or the data itself, only the database file.

2) Get Executive Software's Diskeeper product for servers.

You can also do like I do - On servers where I can take the database
down for maintenance, I schedule a job to stop the SQL service, defrag
the drive for 2 hours (as many times as it can run in that time), and
then restart the SQL service.