SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-26-2008, 06:17 PM
| ||||
| ||||
 Transaction logs full Is there a way (SQL syntax or some preparatory command) to cause a statement which implies a possibly large number of operation, either insert or update, to commit every so many rows? In particular the new 'merge ... when not matched insert ...' statement can consume large amounts of log space, depending on the number of 'not matched' rows.      |
|
02-26-2008, 06:17 PM
| ||||
| ||||
| Re: Transaction logs full "Robert Stearns" <rstearns1241@charter.net> wrote in message news:10cadssoe08bd0e@corp.supernews.com... > Is there a way (SQL syntax or some preparatory command) to cause a > statement which implies a possibly large number of operation, either > insert or update, to commit every so many rows? In particular the new > 'merge ... when not matched insert ...' statement can consume large > amounts of log space, depending on the number of 'not matched' rows. > No, unless you are using the import utility which has a COMMIT INTERVAL parameter. You could turn off logging temporarily by altering the table with NOT INITIALLY LOGGED (only if the table was created with that attribute). Your must include the alter table and the SQL statements not be logged in the same unit of work. As soon as a commit is issued, logging is turned back on. If you are using the CLI, use option +c to turn off autocommit and then explicitly issue a commit when you are done. However there are some recovery issues involved with this solution in case of a failure, so make sure have a backup before you do it. |
Linear Mode
