Skip to Content
-3

Maintaining a growing table

Hello Experts,

I have a issue. I have got a table in the production where the custom table is growing based on the error log and the msgno which the customer is using to increment everytime the error is created. There is a max limit reach now.

What is the best manner in which we can handle and avoid this case in future.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Apr 26 at 12:17 PM

    Hi,

    The best solution would be to use SAP standard tools for logging like SLG1.

    Here is a blog on this very subject by akankshi prasad

    https://blogs.sap.com/2012/04/18/create-and-view-log-using-slg0-and-slg1-transaction/

    It would be foolish to continue your current path since it has been shown to not be a future proof solution, it is wiser to admit your mistake and fix it.

    Kind regards, Rob Dielemans

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25 at 08:31 AM

    If you got problem with table size, you could consider archive or purge of those data. If data should be kept, use archiving, create those as described in Archive Development Kit, schedule archiving with SARA and adapt some display programs to allow read of archive.

    If your got problem with key limits/size, you could add a key of time/date type like year before a range number managed key SNRO.

    What you wrote is suggesting some log of errors?

    " based on the error log and the msgno which the customer is using to increment everytime the error is created.

    In this case why didn't you use standard logging provided by sap tools (ref: Application Log – (BC-SRV-BAL)) so getting the provided tools for no extra costs?

    Add comment
    10|10000 characters needed characters exceeded

    • The first question you have to answer is: Do you want to keep trace of those records for a very long time or even forever (Even if nothing man builds lasts forever)

      • If no: purge oldest records, If yes: archive data (The standard Application log as both options, either set some expiration date or use archiving object BC_SBAL)
      • Then restart the range number from 0, and watch range number to prevent the duplicate keys errors.

      Other solution(s) can be of the increase size of the key type, give a size wide enough to report the problem behind horizon (e.g. when technology will have changed or your retirement scheduled date...) adding a year field for example is actually add 4 character to key (Also manage range number per year)

  • Apr 25 at 08:04 AM

    Pras,

    Btw,what do you mean by maximum limit reached now ? Is it the field length of the counter or the no.of records keep on increasing causing perfromance issues while fetching data from this table.

    If you have date field in the table, then decide upon a cut over date and delete the records prior to that cut-over date.

    You can develop a custom program to do this activity which can be scheduled as a background job,if required.

    You can even consider the option of Acrhiving the data using SARA.

    K.Kiran.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 27 at 11:49 AM

    Thanks Rob, Kiran, Himanshu and Jelena for the comments provided.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25 at 08:43 AM

    Hi,

    What do you mean with limit reached?

    Is this related to the number of unique keys, or is this storage space?

    Kind regards, Rob Dielemans

    Add comment
    10|10000 characters needed characters exceeded

    • If this is an "error log" table, as you were saying, then why would you need archiving? Archiving is needed for the business information that has a legally required retention period (e.g. invoices may have to be kept for N years). The data in utility / technical tables is simply deleted periodically.

      This looks like a poor key choice for such table but, unfortunately, now you're stuck with this. As far as "band-aid" level solutions go, as others said, you'll need to modify the code that updates the counter. You could use the standard number range functionality (SNRO transaction) for that. Those ranges can be "rolled" automatically, there is a flag for that. This feature has been discussed on SCN before, search in Google.

      But a better solution would be to just redesign this whole thing. I'm wondering why do you even need a custom table when we have standard application log functionality. It comes with the classes these days, has its own display transaction and cleanup process. Or if you must have a Z table then at least it needs to have the keys that are better suitable for the expected data volume and usage. You could use some generic number like GUID and that should prevent from running out of keys for a very long time. Or you could choose a different combination of fields as a key, depending on how this data is accessed. (This part is not clear yet, so it's hard to offer more specific suggestions.)

      Either way, you'll need a program to delete the old entries and it should run periodically in a background job.

  • Apr 25 at 08:06 AM

    Hi,

    you can use the timestamp value (Date +time) with the increment number in case many message at same timestamp .

    Regards

    Himanshu

    Add comment
    10|10000 characters needed characters exceeded