We have to carry out the following operation within our database :

There's a table A that has column B_ID that's an overseas answer to the table B. You will find many rows within the table A that have a similar worth of B_ID and you want to fix this by cloning the related rows in B and redirecting the rows from the for them.

All of this is comparatively easy and we've already produced a script that solves this by iterating on the cursor and calling a saved technique of cloning the row in table B. Now however , both A and B tables are huge and there's also a large number of the particular groups within table A pointing towards the same row in B.

What we should finish track of is (soon after minutes of execution) is filling the transaction log and crashes. We've even attempted to split the job into batches of reasonable size and run them 1 by 1, but this eventually fills in the log.

Aside from in some way cleaning the log, can there be a way to deal with bulk card inserts / updates of information in SQL Server that might be faster and never inflate the log whatsoever ?

If you're able to go ahead and take operation offline you can alter the recovery type of the database, build your changes, then alter the recovery model back.

Overall although the transaction log can there be to safeguard you, to permit rollback etc, and it'll get bigger while you do removes etc for monitoring reasons.

NOTE: by using this approach make sure to possess a darn good backup first....

I cant conceive of why you would like to do that. Wrong using the current someone to many relationship? Shouldn't you be now likely to cash bigger tables to do all of your prevent?

However considering that for you to do this, first are you currently taking transaction log backup copies, how often? If it's less often than every a quarter-hour, then change that. Whenever you backup the log, the log will get cut down, if you don't backup the log it develops before you exhaust space. Also possibly the development percentage you specify for that log is simply too small. Increase might it could assist you too.

You can test carrying it out in SSIS but I'm not sure if that can help the logging problem really. It can help improve performance in doing the job though.