Very Computer

by **Wayne Sheffiel** » Sat, 07 Oct 2000 04:00:00

In sql 6.5, is it possible to run an update command that doesn't utilize the
transaction log?

I need to update a field in a large table that should have been zero
prefilled. I am running out of log space when I try to run it.

Thanks,
Wayne

by **Stefan** » Sat, 07 Oct 2000 04:00:00

Wayne,

The update will be always logged. Try not to run the update as one single
transaction, but as multiple transactions, using set rowcount, so you'll
have control over the transaction log. When the log fills up, dump it.

Quote:> In sql 6.5, is it possible to run an update command that doesn't utilize
the
> transaction log?

> I need to update a field in a large table that should have been zero
> prefilled. I am running out of log space when I try to run it.

> Thanks,
> Wayne

by **Wayne Sheffiel** » Sun, 08 Oct 2000 04:00:00

okay, so I do something like:

-- trunc log on checkpoint is set
set rowcount 500
checkpoint -- start off with an empty log
update <table> set field = <calc new value> where datalength(field) <> 11

checkpoint
update <table> set field = <calc new value> where datalength(field) <> 11
end

So, how do I determine what a good number is for the set rowcount? Should I
just set it to something real low (like 500 / 1000), or how do I figure out
just how high of a value I can use?

Thanks again,
Wayne

> Wayne,

> The update will be always logged. Try not to run the update as one single
> transaction, but as multiple transactions, using set rowcount, so you'll
> have control over the transaction log. When the log fills up, dump it.

> > In sql 6.5, is it possible to run an update command that doesn't utilize
> the
> > transaction log?

> > I need to update a field in a large table that should have been zero
> > prefilled. I am running out of log space when I try to run it.

> > Thanks,
> > Wayne

by **Shau** » Wed, 11 Oct 2000 04:00:00

CHECKPOINT only flushes committed transactions (Dirty pages) to disk and SQL
Server does it automatically every minute or so, the issue is "Are you
maintaining a Transaction Log?". If so then you must rather run more
frequent backups for the log. You can also institute is a task that fires
on a predefined alert, that kicks off when log file is say 80% full. So
keep the WHILE statement if you are experiencing problems.

Shaun

> okay, so I do something like:

> -- trunc log on checkpoint is set
> set rowcount 500
> checkpoint -- start off with an empty log
> update <table> set field = <calc new value> where datalength(field) <> 11

> checkpoint
> update <table> set field = <calc new value> where datalength(field) <>
11
> end

> So, how do I determine what a good number is for the set rowcount? Should
I
> just set it to something real low (like 500 / 1000), or how do I figure
out
> just how high of a value I can use?

> Thanks again,
> Wayne

> > Wayne,

> > The update will be always logged. Try not to run the update as one
single
> > transaction, but as multiple transactions, using set rowcount, so you'll
> > have control over the transaction log. When the log fills up, dump it.

> > > In sql 6.5, is it possible to run an update command that doesn't
utilize
> > the
> > > transaction log?

> > > I need to update a field in a large table that should have been zero
> > > prefilled. I am running out of log space when I try to run it.

> > > Thanks,
> > > Wayne

Very Computer

sql 6.5 update without logging

sql 6.5 update without logging

sql 6.5 update without logging

sql 6.5 update without logging

sql 6.5 update without logging