YouTubeTwitterLinkedIn

AX 2012

Removing SSRS document archives

Throughout the years we have noticed that out SRSReport Document Type for Document attachments grew over the size of 90GB. This was getting filled by having the Print to document archive checkbox ticked in their saved Print settings. The generated printouts were just sitting in the archive without ever being actioned. We had to come up with a way to untick this setting for everyone and revoke the option to enable it again on the form. After that we could start removing SSRS document archives.

Did the following changes under \Classes\SysPrintOptions to only allow system administrator to set this flag:

private void init()
(...)
        storeInPrintArchive = Global::isSystemAdministrator() ? printJobSettings.storeInPrintArchive() : false;

public void new()
(...)
    showOutputPrintArchive  = Global::isSystemAdministrator() ? true : false;

public boolean parmStoreInPrintArchive(boolean _storeInPrintArchive = storeInPrintArchive)
(...)
    storeInPrintArchive = Global::isSystemAdministrator() ? _storeInPrintArchive : false;

Then the saved printer settings had to be changed in the SysLastValue table for all user accounts, without changing any other printer settings. They are stored differently based on whether they extend FormLetter classes, or not.

Please make sure you run and validate this in your Test environment first!

static void WIK_setPrintJobSettings(Args _args)
{
    SysLastValue                    sysLastValue;
    container                       lastValues;
    container                       con;
    ContainerClass                  containerClass;
    SRSPrintDestinationSettings     printSettings;
    PrintJobSettings                printJobSettings;
    Object                          object;
    Object                          contractObject;
 
    while select sysLastValue
            // Specific formletter reports
        where (sysLastValue.elementName == classStr(SalesFormLetter_Invoice)
            || sysLastValue.elementName == classStr(SalesFormLetter_PickingList)
            || sysLastValue.elementName == classStr(SalesFormLetter_Confirm)
            || sysLastValue.elementName == classStr(SalesFormLetter_FreeText)
            || sysLastValue.elementName == classStr(SalesFormLetter_PackingSlip)
            || sysLastValue.elementName == classStr(PurchFormLetter_Confirmation)
            || sysLastValue.elementName == classStr(PurchFormLetter_ConfirmationRequest)
            || sysLastValue.elementName == classStr(PurchFormLetter_Invoice)
            || sysLastValue.elementName == classStr(PurchFormLetter_PackingSlip)
            || sysLastValue.elementName == classStr(PurchFormLetter_PurchOrder)
            || sysLastValue.elementName == classStr(PurchFormLetter_ReceiptsList)
            // Specific SSRS reports via controller class
            || sysLastValue.elementName == classStr(AssetBalanceReportColumnsController)
            || sysLastValue.elementName == classStr(AssetReportsController)
            || sysLastValue.elementName == classStr(BankPaymAdviceChequeController)
            || sysLastValue.elementName == classStr(BankReconciliationController)
            || sysLastValue.elementName == classStr(BankReconciliationSummaryController)
            || sysLastValue.elementName == classStr(CustAccountStatementExtController)
            || sysLastValue.elementName == classStr(CustAgingReportController)
            || sysLastValue.elementName == classStr(CustInvoiceController)
            || sysLastValue.elementName == classStr(CustLedgerTransController)
            || sysLastValue.elementName == classStr(CustVendTransOpenPerDateController)
            || sysLastValue.elementName == classStr(HcmAccommodationListController)
            || sysLastValue.elementName == classStr(HcmCourseAttendeeStatListController)
            || sysLastValue.elementName == classStr(HcmCourseConfirmationController)
            || sysLastValue.elementName == classStr(HcmWorkersHiredInPeriodController)
            || sysLastValue.elementName == classStr(HRMEmployeeLeaveController)
            || sysLastValue.elementName == classStr(InventABCController)
            || sysLastValue.elementName == classStr(InventAgingController)
            || sysLastValue.elementName == classStr(InventCostReportController)
            || sysLastValue.elementName == classStr(InventJournalTransController)
            || sysLastValue.elementName == classStr(InventJournalTransTransferController)
            || sysLastValue.elementName == classStr(InventLedgerConflictController)
            || sysLastValue.elementName == classStr(InventPriceOverviewController)
            || sysLastValue.elementName == classStr(InventSettlementAdjustmentController)
            || sysLastValue.elementName == classStr(InventTableOverviewController)
            || sysLastValue.elementName == classStr(InventTransferOrdOverviewController)
            || sysLastValue.elementName == classStr(InventTransferShipReceiveController)
            || sysLastValue.elementName == classStr(InventValueReportController)
            || sysLastValue.elementName == classStr(JADRouteCashReconciliationController)
            || sysLastValue.elementName == classStr(LedgerAccountSchedController)
            || sysLastValue.elementName == classStr(LedgerJournalAcctMovementController)
            || sysLastValue.elementName == classStr(LedgerJournalCashController)
            || sysLastValue.elementName == classStr(LedgerJournalController)
            || sysLastValue.elementName == classStr(LedgerOpenTransactionsController)
            || sysLastValue.elementName == classStr(LedgerTotalAndBalanceListController)
            || sysLastValue.elementName == classStr(LedgerTransListAccountController)
            || sysLastValue.elementName == classStr(LedgerTransPerJournalController)
            || sysLastValue.elementName == classStr(LedgerTransStatementController)
            || sysLastValue.elementName == classStr(LedgerTrialBalanceController)
            || sysLastValue.elementName == classStr(PurchConfirmationRequestController)
            || sysLastValue.elementName == classStr(PurchFinalizeServiceController)
            || sysLastValue.elementName == classStr(PurchPackingSlipController)
            || sysLastValue.elementName == classStr(PurchPurchaseOrderController)
            || sysLastValue.elementName == classStr(PurchStatisticsController)
            || sysLastValue.elementName == classStr(SalesCODLabelController)
            || sysLastValue.elementName == classStr(SalesConfirmController)
            || sysLastValue.elementName == classStr(SalesHeadingController)
            || sysLastValue.elementName == classStr(SalesInvoiceController)
            || sysLastValue.elementName == classStr(SalesPackingSlipController)
            || sysLastValue.elementName == classStr(smmReportsController)
            || sysLastValue.elementName == classStr(SysInfoLogController)
            //|| sysLastValue.elementName == classStr(SysOperationQueryController)
            //|| sysLastValue.elementName == classStr(SysOperationServiceController)
            || sysLastValue.elementName == classStr(SysUsersOnlineController)
            || sysLastValue.elementName == classStr(TaxListController)
            || sysLastValue.elementName == classStr(TaxTransController)
            || sysLastValue.elementName == classStr(TaxTransDetailController)
            || sysLastValue.elementName == classStr(TaxWithholdController)
            || sysLastValue.elementName == classStr(VendAccountStatementIntController)
            || sysLastValue.elementName == classStr(VendAgingReportController)
            || sysLastValue.elementName == classStr(VendBalanceListController)
            || sysLastValue.elementName == classStr(VendInvoiceController)
            || sysLastValue.elementName == classStr(VendInvoiceDocumentController)
            || sysLastValue.elementName == classStr(VendRprtApproveCollectionController)
            || sysLastValue.elementName == classStr(WmsPickingList_OrderPickController)
            // Handle all SSRS-based reports as a generic type
            || sysLastValue.elementName == classStr(SrsReportRunController)
            ) && (
                sysLastValue.recordType == UtilElementType::Class
            )
    {
        if (xUserInfo::find(false, sysLastValue.userId).enable == NoYes::No)
        {
            continue;
        }
 
        lastValues = xSysLastValue::getValue(sysLastValue.company, sysLastValue.userId, sysLastValue.recordType, sysLastValue.elementName, sysLastValue.designName);
        printSettings = null;
        object = null;
 
        if (lastValues)
        {
            switch (sysLastValue.elementName)
            {
                // Formletter classes
                case classStr(PurchFormLetter_Confirmation) :
                    object = PurchFormLetter_Confirmation::newConfirmation();
                    break;
 
                case classStr(PurchFormLetter_ConfirmationRequest) :
                    object = PurchFormLetter_ConfirmationRequest::newConfirmationRequest();
                    break;
 
                case classStr(PurchFormLetter_Invoice) :
                case classStr(PurchFormLetter_PackingSlip) :
                case classStr(PurchFormLetter_PackingSlip) :
                case classStr(PurchFormLetter_PurchOrder) :
                case classStr(PurchFormLetter_ReceiptsList) :
                case classStr(SalesFormLetter_FreeText) :
                case classStr(SalesFormLetter_Confirm) :
                case classStr(SalesFormLetter_Invoice) :
                case classStr(SalesFormLetter_PickingList) :
                case classStr(SalesFormLetter_PackingSlip) :
                    object = classFactory.createClass(className2Id(sysLastValue.elementName));
                    break;
 
                // Controller classes
                case classStr(AssetBalanceReportColumnsController) :
                case classStr(AssetReportsController) :
                case classStr(BankPaymAdviceChequeController) :
                case classStr(BankReconciliationController) :
                case classStr(BankReconciliationSummaryController) :
                case classStr(CustAccountStatementExtController) :
                case classStr(CustAgingReportController) :
                case classStr(CustInvoiceController) :
                case classStr(CustLedgerTransController) :
                case classStr(CustVendTransOpenPerDateController) :
                case classStr(HcmAccommodationListController) :
                case classStr(HcmCourseAttendeeStatListController) :
                case classStr(HcmCourseConfirmationController) :
                case classStr(HcmWorkersHiredInPeriodController) :
                case classStr(HRMEmployeeLeaveController) :
                case classStr(InventABCController) :
                case classStr(InventAgingController) :
                case classStr(InventCostReportController) :
                case classStr(InventJournalTransController) :
                case classStr(InventJournalTransTransferController) :
                case classStr(InventLedgerConflictController) :
                case classStr(InventPriceOverviewController) :
                case classStr(InventSettlementAdjustmentController) :
                case classStr(InventTableOverviewController) :
                case classStr(InventTransferOrdOverviewController) :
                case classStr(InventTransferShipReceiveController) :
                case classStr(InventValueReportController) :
                case classStr(JADRouteCashReconciliationController) :
                case classStr(LedgerAccountSchedController) :
                case classStr(LedgerJournalAcctMovementController) :
                case classStr(LedgerJournalCashController) :
                case classStr(LedgerJournalController) :
                case classStr(LedgerOpenTransactionsController) :
                case classStr(LedgerTotalAndBalanceListController) :
                case classStr(LedgerTransListAccountController) :
                case classStr(LedgerTransPerJournalController) :
                case classStr(LedgerTransStatementController) :
                case classStr(LedgerTrialBalanceController) :
                case classStr(PurchConfirmationRequestController) :
                case classStr(PurchFinalizeServiceController) :
                case classStr(PurchPackingSlipController) :
                case classStr(PurchPurchaseOrderController) :
                case classStr(PurchStatisticsController) :
                case classStr(SalesCODLabelController) :
                case classStr(SalesConfirmController) :
                case classStr(SalesHeadingController) :
                case classStr(SalesInvoiceController) :
                case classStr(SalesPackingSlipController) :
                case classStr(smmReportsController) :
                case classStr(SysInfoLogController) :
                //case classStr(SysOperationQueryController) :
                //case classStr(SysOperationServiceController) :
                case classStr(SysUsersOnlineController) :
                case classStr(TaxListController) :
                case classStr(TaxTransController) :
                case classStr(TaxTransDetailController) :
                case classStr(TaxWithholdController) :
                case classStr(VendAccountStatementIntController) :
                case classStr(VendAgingReportController) :
                case classStr(VendBalanceListController) :
                case classStr(VendInvoiceController) :
                case classStr(VendInvoiceDocumentController) :
                case classStr(VendRprtApproveCollectionController) :
                case classStr(WmsPickingList_OrderPickController) :
                    object = classFactory.createClass(className2Id(sysLastValue.elementName));
                    // it might be packedSuper from SrsReportRunController, in which case we can only unpack value directly
                    if (conLen(lastValues) == 3)
                    {
                        lastValues = conPeek(lastValues, 3);
                    }
                    break;
 
                case classStr(SrsReportRunController) :
                    object = new SrsReportRunController();
                    break;
            }
 
            if (object == null)
            {
                continue;
            }
 
            try
            {
                object.unpack(lastValues);
            }
            catch
            {
                error(strFmt('%1 :: %2 :: %3 :: %4',
                    sysLastValue.elementName,
                    sysLastValue.designName,
                    sysLastValue.userId,
                    sysLastValue.company));
                continue;
            }
 
            if (SysDictClass::isEqualOrSuperclass(classIdGet(object), className2Id(classStr(FormLetterServiceController))))
            {
                printSettings = new SRSPrintDestinationSettings(object.printerSettingsFormletter());
            }
 
            if (SysDictClass::isEqualOrSuperclass(classIdGet(object), className2Id(classStr(SrsReportRunController))))
            {
                contractObject = object.parmReportContract();
                printSettings = contractObject.parmPrintSettings();
            }
 
            if (printSettings && printSettings.parmPrintToArchive() == true)
            {
                info(strFmt('Updating %1 :: %2 :: %3 :: %4 :: %5 :: %6 :: %7 ==> false',
                    sysLastValue.elementName,
                    sysLastValue.designName,
                    sysLastValue.userId,
                    sysLastValue.company,
                    printSettings.printMediumType(),
                    printSettings.printerName(),
                    printSettings.parmPrintToArchive()
                    ));
 
                printSettings.parmPrintToArchive(false);
 
                switch (sysLastValue.elementName)
                {
                    case classStr(PurchFormLetter_Confirmation) :
                    case classStr(PurchFormLetter_ConfirmationRequest) :
                    case classStr(PurchFormLetter_Invoice) :
                    case classStr(PurchFormLetter_PackingSlip) :
                    case classStr(PurchFormLetter_PackingSlip) :
                    case classStr(PurchFormLetter_PurchOrder) :
                    case classStr(PurchFormLetter_ReceiptsList) :
                    case classStr(SalesFormLetter_Confirm) :
                    case classStr(SalesFormLetter_Invoice) :
                    case classStr(SalesFormLetter_FreeText) :
                    case classStr(SalesFormLetter_PickingList) :
                    case classStr(SalesFormLetter_PackingSlip) :
                        object.updatePrinterSettingsFormLetter(printSettings.pack());
                        lastValues = object.pack();
                        break;
 
                    case classStr(AssetBalanceReportColumnsController) :
                    case classStr(AssetReportsController) :
                    case classStr(BankPaymAdviceChequeController) :
                    case classStr(BankReconciliationController) :
                    case classStr(BankReconciliationSummaryController) :
                    case classStr(CustAccountStatementExtController) :
                    case classStr(CustAgingReportController) :
                    case classStr(CustInvoiceController) :
                    case classStr(CustLedgerTransController) :
                    case classStr(CustVendTransOpenPerDateController) :
                    case classStr(HcmAccommodationListController) :
                    case classStr(HcmCourseAttendeeStatListController) :
                    case classStr(HcmCourseConfirmationController) :
                    case classStr(HcmWorkersHiredInPeriodController) :
                    case classStr(HRMEmployeeLeaveController) :
                    case classStr(InventABCController) :
                    case classStr(InventAgingController) :
                    case classStr(InventCostReportController) :
                    case classStr(InventJournalTransController) :
                    case classStr(InventJournalTransTransferController) :
                    case classStr(InventLedgerConflictController) :
                    case classStr(InventPriceOverviewController) :
                    case classStr(InventSettlementAdjustmentController) :
                    case classStr(InventTableOverviewController) :
                    case classStr(InventTransferOrdOverviewController) :
                    case classStr(InventTransferShipReceiveController) :
                    case classStr(InventValueReportController) :
                    case classStr(JADRouteCashReconciliationController) :
                    case classStr(LedgerAccountSchedController) :
                    case classStr(LedgerJournalAcctMovementController) :
                    case classStr(LedgerJournalCashController) :
                    case classStr(LedgerJournalController) :
                    case classStr(LedgerOpenTransactionsController) :
                    case classStr(LedgerTotalAndBalanceListController) :
                    case classStr(LedgerTransListAccountController) :
                    case classStr(LedgerTransPerJournalController) :
                    case classStr(LedgerTransStatementController) :
                    case classStr(LedgerTrialBalanceController) :
                    case classStr(PurchConfirmationRequestController) :
                    case classStr(PurchFinalizeServiceController) :
                    case classStr(PurchPackingSlipController) :
                    case classStr(PurchPurchaseOrderController) :
                    case classStr(PurchStatisticsController) :
                    case classStr(SalesCODLabelController) :
                    case classStr(SalesConfirmController) :
                    case classStr(SalesHeadingController) :
                    case classStr(SalesInvoiceController) :
                    case classStr(SalesPackingSlipController) :
                    case classStr(smmReportsController) :
                    case classStr(SysInfoLogController) :
                    //case classStr(SysOperationQueryController) :
                    //case classStr(SysOperationServiceController) :
                    case classStr(SysUsersOnlineController) :
                    case classStr(TaxListController) :
                    case classStr(TaxTransController) :
                    case classStr(TaxTransDetailController) :
                    case classStr(TaxWithholdController) :
                    case classStr(VendAccountStatementIntController) :
                    case classStr(VendAgingReportController) :
                    case classStr(VendBalanceListController) :
                    case classStr(VendInvoiceController) :
                    case classStr(VendInvoiceDocumentController) :
                    case classStr(VendRprtApproveCollectionController) :
                    case classStr(WmsPickingList_OrderPickController) :
                    case classStr(SrsReportRunController) :
                        lastValues = object.pack();
                        break;
                }
 
                xSysLastValue::putValue(lastValues, sysLastValue.company, sysLastValue.userId, sysLastValue.recordType, sysLastValue.elementName, sysLastValue.designName);
            }
        }
    }
 
    info('done');
}
    /*
    // View the settings on the printer setup form
    Args                            args;
    FormRun                         formRun;
    args = new Args()
    args.name(formStr(SRSPrintDestinationSettingsForm));
    args.caller(printSettings);
 
    formRun = classFactory::formRunClassOnClient(args);
    FormRun.init();
    formRun.run();
 
    if (!formRun.closed())
    {
        formRun.wait(true);
    }*/

