Solved

Defrag tool not defragging?

Posted on 2008-09-30
5
550 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 250 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 250 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

27 Experts available now in Live!

Get 1:1 Help Now