Defragging A Server with SQL Server 1

[bikerboy718]

Are there any known issues with defragging SQL Server with the database engine or the sql agent running? I am pretty sure that I read that it was recommended to do a defrag on the server with SQL SERVER on it but I do not remember if it mentioned it was best to shut down the services.

Any help would be greatly appreciated.



With Great Power Comes Great Responsibility!!!

Michael

 

[mrdenny]

The defrag will go MUCH faster without SQL Server running.

There shouldn't be any issues when using one of the major defag pieces of software.

Unless your drive is heavly fragmented I wouldn't bother. If it is fragmented I'd recommend fixing the cause of the fragmentation rather than defraging on a regular basis.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

Bikerboy718, which defrag tool are you using?

Be aware that the free$ Diskeepeer that comes with Microsoft operating systems will not defrag SQL Server files (databases). I casll this one Diskeeper 'Lite'.

I use Executive Software's Diskeeper. It does a great job defraging the .mdf files and runs very lean. Fragmentation will occur due to inserts and deletes in you r databases. I schedule a defrag that runs on weekends. That's enough to keep my dbs in good shape. Exec Software can run while db is in use... but should be at a time when very lightly used.

HTH. John

 

[mrdenny]

The SQL Server it self can defrag the data within the database files it self via the DBCC commands.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bikerboy718]

MrDenny,

After your last post I was interested in reading up on the DBCC Defragmenting commands. I have only been able to find the DBCC IndexDefrag command. Is there another one that I am missing?

With Great Power Comes Great Responsibility!!!

Michael

 

[mrdenny]

The indexdefrag command will clean up all the indexes. The table data is kept in 8k pages. SQL will allocate them accross the file as it needs to.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bikerboy718]

Cool, Thanks for the info.



With Great Power Comes Great Responsibility!!!

Michael