create alert for rebuild index job

Question

create alert for rebuild index job

rajsin7786

Right there with Babe

Points: 718
More actions
June 20, 2014 at 4:29 am

#294717

Hi Experts,
I will be running rebuild index job for a big table tomorrow, this table is 500GB in size . Last time when i ran this job the DB server crashed because the SAN ran out of space. I had to restore from backup to get DB server running again. The IT team have now added additional 1 TB on the SAN to help me get this rebuild index job running as its business critical.
I basically want to create an alert to send me an email every hour about the percentage completed by this rebuild index job. As its running on a Saturday I don't really want to keep staring my laptop and waiting for the job to finish. Please can you let me know how do i go about creating such an alert?
We use SQL Server 2008 standard

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Just a head's up... it's very possible that the extra 1TB isn't going to be enough if you do the rebuild while in the FULL recovery mode. It'll create a replacement index in the database the index is in before it drops the old one (requires an extra 500GB if it's a clustered index) and it will be a fully logged exercise meaning the log file will most likely exceed 500GB of usage.

Does the table need to be online and available during this rebuild?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajsin7786 Right there with Babe Points: 718 More actions · Answer 2

Hi Jeff,

Thanks for the reply, our DB is in simple recovery mode. The complete task took 5 hours to complete on Saturday. Quite happy that everything went as expected.

The table had a clustered index but we choose not to maintain its index because its fragmentation % was below 5%. So we ended up rebuilding 10 non clustered index on this table.