Transaction logs full

[Robert Stearns]

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.

[Mark A]

Re: Transaction logs full

"Robert Stearns" <rstearns1241@c harter.net> wrote in message
news:10cadssoe0 8bd0e@corp.supe rnews.com...[color=blue]
> 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.
>[/color]
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.