SQL Server 2014 reindex maintenance plans

i have a maintenance plan setup to run a weekly index.  Within the maintenance plan i have a rebuild index task and a reorganize index task.  I just wanted to ask the experts if it is recommended to do both or if i could just use the rebuild task and not worry about reorganizing index task.  

I want to avoid the huge log file that is created when running the reorganizing index task but i don't want to eliminate the reorganizing index task if it is something that will be important.

Also, i should mention that the job that includes reorg and rebuild is failing due to the log file filling up.  Maybe i can change settings for the log file to allow a bigger one and then run the job nightly instead of weekly...

Any thoughts?
LVL 3
IKtechAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
1. No matter what anyone will say in my opinion (and not only me) REBUILD does way more than REORG and I would always use REBUILD if I have a server running enterprise edition like it looks you have.
2. If you run REBUILD then REORG is a waste assuming all indexes are rebuilt. Speaking of that...there are various extremely well written and working scripts available to only REBUILD indexes where fragmentation is greater than xx% so your T-log would not grow as if you rebuild ALL. For instance this one: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html 

Also, some indexes will show fragmentation right after rebuild/reorg because of their composition and fillfactor so...I would not just rebuild ALL by default.
3. If you can change the Recovery option to SIMPLE indeed the T-log growth will be smaller but..you will lose the fast recovery option as T-Log backups cannot be taken. I would try first as mentioned a filtered REBUILD and maybe do it once as you said manual and monitor the T-log growth and when is getting close to get full just take a T-log backup to reclaim the space. After that a daily REBUILD filtered should not fill the T-Log because only fragmented indexes will be rebuilt.
0
 
IKtechAuthor Commented:
Thanks Icohan!!  i'll give it a shot...
0
 
lcohanDatabase AnalystCommented:
Just caution with CLUSTERED INDEXES as they can also be rebuilt with ONLINE = ON but...performance may suffer in particular while these ones are done and a decrease in performance should be expected in general while indexes are rebuilt due to resource consumption but if your database is not backend to a 24/7 system you should have no issues to do that during off-hours.

 Also, do not change Recovery option of a production database without checking existing backup/restore and disaster recovery policies internal to your place.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Most of DBAs I know follows Microsoft's recommendation and that's includes Ola Hallengren.
Fragmentation level:
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)


To reduce the use of transaction log you can use the option SORT_IN_TEMPDB = ON but maybe your transaction log is not correctly sized and better to provide in advance the necessary disk space.
0
 
IKtechAuthor Commented:
I removed the reorg task and just do a rebuild.  Also the max size for the transaction log was increased.  Things seem to be running well at the moment.  Thanks!
0
 
lcohanDatabase AnalystCommented:
Make sure you take regular T-log backups - ideally one as soon as the rebuild is done so the space is recycled/release back to use inside the T-log file.
0
 
IKtechAuthor Commented:
it's done hourly...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I removed the reorg task and just do a rebuild
Technically it's a solution that works but in my opinion this is a waste of resources (CPU, Memory and Storage) and will cause more locks during the rebuild task.
REORG is there for a reason and not using it won't bring any harm but using it will bring benefits in terms of limiting the resource usage.
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.