?
Solved

Running Defrag on a SQL Server

Posted on 2003-11-03
7
Medium Priority
?
12,373 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 375 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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 proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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