Microsoft Dynamics AX (Archived)

Reclaim Space by Shrinking Database LOG & Deleting Transactions by using "SysDatabaseTransDelete" class on Test Server

(0) Share

Report

Posted on by Muneeb

895

Hi,

I am using AX 2012 R2 CU7.

I was running short of storage in "C:\" due to the increasing size of the logs all of a sudden. The size of the LDF file was around 32 GB and of MDF was around 42 GB.

Link: instructorbrandon.com/walkthrough-reclaim-space-and-shrink-the-transaction-logs-after-major-code-moves-in-dynamics-ax

1. After I followed the above blog, my LDF file reduced to to around 1 MB and the space in my "C:"\ was around 39 GB ( I even shrinked MDF file) (Shrinking was done through UI only)

2. I have 8 legal entities in my AX.

3. After step #2, I required more free space, so tried to delete the Transactions using "SysDatabaseTransDelete", for each 8 legal entities individually. In this process I realized that while deleting the Transactions, my LDF size grew again around 32 GB !!!

My query is, deletion of transactions using the the class "SysDatabaseTransDelete", why is it increasing the size of the LDF file so exponentially?

I was expecting more space to be available after Deleting these Transactions but it turned out that few MBs are left in my "C:\" after executing this "SysDatabaseTransDelete" class for each legal entity.

Can some one please help me understand what is happening behind the scene?

Would appreciate for some help.

Sincerely,

Muneeb

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Mohammad

When you delete records, SQL Server log everything on the transaction log file.

So you have to check that on the Log partition, disk free space is available.

After that, you have to stop AOSs, shrink the database files, both MDF and LDF

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

Report

You may also want to change your recovery mode, either permanently or just for this kind of changes, when you don't need to be able to do a point-in-time recovery.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

On SQL Server for Dynamics AX database, have you set the recovery model to “Simple” from SQL Server Management Studio (in Database properties > Options > Recovery model)?

What are the growth settings for Log files (Database Properties > Files), e.g. if auto-growth is set to a too large value in MB?

Have you run the Shrink File for SQL Server log to release the unused space, each time after using SysDatabaseTransDelete for one legal entity?

If log still grows too large for one entity, you can run Size of company accounts report, and see which table takes the most space and delete records directly on a SQL Server (take care that you use DataAreaId just for the legal entity that you want to delete).

The main question is - do you just want to free up some space on your disk, or do you really want to reset your legal entities and delete all transactional data? Using SysDatabaseTransDelete wipes transactions and worksheet tables in the current company and leaves only master data – is this what you want to do?

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

Yes, when you made huge data deletion.

Was this reply helpful? Yes No
Muneeb 895 on at

Like (0)

Report

Hi Jovan,

I just wanted to free up some space on my disk.

Sincerely,

Muneeb

Was this reply helpful? Yes No