[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Defragmentation of the disk and files while running a high production SQL 2000 and or SQL 2005 databases.

Posted on 2007-08-09
10
Medium Priority
?
162 Views
Last Modified: 2012-05-05
Defragmentation of the disk and files while running a high production SQL 2000 and or SQL 2005 databases.  Is there any chance that defragmenting will harm my sql databases if ran off hours?  I just want to know if there are any gotchas from your experience.  Since this is a mission critical server I do not  think I should run a defrag during business hours.  Any thought about defragmenting and SQL server?
0
Comment
Question by:phstechsrv
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 14

Accepted Solution

by:
NBSO_ISS earned 800 total points
ID: 19661381
It will not harm your database, but it will most certainly slow affect the performance of the server for the time you are defragmenting.  Here is some information...

http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

We have clustered servers and can take one down for maintenance, so I am unsure exactly what the performance hit is, but if your SQL server gets heavy traffic, I assume it could be quite noticable.
0
 
LVL 16

Assisted Solution

by:gurutc
gurutc earned 400 total points
ID: 19661547
Hi,

I can offer you this important advice:  before you run the defrag, backup your server.  Take the database services off-line and get a good backup.  If you're worried about harming the databases it sounds like it's time for a backup.  It would be even better to image the entire server if possible.  

I have no fingers left to count how many times I should have done this, not only for SQL, but also for any critical servers, because they've all been bitten off!

Also, are you running maintenance within the SQL Server 'meta-OS'?  I call it that because if functions like an add-on OS.  Internal table maintenance, including defragging, will really hep-up performance.   Again, backup first!

Finally, backup!

Trying to prevent pain in the world here.

Good Luck,
 - gurutc
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19661570
I agree with gurutc... ALWAYS backup first.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19661659
if would NEVER defragment a disk while sql or other databases are running on it.
both defrag and sql server are doing low-level operations on the files, so will actually bite each other.
now, if the disks are really fragmented, the database files growth was not set up properly!!!
it would be better then to stop sql server, move the files to another disk, and move them back, and finally to configure data file growth correctly
0
 
LVL 14

Assisted Solution

by:twoboats
twoboats earned 400 total points
ID: 19662430
That said, I use Diskeeper on a 24/7 system with no problems. Defrag is schedule for a "quiet" time in the early hours, and is preceeded by a backup.

Fingers crossed, touching wood whilst feeding four leaf clovers to an infinite number of black cats each with a lucky rabbit's foot on it's collar.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19662661
>> Fingers crossed, touching wood whilst feeding four leaf clovers to an infinite number of black cats each with a lucky rabbit's foot on it's collar.

And if that doesn't work, there is always dynamite--blame it on terrorists!
0
 
LVL 1

Author Comment

by:phstechsrv
ID: 19664287
Has anyone worked with Diskeeper Enterprise Edition and would you recommend it?
0
 
LVL 16

Expert Comment

by:gurutc
ID: 19664568
Hi,

We use DIRMS and have found it to be less invasive than others.  It has never caused us data loss.  Find it here:

http://www.dirms.com/home/homepage.asp

Good Luck,
- gurutc
0
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 400 total points
ID: 19665024
I've used a version of DK since around version 3.0. I currently use Diskeeper Professional Premiere edition. I've got both RAID-1 and RAID-5 configurations. I've done about everything that can be done with it, using Frag-Guard, boot-time defrag for paging file, MFT, extending MFT, etc. I have NEVER had the least bit of trouble with it.

It is not invasive and behaves itself quite well when other tasks need to take precedence. I wouldn't be without it. i do everything from application and database development (with SQL Server and Visual Studio) to major video editing (with Adobe Premiere/After Effects) and digital photography on my system (quite a lot to ask of one computer, but it behaves).

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19668545
"Has anyone worked with Diskeeper Enterprise Edition and would you recommend it?"

Yes - that's what I was recomending above...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

867 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