RSS

How to find the space taken by each table in the data base in D365 through SQL Server

Hi people,

It’s been quite a bit of time that I wrote a post. So, let’s take a look at a quick one.

Recently, I came across importing a bacpac file from UT into a dev box and the DB after importing it is nearly around 300 GB. So, I wanted to know why the DB is taking some much of space. The below query will give us the information about how much space is consumed by each table and the rows in that table.

SELECT –top(1000)
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name;

Once I found what all the tables are taking huge amount of space, I have shared that information to the maintenance team to look at it and do their part. In my case, one particular Log table was taking 210GB of data.

Have a good day!

 
Leave a comment

Posted by on June 16, 2022 in AX, D365, SQL server

 

Tags: , , , , ,

How to export the DMF data project error records to excel file in D365

Hi Everyone,

Good morning.

How do you export the records which are giving you errors when you are importing data in Data management in D365?

It’s quiet simple, You need to use below link:

https://<xxxxx>devaos.sandbox.ax.dynamics.com/?mi=sysTableBrowser&TableName=DMFStagingValidationLog&cmp=USMF&limitednav=true&Ing=en-us

Please change above URL with your environment’s URL and it will open a table which contains all the error records from different execution. here, you need to use your execution ID to filter and get those records exported through standard excel export method.

Hope this helps.

Happy New Year.

 
Leave a comment

Posted by on December 30, 2019 in AX, D365, X++

 

Data entity method sequence in D365

Hi Folks,

Here is a brief link which will give you a basic data entity method sequence in D365.

https://community.dynamics.com/365/financeandoperations/f/765/t/301996

https://axatoz.wordpress.com/2019/03/10/d365-data-entity-sequence/

Happy Daxing!

 
Leave a comment

Posted by on April 23, 2019 in AX

 

Disable editing of specific financial dimensions on form

The form control “DimensionEntryControl” has a method called “parmEditableDimensionSet” which sets only a specific dimension set as editable. Thus, by excluding financial dimensions from the dimension set, it is possible to make specific dimension not editable.

First, a dimension set storage needs to be created. After that, each financial dimension will be added to the dimension set storage excluding the dimensions which should not be editable. At the end, the dimension set storage will be passed to the mentioned parm-method “parmEditableDimensionSet” which then only sets the dimensions in the dimension set storage as editable.

Below is an example which implements the code in the form event handler “OnInitialized” to make dimension not editable upon calling a form.

 

[FormEventHandler(formStr(SalesTable), FormEventType:Initialized)]
public static void SalesTable_OnInitialized(xFormRun _sender, FormEventArgs _e)
{
    DimensionEntryControl dimControl = _sender.design().controlName(identifierStr(DimensionEntryControlTable));
    DimensionEnumeration dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
    DimensionAttributeSetStorage dimensionAttributeSetStorage;
    DimensionAttribute dimensionAttribute;
    DimensionAttributeSetItem dimAttrSetItem;

    const str contractNumber = 'ContractNo';
    const str contractType = 'ContractType';

    dimensionAttributeSetStorage = new DimensionAttributeSetStorage();

    while select dimensionAttribute
        where dimensionAttribute.Name != contractNumber // Exclude specific dimension which should be not editable
            && dimensionAttribute.Name != contractType // Exclude specific dimension which should be not editable
        join dimAttrSetItem
            where dimAttrSetItem.DimensionAttribute == dimensionAttribute.RecId
                && dimAttrSetItem.DimensionAttributeSet == dimensionSetId
    {
        dimensionAttributeSetStorage.addItem(
            dimensionAttribute.RecId,
            dimensionAttribute.HashKey,
            NoYes::Yes);
    }

    dimControl.parmEditableDimensionSet(dimensionAttributeSetStorage.save());
}

As a result, the dimension fields “ContractNo” and “ContractType” are no longer editable on the sales order form.

findim

Happy daxing!

Courtesy: https://d365dev.com/2018/04/20/disable-editing-of-specific-financial-dimensions-on-form/

 

 

 

 
1 Comment

