Terminating/ Monitoring Database re index 2

[rsai]

I have a weekly database maintenance job which amongst other things rebuilds all the indexes, this job takes about 8 hours & so if it is still running during 'up' time it has a major impact on the users.

I'd like to be able to quit this job if it overruns or at least be able to give users an estimate of when the system will be available so I have two questions:

1/ If I kill this job off whilst it is indexing what impact will this have on the database ( what state will the indexes be in afterwards).

2/ Is there any way of monitoring how far the job has progressed( beyond the start& end times the databases in sqlmaintplan_history)

 

[MeanGreen]

If you kill the job while it is indexing, it will probably corrupt the index of the table it is on. There really is no way to determine when the job will finish. Only thing you can hope for is that history will repeat itself and you can tell when the job started, so use that as a reference point. You might want to move your index process to Sunday during the day when most operations are down. If your company can not allow this, then maybe you could break up the reindex process. I currently run my reindexing through a cursor process, so it would not be difficult to break it up if I had to.

Good Luck.

 

[SQLSister]

I agree with MeanGreen. I'd break this up into several jobs which run at different times or run the whole thing on a weekend where it won't interfere (check whether the users work overtime before running it in the daytime on a weekend, last thing you want to do is stop users from working when they are already under stress to meet a deadline.) Of course the weekend thing won't work well if it is an Internet app, then you'll just have to break it up into pieces as small as possible and run that way. If you are on lowest usage times. Ours are actually during the work day which is convenient for me anyway. Now might also be a good time to look at your indexes and see if some of them could be dropped.

 

[rsai]

Thanks for the info. I thought that was probably the case but couldn't find any documentation to confirm it. The Job does normally run on Sunday morning when the system is idle but on a couple of occasions has hung up on a lock and so has only been part done on Monday morning. I am putting in procedures to kill any logged in users prior to the job starting to try & prevent this, but it looks like once your into the situation the only thing to do is sit tight until the job completes.

Again thanks