How to skip writing to transaction log for insert_recordset, update_recordset, and delete_from clauses

(0) Share

Report

Posted on by Preeti Sonambekar

1,245

Hi All,

I am using x++ queries through .net business connector to load data into AX 2009 tables. The data that needs to be loaded is huge and should be loaded based on several different conditions.

Our tool first creates some tables ino AX using .xpo and uploads the external data into these .xpo tables using "RecordInsertList" class. Once the data is in .xpo tables, we then load this data into AX tables using insert_recordset, update_recordset, delete_from i.e. to perform bulk transaction.

We noted that after each load the Dynamics Log file (DynamicsAX1.ldf) increases by 2 GB.
To avoid writing to this log file, I used "skipDatabaseLog = True", but still it does not make any difference.

I also tried to pin-point where the most disk-space is used and when the .ldf increases, and it is when I delete/insert the data from .xpo table. The .ldf does not increase at all when I actually insert/update/delete into AX tables.

Is there any table property that I need to set for my .xpo tables which skips writing transactions to the log file? Please suggest how to handle this because with each load the hard-disk space reduces by 2 GB.

Thanks,
Preeti Sonambekar

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Community Member on at

Like (0)

Report

skipDatabaseLog on the record is used to skip writing into the AX databaselog which can be set up under Administration > Setup > Database log. It does not relate to the SQL Server database log file.

There are a few things you can try:

1) Set log level on the sql server to bulk (might not do anything)

2) make smaller transactions when doing the import into your .xpo tables (eg: split it up)

3) Set SqLServer logfile growth to a small amount

4)...

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

Hello,

Thanks a lot for replying. We can't change any sql server settings at the customer end. we have to manage this disk space issue from within the code. I can think about making smaller transactions but is there are other way i can confirm that the trasactions on the tables created by me don't get logged into trasaction log (ax.ldf) file?

Thanks,

Preeti

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

Hello,

To give you more information, I could find out what piece of code in increasing the ax.ldf log file. It increases when I try to load the data from my intermediate database into the ax tables (that I have created) using RecordInsertList class. I used this class for the purpose of bulk insert but when I looked into Sql Profiler, it was converted into single line insert and which is causing writing huge amount of data to the ax log file.

Based on the post below:

"Both RecordInsertList objects and a RecordSortedList objects can be downgraded in application logic to record-by-record inserts, in which each record is sent in a separate round trip to the database and the INSERT statement is subsequently executed. This occurs if the insert method or the aosValidateInsert method is overridden, or if the table contains fields of type container or memo. Downgrade does not occur if the Database Log is configured to log inserts or alerts that have been set to be triggered by the insert event on the table. The database logging and eventing occurs on a record-by-record basis after the records have been sent and inserted into the database."

My tables in AX don't have any methods, and none of the fileds are of type container or memo.

It would be really great if you could please suggest why RecordInsertList is getting converted into single line insert (row by row insert and not bulk insert), then it might help me fix the disk-space issue.

Thanks,

Preeti

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can you show the global code you use to create, add recordss and finally flush the RecordInsertList?

Is the second parameter of your new RecordInsertList true? (_skipInsertMethod)

Was this reply helpful? Yes No
Thomas Vogt Poulsen 345 on at

Like (0)

Report

You could try calling some of these table methods to see if it helps:

- SkipAOSValidation(true)

- SkipDataMethods(true)

- SkipDatabaseLog(true)

- SkipDeleteAction(true)

- SkipDeleteMethods(true)

I cannot tell from your description which would right but it might be one these reasons that your statements are downgradede to record-by-record.

Regards

Thomas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The DynamicsAX1.ldf file you mentioned is created and managed by the SQL Server for your AX database, outside of the AOS, and is created when the DB is created - it is an inherent part of the system and is used to assure appropriate DR measures, used for backups, etc. The log settings are typically Full, Simple or Bulk-logged (set from the Recovery Model property of the database, using SSMS (usually) and cannot be changed from AX).

Database Logging in AX is separate from the LDF. While you may be able to limit the events kept in the database log as configured for Database Logging ( a table within the AX database) in AX, those same actions will be logged in the AX.LDF file.

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

Patrick is exactly right.

You can have some influence over how much data is pumped into the database LDF file by turning off features like Database log, thereby cutting the number of record inserts, and by using set based statements over massive numbers of individual statements, or by using truncate table instead of a delete statement.

But, everything you do to the database will still be logged into the LDF. Note that this is true even when the database is set to Simple recovery. If you create one large transaction and process millions of records, the LDF must absorb those changes, as it provides the rollback mechanism should the transaction abort or something fail (for example, the server crash). You can in this case control how much the LDF file grows by using smaller transactions (commit more frequently), since after each transaction the log file will checkpoint in Simple mode.

But, if you find yourself stuck in Full recovery mode, then the only way to truncate the LDF file is to backup the transaction log, i.e. incremental database backup. This will cause the transaction log to checkpoint, effectively making the entire LDF file free to re-use. It will not shrink the LDF file or make it smaller, that requires a shrink operation, but it does free up the space inside that file to start over. If you can intersperse BACKUP LOG statements in your process, it will prevent LDF growth.

Good luck!

Was this reply helpful? Yes No