How to Defrag Database Files

Not sure if you have tried this or not, but try shutting down the database and see what happens. I have done this with diskeeper and it seemed to help.

Diskeeper works great for me,

http://www.execsoft.com/execsoft.asp

Hope it helps,

Benjamin Keller
Systems Administrator

I use Fix-it Utilities "jetdefrag" by Ontrack and it does a good job. It does it with the database in use, but it is a better idea to not be accessing the database files if possible. This program works on 95,98,NT and 2000... The utilities package has a lot of other nice programs included, one of which is fantastic. A registery fixer!!!

Don't bother running defrag on these Db's

SQL server allocates teh space in LARGE blocks (eg gb each depending the size of the Db, and your growth settings) and takes care of its own utilisation of the space within the allocation. If you try defrag on it you risk corrupting the DB (particualrly if the DB is active during the defrag)

Good DB managemnt says you should allocate complete drives for your DB's and not use them for anything else. that way your DB will be mosre responsive and faster. In fact Databases should really be running on dedicated machines.

Defraggers that use the Microsoft defrag APIs should be able to defragment SQL database devices without shutting down the database. If they can't, then it is a limitation of the defragger itself, not the fact that the file is open.

The MS defragAPIs are tightly integrated with the File, Caching subsystems as well as the Windows Memory Manager. The MS defrag APIs take care of all of the low level I/O synchronization that allows open files to be defragmented. While correct sizing of database devices (anticipate max size of database device and pre-allocate space up front for it) does limit the amount of fragmentation that can occur, you may still run into situations where the database device does grow and not in a contiguous manner. In this case, using a defragger that uses the MS defrag APIs will allow this file to be defragmented safely.

- Greg/Raxco Software

Disclaimer: I work for Raxco Software, the maker of PerfectDisk 2000, as a systems engineer in the support department.

This question was closed by the author