DeFrag causing I/O errors 1

[jeffmoore64]

I was running a database defrag indexes script this weekend and got a several I/O errors on the log file raid set.
Questions:
Has this ever happened to anyone out there?
Does Drefraging the indexes drasticlly increase my log file size? (simple recovery model)
TIA

 

[ptheriault]

Jeff,
I've never run into that error before while running defrag.

Does Drefraging the indexes drasticlly increase my log file size?

Yes it will. I would also suggest running DBCC DBREINDEX instead of defrag.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Paul,
I thing I found the problem with the I/O errors. The Network people where running a backup against the DB I was defragging.

As for the second question... So which is faster to run the reindex or the defrag?

 

[ptheriault]

An index defrag is going to re-organize your data in the current pages of your clustered index. It will also compact your clustered index depending on your fill factor.

Reindex will create a new clustered index and if specified all the other indexes on that table.

Unlike DBCC DBREINDEX, or the index building operation generally, DBCC INDEXDEFRAG is an online operation. It does not hold locks long term. Therefore, DBCC INDEXDEFRAG does not block running queries or updates. Because the time to defragment is related to the level of fragmentation, a relatively unfragmented index can be defragmented faster than a new index can be built. A very fragmented index might take considerably longer to defragment than to rebuild.

The one thing defrag does not do that reindex does do it confirm that your index is contiguous on disk. Which will increase performance. That is why if you have the maint window you would be better off with reindex.

Also, defrag will not be supported in future versions.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

So, how does one go about defragging in "future versions"?
It seems that that is a pretty useful feature.

 

[ptheriault]

use ALTER INDEX in 2005.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

okay so I changed my (your) defrag back to bdreindex. I'm thinging that a fill factor of 90% would be a wise way to help prevent fragmentation during our daily loads and then reindex the dbs on the weekend.
Thoughts?

 

[jeffmoore64]

Also .. when I do the dbcc dbreindex, do the stats get updated or do I need to run UPDATE STATISTICS? Currently I run that after I rund the defrag.

 

[ptheriault]

If your database has a lot of writes you want a fill factor between 80 and 90%. The more reads the higher the fill factor. It's kind of a trial and error to find the perfect fill factor.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

And the second part of the question?

BTW here's a star ... thanks for all the insight!!

 

[ptheriault]

Oh sorry,
No you need to run update stats manually. Infact I run update stats every night.

Thanks for the *.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Thanks!!

 

[jeffmoore64]

Last Q.
I should update my stats AFTER I load my nightly data, right?

 

[jeffmoore64]

But ALTER INDEX is NOT supported in 2000.... So I'll need to add that to my "changes list" when I upgrade to 2005

 

[ptheriault]

>>I should update my stats AFTER I load my nightly data, right?
Correct.


>>But ALTER INDEX is NOT supported in 2000.... So I'll need to add that to my "changes list" when I upgrade to 2005

Correct.




- Paul

- If at first you don't succeed, find out if the loser gets anything.