Cancelling Long Running Reindexing jobs

Question

Post reply

Cancelling Long Running Reindexing jobs

dcmccue

Mr or Mrs. 500

Points: 540
More actions
August 28, 2003 at 8:14 pm

#59435

I have a server that cannot be backed up in a timely manner due to long running reindexing jobs. Occasionally, they'll run for 800 hours or so, and prevent db or tranlog backups from taking place. Can the reindexing jobs be cancelling, or do I need to disable them and wait for them to finish? If I do cancel them, how long will it take for the process to die?

Viewing 9 posts - 1 through 8 (of 8 total)

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

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

800 hours / 24 = 33.33 days

What is the database size / processor / memory / disks of this machine?

Do you use SQL Server SQLMaint?

dcmccue Mr or Mrs. 500 Points: 540 More actions · Answer 2

Dual 1 GHz Processors, 1 GB RAM, 2 GB Database. Job is scheduled through SQLMaint, it reindexes 5 2GB DB's in the same job.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Is this a 365 x 24 system?

See article at http://www.sqlmag.com/forums/messageview.cfm?catid=3&threadid=9568

dcmccue Mr or Mrs. 500 Points: 540 More actions · Answer 4

It can be rebooted, but users need to be warned in advance. So yes, 365 x 24 is the goal. I'm less concerned about why this is happening, more concerned about how to stop the current jobs. Life without backups makes me squirm.

ckempste SSCoach Points: 17983 More actions · Answer 5

Hi there

YOu didnt mention the actual command being executed here, this is important when debugging the issue. What other work is being run at the same time? lots of blocking issues? cpu's pegged at 100%?

Cheers

Ck

Chris Kempster

http://www.chriskempster.com

Author of "SQL Server 2k for the Oracle DBA"

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

dcmccue Mr or Mrs. 500 Points: 540 More actions · Answer 6

I'm not in front of the system, so I can't give you the actual text of the last command. It's a reindexing job as setup by a maintenance plan. No blocking problems as listed by sp_who2. CPU utilitization, disk queues, etc.. aren't high 50%,.3 average. However, the job just runs forever and the schedules backups don't complete while it's running.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

Why I ask about 365 x 24, is there anytime of the day that one can get all the users / locks of the database. In the article, another site was experiencing interference / locking problems.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

I would try reindexing the tables outside of the maintenance plan and see how that works. If it's taking 800+ hours I would also look at just dropping and recreating the indexes, remembering to do the clustered index first.

In my opinion, the maintenance plans aren't the best way to go. I have scripted my backup and dbcc jobs out and run them through the scheduler. Never had a problem with failures or blocking.

Tom