Solved

Running Defrag on a SQL Server

Posted on 2003-11-03
7
12,341 Views
Last Modified: 2008-02-09
Will running a defragging tool on a SQL Server adversly affect the data on it?/What is the proper way of defragging a sql Server?
0
Comment
Question by:perfittl
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9670762
No it won't. If anything it will speed it up.

You will need to stop the SQL Server service before you run the defrag though.

0
 
LVL 2

Author Comment

by:perfittl
ID: 9670773
Can you provide documentation to back up your answer?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 125 total points
ID: 9670885
Yes, it's in the MCDBA SQL Server book (it's called external fragmentation)

You can look it up on http://www.microsoft.com/technet/ or msdn.microsoft.com. if you need some kind of solid guarantee that it won't affect your data.

If you are really worried, back up your HD first.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Expert Comment

by:arbert
ID: 9672342
You don't have to stop the service, but depending on what is accessing the database, all data files may not be defragged.  Diskkeeper will defrag the database online (just like the built in windows defragger--they both use the Windows Defrag API).

I would always recommend backing up before you defrag....
0
 
LVL 2

Expert Comment

by:thorkyl
ID: 9672374
Just a comment:

I have my daily full backups scheduled at 11pm
I have Defrag scheduled at 2am

yes I defrag daily.

I also have 3 1.5 gig database that are pounded on by 600+ employees
through out the day (but not at night)

Have been under this configuration for two years with no problem.

0
 
LVL 34

Expert Comment

by:arbert
ID: 9672407
On the other hand......If we all estimate our database size and growth and pre-allocate size ahead of time, there should be no need to defragment :) lol
0
 

Expert Comment

by:PolkCo
ID: 30744148
We have a database that started out around 800MB when the application was first installed but has grown to 26GB as of today. Backups and index reorgs on this database were starting to take a very long time which is when our programmer noticed the DB was set to auto-grow in 5MB chunks. You can imagine the amount of file fragmenation that setting led too. For now we've changed it to grow in 10% chunks but performance is still not very good and it's affecting the application as well not just the maintenance tasks.

The poor performance seems to be caused by fragmentation of the actual database files. We've tried running the Windows Defrag utility but it won't touch those large SQL files. Repated attempts to defrag the drive with the DB files results in the message that says it couldn't defrag all files and when we view the report it shows the MDF and LDF files as having been skipped. Shutting the SQL server down is not really an option either as this DB is used for Law Enforcement record keeping which of course is a 24x7 operation.

Does anyone have experience with Diskeeper defragmenting live SQL files?
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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