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 |
|
cbeganesh
Posting Yak Master
105 Posts |
 Posted - 2004-03-05 : 12:32:00
|
| I have some jobs running for long time. I have to kill those process. I dont it to rollback since it may take long time. I want to force timeout on those process id. Any idea.. |
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-05 : 14:07:56
|
| You cannot stop an data change task without innitiationg a rollback. The rollback is to keep your data integrity.If you need to, break your updates into smaller subsets then only the most recent one will be rolled back, and the rollback time will be minimized. Typically a rollback is as long as the update. So if an update takes 5 minutes, the rollback will take 5 minutes.If your jobs dont perform data mod tasks or do it in groups with commits between then only the most recent is rolled back.Chris |
 |
|
|
cbeganesh
Posting Yak Master
105 Posts |
Posted - 2004-03-05 : 14:14:01
|
| Thanks crisBut I dont want to rollback. I just want to abortis there any way do do this |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-05 : 14:21:34
|
| If the jobs update data then not that I know of without putting your database into suspect mode.If the jobs are finished but for some reason not stopping (dts can do this sometime) then you can try stopping dtsrun on the server. But I'd be sure they are hung, not still processing before doing this.I think we need to know specifically what the long running jobs are doing and what point in their processes they are at to be able to offer anything more.Most SQL is set based, not row based, so if sql is doing an update on a set, it needs to finish the entire set or none of it. This is why a rollback is required - its not optional. SQL needs to either commit the entire change or make sure your data looks like it did before the change was started. It does this to maintain database integrity.Chris |
|
|
|
cbeganesh
Posting Yak Master
105 Posts |
Posted - 2004-03-05 : 14:27:02
|
| Yes, I ran some updates parallel and when i check the locks it showed it's waiting for CXPACKET , Then i found from the net its is bug in sql server. We have 2000 service pack 3. Now that i have killed the jos, It's rollbacking the updates. Now i want to run the updates serially and i was hoping i could start the update immedialtely if it's not rollback. Now i have to wait till the rollback is compltedThanks Cris |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-05 : 14:33:26
|
| Yup. Its an unfortunate thing. But you might have one other option. But you need to work out if it will be faster. If you have a valid current backup then you can stop sql. your database will go into suspect mode when you bring sql back up. then restore from your back up. This is faster if a) you have a valid backup and b) your database restore takes less time than you will be rolling back for.If you dont have or dont know that you have a valid backup, dont even consider this option.On a happy note, if you've been waiting on a cxpacket for most of the running time, then the rollback may be very fast compared to how long the job was running.chris |
|
|
|
cbeganesh
Posting Yak Master
105 Posts |
Posted - 2004-03-05 : 14:42:08
|
| we dont have a valid backup. Hope the rollback will be completed soon. Also do you know how add a timeout to an update statement |
|
|
|
|
|
|
|
|
TIMEOUT ON PROCESSID..ASAP