I haven't touched SQL Servers for some time, so please bear with me:

I need to perform a series of hugest operations on our 24/7 database -
basically cleaning it.

In Oracle it could be done via a loop, where a fixed number of records
gets processed with commit in the end of each round. Everyone's
similing.

In Sybase we have an extra problem with the log segment getting full,
which is not good for a live database. To make matters worse, my
database also has a warm standby established via a rep.server.

So far I came up with the following train of action:

a) Switch on 'truncate log on checkpoint'
b) Set the loop with commit. After each commit do a checkpoint
c) Run the process, then bring the truncate on checkpoint switch back
to 'off'

Didn't work. Log still fills and suspends the transaction. Plus I'm
not sure if the manipulations with the switches & checkpoints will
keep the standby in a valid state.

So what is the proper way to perform such actions?

This ASE 12.x

On Tue, 04 May 2004 18:18:21 -0700, Office Drone wrote:

The determining factor here is going to be the speed at which rep server
can read your log and transfer it to the standby - any transactions that
have not been copied to the standby will remain in the log no matter what
you do.

I would probably do things the same way that you do, but I'd run the
primary loop with a delay, and with a fairly small number of rows getting
deleted each time. Remember that rep server needs to delete each
individual row on the standby, so if you do:

set rowcount 10000
while @@rowcount > 0
begin
delete from foo where somecondition...
end

After the first iteration rep server will have to send 10000 delete row
commands to the standby...

If you need to completely empty the table I would simply truncate it (and
the truncate gets replicated as is, so takes no time).

An alternative might be to extract the rows you want to keep to a temp
table (or to a file), truncate the table and then load the rows that you
want to keep.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

"Office Drone" <fulbrighter2 (AT) yahoo (DOT) com> wrote

It could be that your repserver cant keep up with the transactions to
replicate to your standby server. Remember, the secondary truncation point
controlled by the repagent will also be a factor in the log filling up. If
you know exactly what you're updating then you could "set replication off",
apply the changes to your active server and then apply the same changes to
your standby server.

When you tried it the first time and the log went into suspended, log into
the repserver and run "admin disk_space". If the segments (far right-hand
side of info) are full then this means your stable device became full,
causing a back up with the replication and causing the secondary truncation
point to stop moving, hence the log eventually becomes full.

HTH,
Martin

fulbrighter2 (AT) yahoo (DOT) com (Office Drone) writes:

Turn off replication for that table, perform the same SQL on both
replicas, then turn it back on again? Whether this is feasible
depends on whether you have other transactions running at the same
time which might affect the result of your bulk update.

Alternatively, you could turn off replication for the big tables being
changed, change them only on the master, then bcp them out and onto
the replca before re-enabling replication. This depends on not having
any constraints (eg foreign keys) between replicated tables and those
you're suspending replication for.

--
Ed Avis <ed (AT) membled (DOT) com>