Results 1 to 6 of 6

Thread: Fragmentation

  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: Fragmentation

    Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
    dbcc indexdefrag
    or
    dbcc reindex
    or
    dropping and recreating indexes through SQL?

    Thanks
    Vinnie

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ??? These reduce fragmentation on indexes, not on physical drives. To do this, you need to look at Windows utilities, such as the Windows Resource Kit.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2003
    Location
    Athens Greece
    Posts
    13

    Talking

    On a maintenance level, you stop your SQL server, defrag your drive (use a defragmenter, there are MANY on the market), and start it again. Of course you need to also do defragmentation on DB level, index level, depends on usage of your db, and the design and all. But you have to have a defragmented disk, and while your db is up, there's no way your physical files will be defragmented, you need to stop your SQL server. So you need to do this on a time when you actually can afford a downtime.

    Alternatively, if the fragmentation of your drive that the SQL files is dire, maybe a backup and then a format of the drive is in order, then a restore. Just ponder on that. Depends on which approach is faster.

    Hope that helps.

  4. #4
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    couldn't you also set your database to read-only and then defragment in the background, thus making sure nothing extra gets changed on the disks while defragmentation takes place.

    you could run it at night after first clearing up any temporary files or directories and have a message on your website to say the system is in read-only mode and will switch back to write soon,

    you may also be able to turn on/off read-only mode via a system stored procedure... xp_configure? (which i think is a member of the system setup fixed role?)

    just a thought
    Last edited by sgmuse; 06-02-04 at 08:21.

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    We use a package called Diskeeper from Executive Software. It runs continuously in the background and keeps our drives in great shape.

  6. #6
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71

    Thumbs up

    agree, but you have to use plastic-based authentication to enable that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •