Solved

Running Defrag on a SQL Server

Posted on 2003-11-03
7
12,346 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

920 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

12 Experts available now in Live!

Get 1:1 Help Now