Posted by on February 20, 2019 in AX

 

Running runnable class through URL in Dynamics 365 for Operations

Hi Everyone ,

There are multiple ways you can run Runnable class. One way is we can attach it to Menu item and add it to any form and execute it using menu item.

Another way is set it up you project as startup object and runnable class as startup object in Visual Studio and run it from Visual studio.

Another way of running it is from your browser (IE/Chrome).

https://<D365URL>/?cmp=<YourCompanyName>&mi=SysClassRunner&cls=<YourRunnableClassName&gt;

Parameters:

Cmp = Your company name e.g INMF

Cls = Your Runnable class name e.g. generateseq

https://usnconeboxax1aos.cloud.onebox.dynamics.com/?cmp=INMF&mi=SysClassRunner&cls=generateseq

Whenever you want to quickly verify anything this way is very useful and you don’t have to spent time seeing while visual studio opens diagnostic tool and load other windows.

Happy weekend!

 
Leave a comment

Posted by on November 16, 2018 in AX

 

How to access only selected records of the Grid in D365

Hi Readers,

Here is a small and simple post which may be useful while you are trying to access the selected records in D365.

MultiSelectionHelper     selectionHelper = MultiSelectionHelper::construct();
MyTable                             myTable;
selectionHelper.parmDataSource(myTable_ds); //Set the datasource
myTable = selectionHelper.getFirst();
if (myTable.RecId)
{
        while (myTable)
        {
            info(strFmt(‘Selected record.. %1’,myTable.myField));//Display selected record
            myTable= selectionHelper.getNext();
        }
}
Happy Daxing!!!
 
Leave a comment

Posted by on August 3, 2018 in AX, D365

 

Tags: ,

How to override form data source field methods without overlaying in D365

Another useful look when you want to rewrite the logic of any standard method under data fields of data source in form. The below like has an approach which looks perfect!

https://ievgensaxblog.wordpress.com/2016/05/01/ax-7-how-to-override-form-data-source-field-methods-without-overlaying/

Hoping it helps someone.

 
Leave a comment

Posted by on July 25, 2018 in AX, D365

 

Tags:

Access the private or protected methods or data members in handlers in D365

Hi People,

Here is a very useful link when you try to access the private or protected methods or data members in handlers. It uses reflections.

https://ievgensaxblog.wordpress.com/2016/06/03/ax-7-accessing-privateprotected-class-methods-and-members-from-extension-code/

Good Day!!

 

 

 

 
Leave a comment

Posted by on July 25, 2018 in AX, D365

 

useful link for Events Understanding in D365FO

Hi Readers,

I came across the below link where events are explained in detail and it could help someone.

https://us.hitachi-solutions.com/knowledge-center/customize-dynamics-365-user-interface-extension-event-handler/

Happy Week ahead!!!

 
Leave a comment

Posted by on July 9, 2018 in AX

 

Creating a custom process flow in Advanced Warehouse Managament Mobile service in D365

Hi Readers,

I have come across a useful blog post which is very helpful in exploring advanced warehouse management from technical post of view. Here is the link.

https://blogs.msdn.microsoft.com/dynamicsaxscm/2017/07/06/customizing-the-warehousing-mobile-app/

Happy weekend!!!

 
Leave a comment

Posted by on June 3, 2018 in AX, D365

 

Tags: , ,

 
Azure Integration services for Dynamics 365

Azure Integration services for Dynamics 365 Unified Operations, Logic Apps, Azure Functions, REST

D365 FinOps

Technical and Functional Blog

Sunny's Blog on Dynamics AX

Exploring AX2012 and initiating knowledge sharing

Microsoft Dynamics 365 Blog

By Sandeep Chaudhury, Specialist in Microsoft Business Applications focused on Service and Project centric Organizations

D365FO/ AX Tips and Tricks

This blog is to help with AX issues

Moving on to D365 Finance and Operations

This blog is to help with AX issues

Dynamics AX Daily

This blog is to help with AX issues