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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply