web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Clearing AIF document ...
Microsoft Dynamics AX (Archived)

Clearing AIF document Logs in Dynamics AX 2009

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I have a client whose AX 2009 database is expanding out of control. Looking at top tables, the following AIF tables are taking up 90% of the space:

AIFDocumentLog

AIFResponse

AIFMEssageLog

What is the easiest and safest way to delete these logs?

I can provide more information about AIF setup if required.

If anyone has any insight please share.

Thanks!

Charu

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Umesh Pandit Profile Picture
    Umesh Pandit 9,315 User Group Leader on at

    Hi Charu,

    Yes we can delete these unwanted logs. Follow these

    www.kevinroos.be/.../cleaning-up-the-aif-document-log

  • Suggested answer
    Tommy Skaue Profile Picture
    Tommy Skaue Microsoft Employee on at

    Pay attention to the database log file while deleting. If your database is using the "Full" recovery mode, which  is recommended for point-in-time recovery, then these delete statements may cause the log to grow immensely if the tables contains a lot of rows (ie millions). It will log every deleted row in case it needs to roll back the operation.

    If your logs grows too much for your taste, you can opt for deleting directly on the database. Here is a sample of a query that will delete 50 000 rows for each submit, making sure the log doesn't grow beyond this:

    SELECT 1

    WHILE @@ROWCOUNT > 0

    BEGIN

      DELETE TOP (50000)

      FROM aifMessageLog

    END

    Just let it chew its way through the table, and when it is done replace the table name part with "aifDocumentLog" or "aifCorrelation".

  • Community Member Profile Picture
    Community Member on at

    Can records within AIFResponse be deleted? What is this table used for?

  • vesajuvonen Profile Picture
    vesajuvonen 10 on at

    Hi,

    Can you please explain your following answer and code:

    How that will prevent SQL Server full log from growing a lot,if, for

    example we are deleting 50 million rows?

    ==============

    If your logs grows too much for your taste, you can opt for deleting directly on the database. Here is a sample of a query that will delete 50 000 rows for each submit, making sure the log doesn't grow beyond this:

    SELECT 1

    WHILE @@ROWCOUNT > 0

    BEGIN

     DELETE TOP (50000)

     FROM aifMessageLog

    END

    Just let it chew its way through the table, and when it is done replace the table name part with "aifDocumentLog" or "aifCorrelation".

  • Suggested answer
    Tommy Skaue Profile Picture
    Tommy Skaue Microsoft Employee on at

    Hi

    You are right. My post is not complete here. The idea is to commit the delete in batches, because SQL Server will "back-up" the lines it is trying to delete in case the operation is rolled back for some reason. It might be that you would want to wrap the deletion with a begin and commit transaction, though if I remember correctly it worked without when I tried.

    If you need to wrap it, try this instead:

    SELECT 1

    WHILE @@ROWCOUNT > 0

    BEGIN

      BEGIN TRANSACTION

      DELETE TOP (50000)

         FROM AIFMESSAGELOG

      COMMIT

    END

    Just replace the tablename with the table you need to flush out. I'm sorry for not answering that part complete and correctly.

    Here are some pretty good posts explaining this further and you might find inspiring:

    alexandrebrisebois.wordpress.com/.../deleting-large-amounts-of-records-from-a-table-without-truncating-on-windows-azure-sql-database

    merill.net/.../deleting-millions-of-records-from-a-table-without-blowing-the-transaction-log

    wikidba.wordpress.com/.../sql-server-delete-millions-of-rows-without-hampering-the-transaction-log

  • xfmoralesy Profile Picture
    xfmoralesy on at

    HI,

    Just a question for you guys....

    Can't you just use the "TRUNCATE TABLE" SQL command to purge the contents of these tables, specially if I don't care about the contents of these?

    Why should I have to be cautious using this command?

    Best regards,

    Xavier

  • vesajuvonen Profile Picture
    vesajuvonen 10 on at

    Yes, if you are sure that you do not need any logging or transactions.

    TRUNCATE is very fast and efficient and I have used it for many other tables also.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Joris dG Profile Picture

Joris dG 5

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 2

#2
Henrik Nordlöf Profile Picture

Henrik Nordlöf 2 User Group Leader

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans