update for bulk logged

anushahanna
anushahanna used Ask the Experts™
on
let's say you need to update 60 million rows, correcting the company code- could that be considered bulk logged , so can reduce log space usage at that time.

any other option for updates and log, while in FULL mode/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst
Commented:
Besides all that depending on the OLTP - inserts/deletes on the table where you need to do this update I suggest:

1. No matter what do not attempt update them all in one batch on a live production syste even if you use explicit hints like UPDLOCK or ROWLOCK

2. related to the above, write your update code in BATCHES and use either one of the above suggested hints in the update statement.

Author

Commented:
thanks for the nice ideas-

would bulk_logged  help at all, or is it just the same as FULL for one line updates, like the above? (update all rows for a column with the same data).. is that considered bulk operations?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lcohanDatabase Analyst
Commented:
I would use simple recovery if possible but I think you can't use that due to your backup and besides that I don't think you want to switch database recovery mode back and forth while is accessed and used on line.
If by any chance is one time update that could be done while db is not accessed in production and could be off line (of line from the application and all users/processes hitting it) it is worth to switch it to simple even if you need to redo the backup after you come back to full recovery mode.
lcohanDatabase Analyst
Commented:
just crossed my mind...You said " to update 60 million rows, correcting the company code" and I hope you don't need to do some UPDATETEXT on some text (not varchar or other data type) in SQLright?
If you have to do that on such volumes you can forget it...I went through that pain at some point and it is infinitely faster to bcp them out in a flat file, do the update outside sql at file system level then bcp them back in. In that case only bulked_logged recovery may help. But again - only if you have to update text or ntext columns.

Author

Commented:
lcohan- very good thought-

but why restrict it to char only, why not int?
lcohanDatabase Analyst
Commented:
Sure you can do that if the data type you are using is int(eger).

Author

Commented:
Thanks for the very good idea..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial