Windows defrag on SQL Server 2005 machine?

Posted on 2009-02-15
Last Modified: 2012-05-06

Is it necessary / recommended to run Windows defrag on servers primarily used for SQL Server 2005?

I have a server (Windows 2003 x64) with quite a lot of data on it split over several volumes - which have never been defragmented. Most of the data is SQL Server 2005 databases - but some is files/documents.

Should I run a windows defrag on the volumes? If so is it necessary to do this with the databases offline?

thanks - reddal
Question by:reddal
    LVL 18

    Expert Comment

    Databases do become not contiguous over time, and for that reason I run contig.exe which does a single file defrag and will run online but seems to slow things up a bit so it runs during off peak hours weekly via .bat files and Task Scheduler.  

    Contig.exe can be downloaded from Microsoft for free at sysinternals:


    LVL 17

    Expert Comment

    Instead of contig i would just defrag the whole drive but I wouldnt do it frequently unless the database frequently grows and shrinks creating a lot of fragmentation. I would also recommend Running a defrag in SQL Server because daabases have their own kind of defragmentation.
    LVL 18

    Expert Comment

    Actually there are two kinds of fragmentation in SQL server files. And Defrag won't necessarily make a file contiguous.
    LVL 25

    Accepted Solution

    An initial single file defrag using contig.exe, followed by a rebuild of all clustered indexes should get you database in a fairly optimum condition storage wise, but this wouldn't be a complete defrag of the data within the database file though.. Having the database offline would be advisable..

    You can get a list of all clustered indexes with the following command -

    SELECT Name FROM sysindexes where indid = 1

    Use this list in a cursor and use the DBCC DBREINDEX command to rebuild the clustered indexes.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now