Home / server / Questions / 123301
In Process
bdhar
Asked: 2010-03-18 01:43:37 +0800 CST

Running "Rebuild Index" maintenance plan with "Online indexing"

  • 772

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.

sql-server sql-server-2005

2 Answers

    • 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.

    • 1

  2. 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

    • 1