Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-01-03 : 21:21:38
|
| Hi,This is the 3rd time I stopped a job while it was running, even though EM shows after a hell lot of time that the job has been cancelled, it actually doesn't!!!!!!!! The job finishes no matter what you do! Why does this happen?I have optimization job (rebuild index) which I stopped while it was running because it filled up log fully!! I have 15 mins log backup but backup log (also used for logshipping) job always seems to get stuck with other optimization jobs - they both just keep executing step 1 - never finish (atleast for about an 1hr) !!!Why doesn't the job die the minute I stop it?? I used KILL process in Current Activity window - it didn't work! What can I do in Query Analyzer to stop a job when I want it to stop?Thanks,Sarat. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-03 : 22:19:24
|
| If you're using SQL Server 2000 in full recovery mode, the index rebuilds are logged, and cancelling the jobs would roll back the changes. You can change the recovery model to minimize the logging of these activities.Have you run the job to absolute completion without cancelling it? Do you know if it naturally takes that long to run? You may have some data corruption that needs to be fully repaired before the job runs properly. You should consider running the optimization outside of a job to see how it performs (doing it off-hours might be necessary) |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-01-06 : 11:37:06
|
| I believe even after cancellation, the job ran successfully because the output looked good to me.ex:----Rebuilding indexes for table 'PS_YE_W2C_DATA'Rebuilding indexes for table 'PS_ZBD_JOBCODE_TBL'Rebuilding indexes for table 'XLATTABLE'Rebuilding indexes for table 'XLATTABLEDEL'** Execution Time: 1 hrs, 4 mins, 49 secs **Deleting old text reports... 1 file(s) deleted.End of maintenance plan 'RebuildIndexes' on 1/3/2003 6:31:49 PMSQLMAINT.EXE Process Exit Code: 0 (Success)-------------------------------------------I know the XLATTABLDEL is the last table in the database if sorted alphabetically.If the job rolls back the changes, shouldn't the text report indicate rollback comments?**The problem I have is I cannot change the recovery model because I have logshipping setup which wont work unless I have full recovery model. Could logshipping and rebuilding index jobs running at the same time be a problem? Should I stop logshipping, change recovery model to simple and then do index job?The answers to your questions would be:1. Yes I have run the job before (before we upgraded to web version of our s/w) to its completion and it took about 1 hr same time as it takes now. => BUT I have double the number of tables now (8000, B4 it was 4000)...But most of them do not have any data and hence are not used.Q1: If there is no data, should the index be even built as frequently as for other tables?2. How I do know if I have corrupted data?3. Running optimization job outside of EM? You mean in QA? That is my next step as I am not very happy with EM, it keeps hanging now and then.4. If multiple people open EM on different workstations, does it impact the performance of server? Thanks,Sarat. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-06 : 12:01:57
|
| If you set up the job using the Maintenance Wizard, well...let's just say you should look at doing it another way. I would definitely NOT combine log shipping and index maintenance in the same job, especially if the job needs to roll back as a whole.It really doesn't make sense to rebuild the indexes and then ship the log off to another server. It's just a lot of extra log records that won't be used. Do the log shipping, and then rebuild the indexes after it's completed. If it allows you to change the recovery model without affecting the logshipping, then do it.As far as checking for data corruption, look in Books Online under DBCC CHECKDB, CHECKTABLE, and the related DBCC commands. It will explain the various repair options you could use to fix corruption, if there is any. It may NOT be data corruption either, but it's a good thing to check periodically, especially if you're rebuilding indexes on a regular basis.And while it doesn't hurt to rebuild indexes, it's not all that necessary if only 5-10% of the data in the table changed since the last reindex (unless you see major performance problems that are fixed by rebuilding) If you are rebuilding a lot of tables like that, try taking them out of the rebuild job and see if it speeds up the process. You can always put tables on separate maintenance schedules to better balance the load on the server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-06 : 12:44:38
|
| I am not sure if it is true in SQL2k, but I know that in other versions, you can not cancel a DBCC statement (which is what the optimization job is doing). They can only be cancelled with a restart of the service, which is not recommended. |
|
|
|
|
|
|
|
|
Job scheduled via EM never stops