December 6, 2012 at 9:10 am
I have to update 9 million recs. I've buildt a loop that does N updates per loop. I dont wnat to use trasactions because i dont want to block anyone and the update is a bit intense because it's doing a replace on a varchar(max).
question: Is there a way to ensure that it doesnt roll back if someone runs this script then stops it after 10 mins. i want the records who were updated before it was killed to stay updated and not roll back.
December 6, 2012 at 9:18 am
Snargables (12/6/2012)
I have to update 9 million recs. I've buildt a loop that does N updates per loop. I dont wnat to use trasactions because i dont want to block anyone and the update is a bit intense because it's doing a replace on a varchar(max).question: Is there a way to ensure that it doesnt roll back if someone runs this script then stops it after 10 mins. i want the records who were updated before it was killed to stay updated and not roll back.
Sure
loop
Begin tran
Update
Commit
next
December 6, 2012 at 9:39 am
sorry. dont know what i was thinking. it isnt rolling back any updates from previous loops. please disregard
December 6, 2012 at 9:48 am
If you're not using a transaction, then SQL is in auto-commit mode meaning once a statement completes it automatically commits and can't then be rolled back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply