Rebuild Index Task doesn't completes in Maintenance Plan

Question

Post reply

Rebuild Index Task doesn't completes in Maintenance Plan

sql.monk

Mr or Mrs. 500

Points: 570
More actions
March 15, 2016 at 7:27 am

#316952

Hi ,
I am fairly new to SQL Server Administration.
My maintenance plan was running fine with rebuild index task but for some days now its not completing taking long hours and not completing it. I had a maintenance plan running after hours first it used to took 2:20 min for 150 GB database but now its now completing for long hours can any one suggest with it and also sometimes its hitting the transaction log files making transaction log drive full in production . Plus i have a script running in my sql server agent job which detects the blocking spid's processes and mail us so a long number of mails come from rebuld index task its also creates a deadlock situation so it come up in mail.
kindly help me on these to get it resolved
Regards
SQL-MONK

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

Are you running the built in SSMS rebuild indexes maintenance plan task?

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 2

yes , i am running the inbuilt SSMS rebuild index task in the maintenance plans .

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

That task rebuilds every index, even if it doesn't need to be...

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 4

yes it rebuilds each of them even if they don't ......!

Jeegsjaan Grasshopper Points: 13 More actions · Answer 5

Do you have special switch turned on in your Maintenance Plan task? Like "Keep index online while re-indexing"

Did you selected Tables and view or just tables?

Jigar

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 6

If you're running the rebuild index task built into SQL server it will run slower and slower and use more and more log space as the DB grows as it rebuilds all the index regardless of fragmentation which uses a lot of processing and log space.

Try using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html instead, you might need to configure the settings a little.

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 7

@jigar No i didn't make switched on "Keep index online while re-indexing" but i think online option will not work in SQl server standard 2008r2 edition and i selected the both tables and views .

Thanks jigar

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 8

ZZartin (3/15/2016)
If you're running the rebuild index task built into SQL server it will run slower and slower and use more and more log space as the DB grows as it rebuilds all the index regardless of fragmentation which uses a lot of processing and log space.
Try using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html instead, you might need to configure the settings a little.

Let me try and will get it back to you soon

Thanks

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 9

ZZartin (3/15/2016)
If you're running the rebuild index task built into SQL server it will run slower and slower and use more and more log space as the DB grows as it rebuilds all the index regardless of fragmentation which uses a lot of processing and log space.
Try using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html instead, you might need to configure the settings a little.

@jigar No i didn't make switched on "Keep index online while re-indexing" but i think online option will not work in SQl server standard 2008r2 edition and i selected the both tables and views . Can you suggest me what to select tables or views ..!!

Thanks jigar

MadAdmin SSChampion Points: 11261 More actions · Answer 10

ZZartin (3/15/2016)
If you're running the rebuild index task built into SQL server it will run slower and slower and use more and more log space as the DB grows as it rebuilds all the index regardless of fragmentation which uses a lot of processing and log space.
Try using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html instead, you might need to configure the settings a little.

+1 !!!!!!!!

The cool thing about ola hallengren's solution is that it logs which indexes are rebuilt, together with the start and end time.

It works on fragmentation thresholds, so you can tell it to only rebuild with fragmentation larger than 25% say, instead of every single index.

You also know which index is taking long.

I run online index rebuilds daily and because of the thresholds, I only get about 4 indexes rebuilt a day, and sometimes none.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 11

MadAdmin (3/17/2016)
ZZartin (3/15/2016)
If you're running the rebuild index task built into SQL server it will run slower and slower and use more and more log space as the DB grows as it rebuilds all the index regardless of fragmentation which uses a lot of processing and log space.
Try using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html instead, you might need to configure the settings a little.
+1 !!!!!!!!
The cool thing about ola hallengren's solution is that it logs which indexes are rebuilt, together with the start and end time.
It works on fragmentation thresholds, so you can tell it to only rebuild with fragmentation larger than 25% say, instead of every single index.
You also know which index is taking long.
I run online index rebuilds daily and because of the thresholds, I only get about 4 indexes rebuilt a day, and sometimes none.

Yah i setup the ola hallengren's script and the work was done but don't understand why when i was running the ola hallgren script manually and rebuiting the indexes of the database...the transaction log file drive was increasing so i started taking the backup of transaction log files for that database so it came under control but couldn't understand it will it happen for next time also ? if i will schedule it automatically with sql cmd that time how to tackle the problem of increasing the transaction log file ?