Sybase large update in batch

Question

I need to do large update in sybase.

Table1 has column a, b, c, e, f, around 9 million records. Table2 has column a, e, f, around 500000 records.

The bellow update sql failed due to syslog full

update table1
set e = table2.e, f = table2.f
from table1, table2
where table1.a = table2.a

After doing research in the internet, the bellow two procedures still failed due to the same error however they did update 5000 records successfully.

Any ideas. Thank you!

SET ROWCOUNT 5000
WHILE (1=1)
    BEGIN 

   update table1
set e = table2.e, f = table2.f
from table1, table2
where table1.a = table2.a

    IF @@ROWCOUNT != 5000
        BREAK
    END
SET ROWCOUNT 0


declare @errorStatus int, @rowsProcessed int
set rowcount 5000

select @rowsProcessed = 1
while (@rowsProcessed != 0)
begin
  begin tran

   update table1
set e = table2.e, f = table2.f
from table1, table2
where table1.a = table2.a

    select @rowsProcessed = @@rowcount, @errorStatus = @@error

    if (@errorStatus != 0)
    begin
       --raiserror ....
       rollback tran
       --return -1
    end
  commit tran
end

set rowcount 0

You could just add more log space to accommodate larger transactions? — Mike Gardner, May 29 '15 at 19:57

score 1 · Answer 1 · answered May 29 '15 at 20:18

It appears that your syslog device is not large enough for what you are trying to accomplish, and the log may not be getting truncated.

You should check the setting of the database (sp_helpdb <DBNAME>) and find out if it says trunc log on chkpt If you don't find that setting, then big transactions will always fill up your syslog.

To fix this you must either add log space to the database, manually dump the log, or set the database option to truncate the log on checkpoint.

If you are worried about up to the minute recovery in case of a system or disk failure, then you should add log space, and schedule more frequent log dumps.

If you are not worried about up to the minute recovery, then you can turn on the database option to truncate the log on checkpoint.

sp_dboption <DBNAME>, 'trunc log on chkpt', true

Sybase large update in batch

1 Answers1