4

I am using Windows Server 2003 SP 2 and SQL Server 2005 Enterprise edition

I am creating a "Rebuild Index" job for a particular database and I am successfully able to run the job.

But when I try to enable the "Keep index online while rebuilding" option, the job does not execute successfully and throws out errors.

I have attached the screenshots. Any help would be app

http://img535.imageshack.us/gal.php?g=error1r.png

PS: I am not able to attach the images here since i do not have 10 points yet!

Thanks.

Improve this question
1
  • if any of the provided answers have answered your question please don't mind checking one of them as the answer for this question. Thanks Gabriel
    – Gabriel Guimarães
    Dec 14, 2010 at 17:08

2 Answers 2

Reset to default
1
  • 1st screenshot is an unrelated error. Most likely you havent setup a mail profile.
  • 2nd screenshot doesn't say much.
  • 3rd screenshot shows the 'index only' job failing, which could be for any reason. You need to click on the blue link under message. or look at the job history for that job. You can do this by right-clicking the job and clicking view history. Then expand the details.
  • 4th screenshot doesn't say much. The view t-sql button may help?

Best suggestion i can make is that you view the t-sql that the job is trying to run copy/paste this into SSMS and see what error is returned there.

Improve this answer
2
  • Thanks for the comment. Actually I learnt from some threads that 'Keep indexes online while re-indexing' option will fail if the tables contain LOB values. Why is that?
    – bdhar
    Mar 30, 2010 at 7:24
  • 1
    thats correct. LOB's such as varchar(max), varbin(max), text, image, as well as xml and spatial data types aren't supported for online rebuilds unfortunatley. A well written index maintenance job will check if the table has any off these types of columns & choose the appropriate type of rebuild
    – Nick Kavadias
    Mar 30, 2010 at 8:34
1

Its probably because of not everything being able to rebuilt online. you better check http://technet.microsoft.com/en-us/library/ms188388.aspx

it says rebuild online will fail when its tried on XML index, spatial index or LOB objects data types columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) and xml.

By the way only for documentation Online indexing rebuild is only available at Enterprise and upper.

there's a free script solution which will enable you to create a better maintenance script. with this script you can determine that some actions be taken for determined indexes types. Like when you have High fragmentation you rebuild, but when the fragmentation is low you can reorganize and update statics (reorganize is always online) you can find the scripts on: http://ola.hallengren.com/

Good luck

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .