Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Defrag tool not defragging?

Posted on 2008-09-30
5
Medium Priority
?
566 Views
Last Modified: 2009-01-10
I have my SQL server with a 300GB hard drive. Currently there is 50GB free. I have ran defrag about 6 times, but the file fragmentation level has stayed the same at 61%. I have stopped the SQL service (this is the onlything running on this box) and it doesn't seem to make a difference. Defrag runs (for several hours) and I can see file progress in the status bar, but in the end when its done and gives me my "report" it still says 61%. Any ideas? The graph is solid red almost.... with very little changing after a defrag.
0
Comment
Question by:tbirkhimer
5 Comments
 
LVL 7

Expert Comment

by:swaller
ID: 22605480
You may have an unmovable block. Have you run a checkdisk?
0
 
LVL 97

Expert Comment

by:war1
ID: 22605739
0
 
LVL 3

Accepted Solution

by:
tempter earned 1000 total points
ID: 22611808
Hey mate

since you are running SQL in your server, SQL files are treated different than normal files and normal defragmentation will NOT work. Defrag software utilities will normally make an attempt to defrag but will skip them thus giving you the 61% defrag level you see.

SQL will take a slice of disk and allocate it for its database. If you try to defrag you could actually break the database file as its a custom archive that only SQL can read n retrieve. It could mess up your tables and index and you may need to dump it out, and do a SQL recovery with merge (reload of database from dumps)

Your SQL database administrators should know this and keep it healthy by running SQL maintenance tasks such as re-indexing, clearing up space and dbcc's.

if your databse is running slow or people telling you the system is slow, some SQL maintenance tasks may be needed. Re-indexing is almost mandatory every so often if the DB is used with zero downtime.

Cheer & good luck
Morci
0
 

Assisted Solution

by:zoobuhs
zoobuhs earned 1000 total points
ID: 22637533
Hi tbirkhimer-

Nothing against 3rd party tools, but i depend on MS native tools when maintaining MS products, like SQL.

Ola Hallengren developed a script that not only does defrag (http://blog.ola.hallengren.com/_attachments/3440068/IndexOptimize.sql), but it also includes the routines to initiate/schedule an entire maintenance job (http://blog.ola.hallengren.com/_attachments/3440068/MaintenanceSolution.sql). The full script "Hallengren-SQL-Maintenance.sql" is attached. Simply remove the ".txt" after downloading.

Here are the details if you're interested:
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

...and don't forget to thank Ola if you find this useful; ola -at- hallengren *dot* com

\doru

------------------------------
Admin Edit:
The posted script was removed at Ola Hallengren's request.  Please use the following permanent links to access the script:
Documentation http://ola.hallengren.com/Documentation.html 
Version history http://ola.hallengren.com/Versions.html 
Script http://ola.hallengren.com/scripts/MaintenanceSolution.sql
WhackAMod
Experts Exchange Administrator
26/11/2009
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

916 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