By |2022-03-31T18:52:51+02:00March 31st, 2022|Categories: AX 2012|Tags: , , , |0 Comments
Read More

Finding objects marked for deletion

During our code upgrade we have identified that the SysDeletedObjects configuration keys were still turned on. Also many DEL_-prefixed fields were being referred or used in various places. We had to come up with a way for finding objects marked for deletion.

The documentation gives you an idea of what to do with such elements.

https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/delete-obsolete-application-objects

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/best-practices-tables

The following job can produce a CSV file, which contains everything with the DEL_ prefix, their table property and layer information:

static void findDelObjectsinAOTTree(Args _args)
{
    #Properties
    #AOT
    int row;
 
    xRefReferences xRefReferences;
    xRefPaths      xrefPath;
    xRefNames      xrefNames;
    str 4          strMatch = 'DEL_';
    str 100        configKey;
    TreeNode       treeNode;
    TreeNodeIterator        treeNodeIterator;
    TreeNodeTraverser       treeNodeTraverser;
    str 30                  layerObject;
 
    int             i;
    SetEnumerator   setEnumerator;
    Set             layers;
    UtilEntryLevel  layerEnum;
 
    #File
    CommaTextIo commaTextIo;
    FileIOPermission permission;
    CustTable custTable;
    str fileName = @"D:\Temp\delobjectsinAOT.csv";
 
    MenuFunction                    mf;
 
    void findNodes(TreeNode _treeNodeLoc, boolean traverse = false)
    {
        treeNode = TreeNode::findNode(_treeNodeLoc.treeNodePath());
 
        treeNodeIterator = treeNode.AOTiterator();
 
        if (traverse)
        {
            treeNodeTraverser    = new TreeNodeTraverser(TreeNode::findNode(_treeNodeLoc.treeNodePath()));
 
            while (treeNodeTraverser.next())
            {
 
                treeNode = treeNodeTraverser.currentNode();
 
                if (treeNode && findProperty(treeNode.AOTgetProperties(), #PropertyName))
                {
                    if (subStr(treeNode.AOTgetProperty(#PropertyName), 0 , strLen(strMatch)) == strMatch)
                    {
                        if (treeNode && treeNode.TreeNodeType().isLayerAware())
                        {
                            //layerObject     = treeNode.AOTLayers().toString();
                            layers = treeNode.AOTLayers();
                            setEnumerator = layers.getEnumerator();
                            layerObject = '';
                            while (setEnumerator.moveNext())
                            {
                                layerEnum = SetEnumerator.current();
                                layerObject += enum2str(layerEnum);
                            }
                            setEnumerator = null;
                        }
 
                        if (findProperty(treeNode.AOTgetProperties(), #PropertyConfigurationKey))
                        {
                            commaTextIo.write(treeNode.treeNodePath(), treeNode.AOTgetProperty(#PropertyConfigurationKey), layerObject) ;
                            layerObject = '';
                        }
                        else
                        {
                            if (treeNode.AOTObjectNode())
                            {
                                commaTextIo.write(treeNode.treeNodePath(), '', layerObject) ;
                                layerObject = '';
                            }
                        }
                    }
                }
            }
        }
        else
        {
            while (treeNode)
            {
                if (treeNode && findProperty(treeNode.AOTgetProperties(), #PropertyName))
                {
                    if (treeNode && treeNode.TreeNodeType().isLayerAware())
                    {
                        //layerObject     = treeNode.AOTLayers().toString();
                        layers = treeNode.AOTLayers();
                        setEnumerator = layers.getEnumerator();
                        layerObject = '';
                        while (setEnumerator.moveNext())
                        {
                            layerEnum = SetEnumerator.current();
                            layerObject += enum2str(layerEnum);
                        }
                        setEnumerator = null;
                    }
 
                    if (subStr(treeNode.AOTgetProperty(#PropertyName), 0 , strLen(strMatch)) == strMatch)
                    {
                        if (findProperty(treeNode.AOTgetProperties(), #PropertyConfigurationKey))
                        {
                            commaTextIo.write(treeNode.treeNodePath(), treeNode.AOTgetProperty(#PropertyConfigurationKey), layerObject) ;
                            layerObject = '';
                        }
                        else
                        {
                            if (treeNode.AOTObjectNode())
                            {
                                commaTextIo.write(treeNode.treeNodePath(), '', layerObject) ;
                                layerObject = '';
                            }
                        }
                    }
                }
 
                treeNode = treeNodeIterator.next();
            }
        }
 
        treeNode = null;
        treeNodeIterator = null;
        treeNodeTraverser = null;
    }
 
    if (!isRunningOnServer())
    {
        mf = ClassFactory::makeObjectOnServer(classNum(MenuFunction));
        mf.objectType(MenuItemObjectType::Job);
        mf.object(funcName());
        mf.runOn(ClassRunMode::Server);
        mf.run();
       
        return;
    }
    else
    {
        info('running code on server side');
    }
 
    permission = new FileIOPermission(fileName,#io_write);
    permission.assert();
    commaTextIo = new CommaTextIo(fileName,#io_write);
    commaTextIo.outFieldDelimiter('|');
 
    treeNode = TreeNode::findNode(#AOTRootPath);
 
    treeNodeIterator = treeNode.AOTiterator();
    TreeNode = treeNodeIterator.next();
 
    findNodes(TreeNode::findNode(#TablesPath), true);
    findNodes(TreeNode::findNode(#ExtendedDataTypesPath));
    findNodes(TreeNode::findNode(#BaseEnumsPath));
    findNodes(TreeNode::findNode(#ViewsPath), true);
    findNodes(TreeNode::findNode(#TableMapsPath), true);
    findNodes(TreeNode::findNode(#FormsPath));
    findNodes(TreeNode::findNode(#ClassesPath));
    findNodes(TreeNode::findNode(#MenusPath));
    findNodes(TreeNode::findNode(#MenuItemsDisplayPath));
    findNodes(TreeNode::findNode(#MenuItemsOutputPath));
    findNodes(TreeNode::findNode(#MenuItemsActionPath));
    findNodes(TreeNode::findNode(#SecPrivilegesPath));
 
    info('Export has finished');
 
}

The final results looks like this after executing the job for finding objects marked for deletion:

finding objects marked for deletion
By |2022-03-31T18:29:44+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: , , , , , , |0 Comments
Read More

Data histogram equalization

The Document Attachments were stored in our AX 2012 database, and were occupying several hundred GBs. As part of the migration to D365FO we had to export these files in bulk. The most efficient way is to run the batch task in parallel. But this data is tilted in a way that if you would group the tasks based on their creation date, it the first tasks would barely have any records to process while the last ones would go on forever. The solution for this is called data histogram equalization.

It would be a big challenge to code this in X++, but SQL Server has a function for doing this exactly: NTILE.

The following direct query is able to process the data histogram, and then return 10 buckets of Record Identifier ranges of roughly equal size:

WITH documents (Bucket, RecId)
AS (
       SELECT NTILE(10) OVER( ORDER BY DocuRef.RecId) AS Bucket
              ,DocuRef.RECID
    FROM docuRef
        INNER JOIN docuType
            ON  (docuType.dataAreaId = docuRef.RefCompanyId OR DOCUREF.REFCOMPANYID = '')
                AND docuType.TypeId = docuRef.TypeId
        INNER JOIN docuValue
            ON  docuValue.RecId          = docuRef.ValueRecId                
       WHERE  docuType.TypeGroup         = 1 -- DocuTypeGroup::File
        AND docuType.FilePlace           = 0 -- DocuFilePlace::Archive
        AND docuType.ArchivePath  <> ''
              AND docuValue.Path                = ''
       )
 
SELECT Bucket
       ,count(*) AS Count
       ,(SELECT MIN(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_From
       ,(SELECT MAX(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_To
       FROM documents
       GROUP BY Bucket

Here are the results for spawning 10 batch job tasks to do parallel execution based on the RecId surrogate key index, with ~57230 rows in each bucket. This allows you to evenly distribute the load for data processing in parallel.

data histogram equalization

If we would export our document attachments sequentially, it would take roughly 40 hours total. By utilizing the data histogram equalization we could get it down to be under 3 hours. That is amazing!

It is a great way to split ranges of data for parallel processing, to keep in mind for the future.

You can learn more about the functionality here:

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-ntile/

By |2022-03-31T16:42:48+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: , , , , , , |0 Comments
Read More

Out of control TempDB growth

Sometimes we have noticed that our SQL health report has shown an out of control TempDB growth. It has happened for example when we have triggered bulk invoiced sales order cleanup, or some other larger processes. You can find out more about monitoring TempDB growth for version store in these articles:

https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server

https://docs.microsoft.com/en-us/archive/blogs/sqlserverfaq/troubleshooting-tempdb-growth-due-to-version-store-usage

Running the following statement can tell you the currently occupied space overall, and per file in your TempDB. Our normal usage is around 2 GB, so having a 67GB space used looked very suspicious.

SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))) AS [Space Used in MB]
FROM sysfiles
 
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB]
FROM sysfiles
ORDER BY groupid DESC
Out of control TempDB growth

This query shows the composition of data within the TempDB files. It was immediately apparent that it is not the user/system data that is taking up the space, but the so called version store. Normally that should be regularly cleaned up by an internal process, unless there is a dead thread/blocking/something broken within SQL:

SELECT
--SUM(allocated_extent_page_count) AS [allocated extent pages used],
--(SUM(allocated_extent_page_count)*1.0/128) AS [allocated extent space in MB],
SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],
SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB],
SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB],
SUM(mixed_extent_page_count) AS [mixed object pages used],
(SUM(mixed_extent_page_count)*1.0/128) AS [mixed object space in MB]
FROM sys.dm_db_file_space_usage;
Version store pages

The following query has shown that session 224 was active for several days, with a sleeping state, thus preventing this TempDB cleanup on the version store to run.

DECLARE @runtime datetime
SET @runtime = GETDATE()
 
PRINT '-- sys.dm_db_file_space_used'
select CONVERT (varchar(30), @runtime, 121) AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
 
PRINT ' -- Output of active transactions which are using version store'
select CONVERT (varchar(30), @runtime, 121) AS runtime,a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,b.dbid,b.cpu,b.physical_io,b.memusage,b.login_time,b.last_batch,b.open_tran,b.status,b.hostname,b.program_name,b.cmd,b.loginame,request_id
from sys.dm_tran_active_snapshot_database_transactions a
inner join sys.sysprocesses b
on a.session_id = b.spid
 
PRINT ' -- Input buffer of SPIDs identified above Output of active transactions which are using version store'
select CONVERT (varchar(30), @runtime, 121) AS runtime,b.spid,c.*
from sys.dm_tran_active_snapshot_database_transactions a
inner join sys.sysprocesses b
on a.session_id = b.spid
cross apply sys.dm_exec_sql_text(sql_handle) c
 
PRINT ' -- Open cursors'
select * from sys.dm_exec_cursors(0) a
cross apply sys.dm_exec_sql_text(sql_handle)
WHERE DATEDIFF(hh, a.creation_time, GETDATE()) > 1;

The above Session 224 was running on one of our AX integration AOS instances. As a quick fix, restarting the AX AOS service has released the API sessions.

The root cause analysis has revealed that if we refer tables, which have their configuration keys turned off, it can generate TempDB entries. In our case the sales order deletion batch job was running SalesLineType::delete. It has a LedgerCov::deleteTransact call, which is a delete_from ledgerCov in a transaction block. We have introduced a configuration key check for the table, and that has eliminated the out of control TempDB growth issue.

Code compare for disabled configuration key checking

We highly recommend to have a close look at your TempDB usage, and try to identify which tables with a disabled configuration key can have a big performance impact on your environment by running code unnecessarily.

By |2022-03-31T14:20:26+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: , , , , , |0 Comments
Read More

Force executing batch jobs

There is an old, but good article posted by Tariq Bell about the inner workings of the AX Batch framework. When you are working with Dev and Test instances and enable/disable the Batch flag in Server configuration, it might take a while before your tasks are starting to run. There is a simple trick to force executing batch jobs.

As explained in the reference article, each batch task and the framework itself triggers this call, when they are ready to finish execution and a new task needs to be picked up. You could just write a simple job and trigger it with a Menu Item on Server side to kick off a batch job earlier.

static void Job1(Args _args)
{
    BatchRun::serverGetNextTask('01@YOURSERVERNAME');
}

Providing the correct instance number and the server name is required in order to force executing batch jobs.

We have used this method in part to change the batch framework itself, and allow execution of jobs faster than the typical 1 minute schedule. I wanted to post about those findings, but unfortunately that code is not compatible with more recent versions of AX 2012 R3 CU10+, due to Microsoft introducing the new SP_GETAPPLOCK calls in the framework.

By |2022-03-31T13:16:31+02:00March 31st, 2022|Categories: AX 2012, Microsoft Dynamics AX|Tags: , , , |0 Comments
Read More
This website uses cookies and third party services for embedding videos and content from YouTube, Facebook and Twitter. OK
Go to Top