Solved

Running Defrag on a SQL Server

Posted on 2003-11-03
7
12,362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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