Solved

Running Defrag on a SQL Server

Posted on 2003-11-03
7
12,355 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 shrink a transaction log file down to a reasonable size.

792 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