Know of Good Deftrag Software that will....

[sshafe]

I am checking to see if anyone knows of a good defrag software to run on SQL Server that is able to: (in order of priority)
1. Determine if a defrag is needed.
2. Has the ability to be scheduled.


All input is appreciated

 

[sshafe]

Ok, I have additional requirements. I need some software to do the following:

Determine if a defrag is needed.
If yes,
be able to take SQL Server down,
run the defrag
bring SQL Server back up
If no,
keep trucking along.


Is there any product out there that can do all of this?

 

[MaffewW]

you should set up a maintenance plan to reindex and shrink your database to fix internal defragmenation. It may be that is all that is needed, eg if your database is installed on a new drive with lots of space and it never grows then the files shouldnt become fragmented, though if this isnt the case check DIskKeeper

http://www.sql-server-performance.com/sql_fragmentation.asp

 

[sshafe]

From my research with DiskKeeper Lite, you cannot do the analyze and then say yes or no, do the defrag. And then, the option to say yes, do the defrag and kill processes (SQL Server)and when it is finished, bring the processes back up or no, a defrag is not needed. I would like all this to be done behind the scenes. Did I just confuse you on that?

 

[strella]

Old time hack....

If you asynchronously spawn diskeeper with params and it is still running in 't' seconds, say 30 seconds, then it is doing a defrag.

A few quick tests should tell you how long DK takes to find out if it needs to run. Double it.

The rest of the process is trivial.

--------
sleep 30
listtasks| keeplineswith diskkeeper| count
if not errorlevel 1 goto end

down_sql

:waitloop
sleep 30
listtasks| keeplineswith diskkeeper| count
if errorlevel 1 goto waitloop

up_sql
:end
--------

where up_sql and down_sql start and stop the server.

 

[SemperFiDownUnda]

I'm weary of Diskeeper statements. They lead you to believe you can't use normal processes to clean up your database

Take this statement
"SQL Server databases are made up of large database and log files that are preallocated in size at the point of their creation. "

A good DBA knows the size of his/her database including growth factor, log file expected size etc. Thus when you initially create the database on a defragmented RAID array you WILL get contiguous space.

most of there statements are misleading. If you properly manage your SQL server or are willing to devote just a little effort you can fix the fragmentation with the tools provided by Microsoft.

If this fragmentation is hurting your performance to an unacceptable extent then you should really investigate getting a good DBA in for a bit to design a maintenance plan for your database and possibly restructuring your filegroups to perform better.

But if you want to spend money for tools you already have more power to you.

 

[tlbroadbent]

We've been running SQL Server for nine years and have never used disk defragmentation software. We use SQL tools to reduce internal database fragmentation, as suggested by SemperFiDownUnda. Our SQL Servers provide excellent performance.
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sshafe]

Let me clear this up a little. I am not trying to defrag the database. I want to defrag the datafiles that are stored to disk. But you cannot defrag the datafiles while the database is up. (the .mdf files) Any solutions for this?

 

[tlbroadbent]

I fully understand what you want to do. Have you seen the following article on SQL-Server-Performance.com? It discusses the use of DiskKeeper.

http://www.sql-server-performance.com/sql_fragmentation.asp

A quick web search also turned up the following.

Raxco PerfectDisk claims to defrag SQL Server databases even while SQL Server is running.

http://www.raxco.com/support/windows/pd40/perfectdisk40_faqs.cfm#4

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm