March 15, 2016 at 7:27 am
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
March 15, 2016 at 11:26 am
Are you running the built in SSMS rebuild indexes maintenance plan task?
March 15, 2016 at 2:08 pm
yes , i am running the inbuilt SSMS rebuild index task in the maintenance plans .
March 15, 2016 at 2:15 pm
That task rebuilds every index, even if it doesn't need to be...
March 15, 2016 at 2:26 pm
yes it rebuilds each of them even if they don't ......!
March 15, 2016 at 2:39 pm
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
March 15, 2016 at 2:46 pm
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.
March 15, 2016 at 3:18 pm
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
March 15, 2016 at 3:22 pm
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
March 17, 2016 at 8:43 am
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.
March 18, 2016 at 11:26 am
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 ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply