How do I defrag a drive with a sql db?

Posted on 2009-04-16
Medium Priority
Last Modified: 2012-05-06
I've very seldomly used SQL but have noticed that the drive that our big SQL database is on is really fragmented and maybe the database itself.

If I run a regular disk defrag on the hard drive with either windows or diskeeper, will I do any damage to the database?

Also, how do I go about defragging the database itself, or is this even possible?

Question by:wilson0728
  • 2
LVL 12

Accepted Solution

udaya kumar laligondla earned 672 total points
ID: 24159757
you will have to stop the server and then do the disk defrag. I doubt if you get any greate performace boost.
try to tune the database
LVL 25

Assisted Solution

reb73 earned 664 total points
ID: 24159765
"If I run a regular disk defrag on the hard drive with either windows or diskeeper, will I do any damage to the database?"

You will have to have the databases offline or stop the SQL Server Service to enable your database files to be defragmented at an OS level.. The databases won't be defragmented if the SQL Server service is running.

"Also, how do I go about defragging the database itself, or is this even possible?"

You could use the DBCC INDEXDEFRAG to defragment the clustered index of every table in the database..
LVL 39

Assisted Solution

BrandonGalderisi earned 664 total points
ID: 24160080
I recommend using CONTIG from sysinternals (now owned by M$) to defragment the individual files for the databases.

Contig is a single file defragmenter.

LVL 39

Expert Comment

ID: 24160103
Also worth mentioning, that the physical database files can only become fragmented on disk when the file is initially created or it is expanded.  This is a good reason in itself to not have auto grow enabled.  It allows you to plan ahead for manual expansion and defragmentation of the database.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

864 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