try to understand how updating a 1Gb table fill 10Gb of log

207 Views

--- sybase information

--- Adaptive Server Enterprise/15.0.3/EBF 17690 ESD#1.1 RELSE/P/Solaris AMD64/OS 5.10/ase1503/2681/64-bit/FBO/Thu Aug 20 15:37:40 2009

--- page size is 4K

Good afternoon,

I hope someone can help me understanding how my update statement on a 1Gb table can fill my 10Gb of log.

I have the following update statement:

UPDATE table_A

SET CLASS_ID = contact.M__INTID_

FROM

table_B hdr, table_A doc, table_C contact

WHERE convert(char(20),hdr.NB) = doc.DOC_REF

AND contact.M_REFERENCE = hdr.M_CONTACT

AND doc.ORIGIN_ID = contact.ORIG_REF

AND doc.VERSION = contact.VERSION

at the moment it's the only transaction on my database (syslogshold output)

6 0 637 11471352 f809af002f00 000000000000 13/05/2013 17:29 $upd 1274

9 0 863 15644033 81b5ee000200 000000000000 13/05/2013 14:58 $chained_transaction 1726

this spid is also the only one locking anything on this database

sp_lock output:

0 863 1726 Ex_table 1152887968 0 0 database_A Non Cursor Lock

0 863 1726 Sh_intent 1259928254 0 0 database_A Non Cursor Lock

0 863 1726 Sh_intent 2104507320 0 0 database_A Non Cursor Lock

As I said, the table impacted in about 1Gb (index included - sp_spaceused output)

name rowtotal reserved data index_size unused

------------------ ----------- ----------- --------- ------------- ---------

table_A 1430142 993180 KB 817236 KB 174496 KB 1448 KB

And now if I look my logsegment for the database it keeps filling (sp_helpsegment logsegment output)

total_size total_pages free_pages used_pages reserved_pages

------------- -------------- ------------- ------------- -----------------

10000.0MB 2560000 504070 1801288 254642

for me even if I update my whole table, I should only be used about 2Gb of log maximum, I don't understand how it keeps being filled (probably the chained_transaction).

Thanks for your advices.

Simon