Solved

Defrag tool not defragging?

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

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

Suggested Solutions

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now