cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

--- 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 KB817236 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

Former Member
0 Kudos

Thank you Mark,

I love this "community" so many things to learn from 'the experts'

I'll use these valuable information and see if my guilty statements can be classified in your cases.

Back to my case, I noticed that the active transactions that was actually filling my syslogs was made of at least 4 UPDATE statements. We'll see with dev team if it's worth separate these statements in separate transactions

Simon

former_member188958
Active Contributor
0 Kudos

Hi Simon,

If the answer is that they can't as the four updates form a transaction, then the next question to ask is whether the transaction could be applied multiple times on subsets of the entire data rather than on the entire data set at once. 

As a general tip for developers (if they are not using chained mode) is that it is helpful for troubleshooting if they make a habit of providing the optional transaction_name value in their BEGIN TRAN statements, using a unique value for each transaction.  These names show up in the BEGINXACT log records, so if you are investigating an issue through looking at the log records, the transaction name can quickly lead you back to the exact area of the client that generated those records.

Former Member
0 Kudos

Thank you Bret,

When you state "These names show up in the BEGINXACT log records", you meant in the output of dbcc log, right?

By the way, the ouput might be very very long.

Simon

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Simon,

You mentioned:-

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

syslogshold shows you the oldest open transaction on each database, not all transactions.

Is there anything else running on the database? Maybe check systransactions.

It could well be log records generated from preceding statements in the chained transaction but it couldd also be any log space accrued by any other actvity that may have started since the chained transaction was initiated, so what you are looking at it the log that has been used since

13/05/2013 14:58  

No log can be truncated before this point.

BTW, if you are on 15.0.3 I guess this isn't an ASE running SAP in which case future posts may be better in the Sybase ASE for custom application group)

Cheers,

Simon

Former Member
0 Kudos

Hi Simon,

Thanks for the remark, I forgot about that 'important' point (syslogshold shows you the oldest open transaction on each database).

Thanks also for the remark regarding the forum to use for posting my question.

Simon

Former Member
0 Kudos

Simon,

By the way, let's imagine we have 3 transactions filling the log segment. Is there a way to know the space used in log segment per transaction?, is there a way to know the space used per object in log segment?

Does it make sense to get these metrics?

Thank you

Simon

sap_mk
Active Participant
0 Kudos

Hi Simon,

You can try something like this to see what is using up the majority of the tran log. Note that this query may take a minute to run, depending on the number of rows in syslogs.

select top 5 xactid,count(*) from syslogs group by xactid order by 2 desc

go

Sample output:

xactid

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

0x590800001200          44

0x5d0800000500          44

0x2a0800000400          32

0x660800000900          23

0x670800001400          23

If we look at the first transaction id, we need to separate that into a page and row. The first 4 bytes are the page number, the last 2 are the row. From my first line of output:

0x59080000 becomes 0x00000859 = 2137

0x1200 becomes 0x0012 = 18

Now we can display information about that transaction:

dbcc traceon(3604)

dbcc log(<database>,1,2137,18,0,0)

go

LOG SCAN DEFINITION:
        Database id : 1
        Forward scan: starting at beginning of log
        Log records for session id 2137,18,0
        Log operation type BEGINXACT (0)

LOG RECORDS:
        BEGINXACT                (2137,18)      sessionid=2137,18,0
        attcnt=1 rno=18 op=0 padlen=1 sessionid=2137,18,0 len=72
        odc_stat=0x0000 (0x0000)
        loh_status: 0x0 (0x00000000)
        masterxsid=(invalid sessionid)
        xstat=XBEG_ENDXACT,
        spid=1 suid=0 uid=1 masterdbid=0 dtmcord=0
        name=sysconfigures   time=Sep 11 2012  5:09:32:876PM

By specifying 0 as the last parameter in dbcc log, I told it to only print the BEGINXACT record for this transaction. This record contains the name, which is sometimes helpful. Here it is sysconfigures and since this is from Database id 1, I know this was a sp_configure transaction.

To print all the rows from a transaction (this will be a lot for your highest listed transaction), use:

dbcc traceon(3604)

dbcc log(<database>,1,2137,18,0,-1)

go

One final note. This will tell you which transaction is using the most number of entries in the tran log, which is usually a good indicator of your problem. However, if a transaction is updating (for example) a large character field, the before and after images in the log can cause it to use up a log page for each of those records. If a transaction did a lot of this, a lower number of log rows could use more pages (space) than a higher number of log rows for a different type of transaction.

Regards,

Mark Kusma

Former Member
0 Kudos

Nice explanation Mark.

Let's not forget though that even if a transaction is using a ton of log space, it won't be reclaimable until the oldest transaction has been committed (or rolled back).

We may have transaction A that has 1 record associated with it, but has been waiting on a commit, subsequent to that transactions B through F have begun (and even committed), those transactions took 10GB.

That 10GB will remain allocated and active (after dump tran) until transaction A is completed.

So basically transaction A is responsible for 10GB of log so I'm not always sure it is that useful to breakdown the log usage by transaction in log full scenarios - although in terms of trying to breakdown large trans into smaller more manageable ones ones, it's ideal.

sap_mk
Active Participant
0 Kudos

Hi Simon,

No doubt that:

begin tran

go

and go to lunch will eventually cause problems. But if that transaction is the problem (oldest open), then it will be revealed by your previous answer of syslogshold. I was covering the part about what is using up all the space since the oldest one hadn't generated that much log activity. Between the two, one should get a pretty good idea of how the 10GB log is getting filled.

Mark

Former Member
0 Kudos

Simon, Mark,

Thanks for your explanations, they're very valuable and I just can't wait to practice on a use-case at my work.

Now I have a question that might highlight my few knowledges on ASE. Regarding this part "Let's not forget though that even if a transaction is using a ton of log space, it won't be reclaimable until the oldest transaction has been committed (or rolled back)."

does it mean that if I try to 'dump tran', only the part prior to the oldest active transaction will be save in files then remove from syslogs?

What if my database has option 'trunc log on chkpt', as far as I understand the syslogs will be cleaned of 'any data' garantee to be written on 'data' disks? am I correct?

if anyone has an article, explaining the mechanism, it will be very nice.

Thanks again for your valuable advices.

Former Member
0 Kudos

Mark, Simon,

I think I got good explanations there:

http://www.sybaseteam.com/transaction-log-management-in-sybase-t-497.html

Simon

former_member188958
Active Contributor
0 Kudos

Not quite.  The DUMP TRAN will write out the entire contents of the log to file, including all the open transactions.  But it can only truncate from the syslogs table the records that come before the begin tran of the oldes open transaction (or the replication log transfer marker, if replication is active).

The truncate log on checkpoint option is similar.  It doesn't write anything out to a disk file, of course.  It truncates the log records up to the begin tran of the oldest open transaction (or LTM marker, if active).

Former Member
0 Kudos

Thanks Bret for the clarification.

Simon

Answers (1)

Answers (1)

kimon_moschandreou
Contributor
0 Kudos

Hi Simon,

Are there any triggers involved? I suppose this statement runs in its own transaction and nothing else.

Regards,

Kimon

Former Member
0 Kudos

Hi Kimon,

Indeed there aren't any triggers but only a big transaction I guess. Maybe I'll "dbcc log" to show the dev they should try to improve their code.

Simon

kimon_moschandreou
Contributor
0 Kudos

Hi,

I posted my comment before checking Mark and Simon's answers. What Mark proposed is really impressive for me and shows a very good approach for this issue.

Best regards,

Kimon