Question
· 7 hr ago

Controlling disk usage on LOAD DATA error logging

#InterSystems IRIS

Hi everyone,

I'm dealing with a situation where LOAD DATA operations — especially large batches with data inconsistencies — are consuming a lot of disk space. I've noticed that the same error messages are being repeatedly logged in the %SQL_Diag.Result and %SQL_Diag.Message tables, which is significantly increasing the size of the database.

One idea was to move these diagnostic tables to a separate database with a configured size limit, but before going down that path, I'd like to ask:

Is there a simpler or more efficient way to handle this?
For example:

  • Can we limit the number of repeated logs?
  • Is there a way to disable or filter out redundant error messages?
  • Are there built-in retention or cleanup mechanisms for these tables?

Any suggestions or best practices would be greatly appreciated!

Thanks in advance,
José Pereira

Product version: IRIS 2024.1
Discussion (1)2
Log in or sign up to continue
DC AI Bot · 7 hr ago

To manage disk usage related to error logging in %SQL_Diag.Result and %SQL_Diag.Message, here are some possible approaches:

  1. Limiting Redundant Logging:
    There are no specific mentions of built-in mechanisms in the documents retrieved to limit repeated logs or filter out redundant error messages in %SQL_Diag tables directly. However, you could achieve this via custom application logic or by introducing conditions around your LOAD DATA or SQL processes.

  2. Data Cleanup:
    There isn’t a specific retention or automated cleanup mechanism for %SQL_Diag.Result and %SQL_Diag.Message. Such tables would likely require periodic manual or automated maintenance tasks, such as DELETE statements or archiving historical records.

  3. Moving Tables to a Separate Database:
    Moving diagnostic tables to a separate database with storage limits is a viable strategy. This allows you to isolate and control the potential disk usage without impacting the primary database. Implementing this change might involve:

    • Redirecting the error logging functionality to use tables stored in the designated database.

  4. Transaction Commands:
    While managing transactions should include commands like TStart and TCommit to ensure database integrity, these don't inherently affect logging configurations but do guarantee atomicity. Locking mechanisms could help manage concurrent operations during troubleshooting large batches [1].

  5. SQL and ObjectScript Integration:
    For advanced operations based on requirements, ObjectScript commands and SQL functionality can be further customized and integrated.

Unfortunately, no direct system option or detailed guideline about controlling disk usage specifically for the %SQL_Diag tables and limiting repetitive entries is provided. Custom scripting and database management strategies will likely be required to achieve this goal.

Let me know if you need assistance with a specific implementation!

Sources:

0 0