DBClean – staglog

This is part of a series focusing on the details of each item that needs to be pruned in a Commerce database.

STAGLOG

What is it?

Staglog is a table in the staging database that records changes in data. Staging triggers on all stagingprop tables write to it when there is a change to any of those tables. Staglog reads from it and updates the stgprocessed column as it processes changes. Data is not usually removed by any other process, so this table can get big if not pruned properly and frequently.

Potential for impact

This is most likely to impact how fast stagingprop runs and how fast you can query the table to troubleshoot stagingprop problems. Both important, but it would have to be really out of control before affecting normal operations.

Where this Applies

This is one of the few areas that only applies in a staging database. You may have a test staging database in your setup, and it would apply there as well.

Table (other tables may be involved in cascading deletes)

STAGLOG

SQL

note: always test for yourself, don’t take my word for it

note also that you should be cautious running the SQL during peak loads, and always use “with ur”

Number of rows to be deleted:

select count(*) from staglog where stgprocessed = 1 and stgstmp < current timestamp - <days to retain> days with ur

Pruning method

You can use dbclean to prune this one. Object=staglog and Type=obsolete

If you wanted to prune it manually, you could, keeping in mind all the gotchas for big deletes – you’d probably want to break it up. You don’t want to run it as one statement, but it would be:

delete from staglog where stgprocessed = 1 and stgstmp < current timestamp - <days to retain> days

Determining Retention

Retention time for this depends primarily on how frequently you run stagingprop. This pruning will never delete rows that haven’t been propped, but generally, you want to keep the processed rows around at least until the next stagingprop so you have them for reference if need be. Frequency for running stagingprop varies greatly by client – some do it nightly, some do it weekly, some do it only on demand, and some do it on differing schedules for differing filters. So go with at least 2X your least frequent schedule, and you should be OK. This is something to be decided by the engineers/admins/developers and rarely has any visibility up to the business level.

Indexes to help deletion

You may want to add an index to the orderitems table on stgprocessed, stgstmp and (if you’re using it to split up deletes) stgrfnbr

So, good information? More detail needed in any areas? Let me know what you think.

You may also like...

2 Responses

  1. April 7, 2011

    […] The STAGLOG table holds all of the changes captured by stagingprop to be copied from the staging database to the production database. It is only populated in a staging-role database and not in a production-role database. You’ll find many more details on it in my entries on stagingprop and on dbclean for staglog. […]

  2. September 15, 2011

    […] Staglog (see DBCLEAN – Staglog) […]

Leave a Reply

Your email address will not be published. Required fields are marked *