Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Finding Source of Query Blocking

Posted on by Microsoft Employee

Started a custom AIF process this morning that updates a bit of data, and it was being blocked by something continuously running the query shown below.  The spid that was blocking also blocked reindexes on LedgerJournalTrans last night, so whatever it is doing it is taking forever.  The queries it is running finish in less than a second, so it's not just getting hung up due to indexing issues.  After killing the spid, I got the following in the event log showing that it was one of the workers running under the service account.

Object Server 01:  The database reported (session 50 (Admin)): [Microsoft][SQL Server Native Client 10.0]Communication link failure. The SQL statement was: "SELECT TOP 1 T1.RECVERSION,T1.PARTITION,T1.RECID FROM tempdb."DBO".t1052_832587F2C1354323A8EDE43628AD050E T1 WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND ((?=?) AND (?=?)))"

So how would one go about finding what this worker was doing that was locking LedgerJournalTrans for hours upon end?

Thanks!

 

(@P1 bigint,@P2 nvarchar(5),@P3 nvarchar(21),@P4 datetime2)SELECT TOP 1
T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,T1.C
ASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,T1.ENTERPRISENUMBER,T1.PRICE,T1.SETTLEVOUCHER,T1.POSTINGPROFILE,T1.VOU
CHER,T1.AMOUNTCURCREDIT,T1.PAYMENTSTATUS,T1.CASHDISCAMOUNT,T1.ACKNOWLEDGEMENTDATE,T1.VENDTRANSID,T1.CUSTTRANSID,T1.REMAI
NAMOUNT,T1.TAXCODE,T1.BANKDEPOSITVOUCHER,T1.PAYMREFERENCE,T1.BANKDEPOSITNUM,T1.PAYMSPEC,T1.PAYMMODE,T1.MARKEDINVOICE,T1.
OFFSETACCOUNTTYPE,T1.BANKTRANSTYPE,T1.OFFSETCOMPANY,T1.OFFSETTXT,T1.TAX1099AMOUNT,T1.PAYMENTNOTES,T1.MARKEDINVOICERECID,
T1.FOREIGNVOUCHER,T1.FOREIGNCOMPANY,T1.TRANSFERRED,T1.CANCEL,T1.NOEDIT,T1.INVISIBLE,T1.TRANSDATE,T1.TRANSACTIONTYPE,T1.D
OCUMENTDATE,T1.DOCUMENTNUM,T1.APPROVED,T1.PAYMID,T1.FREQVALUE,T1.FREQCODE,T1.DUE,T1.DATECASHDISC,T1.TRANSFERREDBY,T1.LOA
DINGDATE,T1.FILECREATED,T1.PAYMENTACCOUNT,T1.INVOICE,T1.TRANSFERREDTO,T1.TRANSFER,T1.LASTTRANSFERRED,T1.TRANSFERREDON,T1
.PURCHLEDGERPOSTING,T1.BANKCHEQUENUM,T1.PAYMENT,T1.CASHDISCCODE,T1.EXCHRATESECOND,T1.TRIANGULATION,T1.TAXITEMGROUP,T1.VA
TNUMJOURNAL,T1.FURTHERPOSTINGTYPE,T1.PURCHIDRANGE,T1.POOLRECID,T1.PREPAYMENT,T1.IMPORTDATE,T1.BANKCENTRALBANKPURPOSECODE
,T1.REASONREFRECID,T1.BANKCENTRALBANKPURPOSETEXT,T1.CUSTVENDBANKACCOUNTID,T1.PAYMENTSEQUENCENUM,T1.ERRORCODEPAYMENT,T1.F
URTHERPOSTINGRECID,T1.TAXDIRECTIONCONTROL,T1.CUSTVENDNEGINSTPROTESTREASON,T1.BANKREMITTANCETYPE,T1.BANKREMITTANCEFILEID,
T1.BANKACCOUNTID,T1.BANKPROMISSORYNOTENUM,T1.RELEASEDATECOMMENT,T1.INVOICERELEASEDATE,T1.INVOICERELEASEDATETZID,T1.BANKB
ILLOFEXCHANGENUM,T1.NEGINSTID,T1.TAXWITHHOLDGROUP,T1.TAX1099STATEAMOUNT,T1.TAX1099STATE,T1.REMITTEECURRENCY,T1.DEFAULTDI
MENSION,T1.OFFSETDEFAULTDIMENSION,T1.LEDGERDIMENSION,T1.REVERSEENTRY,T1.REVERSEDATE,T1.REVRECID,T1.OFFSETLEDGERDIMENSION
,T1.BANKRECONCILEACCOUNTATPOST,T1.BANKCHEQUEDEPOSITTRANSREFRECID,T1.MARKEDINVOICECOMPANY,T1.REMITTANCELOCATION,T1.REMITT
ANCEADDRESS,T1.RELEASEDATE,T1.RELEASEDATETZID,T1.CUSTEINVOICEPAYMDELIVERYNUM,T1.CUSTEINVOICEPAYMSECTIONNUM,T1.CUSTEINVOI
CEPAYMTRANSNUM,T1.PAYMINSTRUCTION1,T1.PAYMINSTRUCTION2,T1.PAYMINSTRUCTION3,T1.PAYMINSTRUCTION4,T1.VENDINVOICEDECLARATION
_IS,T1.TAX1099FIELDS,T1.GSTHSTTAXTYPE_CA,T1.VENDBANKACCOUNT,T1.CUSTBANKACCOUNT,T1.BUDGETSOURCELEDGERENTRYUNPOSTED,T1.OPE
RATIONTYPE_MX,T1.TAXWITHHOLDITEMGROUPHEADING_TH,T1.APPROVER,T1.LEDGERJOURNALDLVDATE_ES,T1.TAXWITHHOLDCALCULATE_TH,T1.TAX
WITHHOLDALTERNATEVENDORACCT_TH,T1.CUSTFACTUREAUTOCREATE_RU,T1.VATONPAYMENT_RU,T1.CORRECT_RU,T1.INTRACOMVATDUEDATE_W,T1.C
ORRECTEDINVOICEID_RU,T1.CORRECTEDINVOICEDATE_RU,T1.ATTACHMENTS_CN,T1.REFCOPYREVERSE_CN,T1.VOUCHER_CN,T1.VATPAYMREFRECID_
RU,T1.VATTAXAGENTVENDACCOUNT_RU,T1.VATOPERATIONCODE_RU,T1.ANNOUNCEMENTNUM_RU,T1.CASHRECEIPTNUM_LV,T1.PAYMENTPRIORITY_LT,
T1.FOREIGNBANKFEE_LT,T1.INFORMBYTELEX_LT,T1.INFORMBYPHONE_LT,T1.DEBITCURRENCYCODE_LT,T1.CUSTOMSTARIFFDIRECTION_IN,T1.ASS
ESSABLEVALUE_IN,T1.MAXIMUMRETAILPRICE_IN,T1.EXCISERECORDTYPE_IN,T1.GTASERVICECATEGORY_IN,T1.NONRECOVERABLEPERCENT_IN,T1.
VATGOODSTYPE_IN,T1.CONSIGNMENTNOTENUM_IN,T1.CUSTOMSDUTY_IN,T1.TDSGROUP_IN,T1.TCSGROUP_IN,T1.NATUREOFASSESSEE_IN,T1.COMPA
NYLOCATION_IN,T1.LEDGERVOUCHERTYPE_CN,T1.SALESTAXFORMTYPES_IN,T1.CUSTOMSTARIFFCODETABLE_IN,T1.EXCISETARIFFCODES_IN,T1.SE
RVICECODETABLE_IN,T1.EXCISETYPE_IN,T1.BANKCURRENCYTRANSFERLOG_RU,T1.BANKCENTRALBANKTRANSTYPECUR_RU,T1.BANKPRINTACCOUNTNU
M_RU,T1.OFFSESSIONID_RU,T1.RCASHDOCREPRESPERSONID,T1.OFFSETPOSTINGPROFILE_RU,T1.PAYMPURPOSE_LT,T1.INTBANK_LV,T1.RCASHDOC
REPRESTYPE,T1.TAXBASE_W,T1.RCASHPAYTRANSTYPE,T1.ADDRESSING_PL,T1.BANKCLIENTDOCUMENTTYPEID_RU,T1.BANKCOMMACCOUNTID_RU,T1.
BANKCURRENCY,T1.BANKCURRENCYAMOUNT,T1.BANKCURRENCYDEALCOURSE_RU,T1.BANKPAYMENTREGISTRATIONNUM_LV,T1.BANKRESPWORKERID_RU,
T1.BANKSPECACCOUNTID_RU,T1.BANKVENDACCOUNTID_RU,T1.CASHDISCBASEDATE,T1.CASHDISCBASEDAYS,T1.CORRECTIONTYPE_RU,T1.CUSTVEND
ACCOUNT_PL,T1.CUSTVENDNAME_PL,T1.TAXPERIODPAYMENTCODE_PL,T1.TAXSETOFFVOUCHER_IN,T1.TAXWITHHOLDCODE_IN,T1.TRANSFERCURRENC
Y_RU,T1.SKIPBLOCKEDFORMANUALENTRYCHECK,T1.SOFTWAREDECLRECEIVED_IN,T1.MCG_SENTBATCHID,T1.MCG_SENTTODATAMART,T1.MCG_SENTTO
DATAMARTTZID,T1.MCG_DATAMARTSUMMARYID,T1.MCG_UAID,T1.MCG_UAID_DT,T1.MCG_UAID_DTTZID,T1.MCG_FINDIM,T1.MCG_INTEGRATING,T1.
REPRESPERSONCARD_W,T1.REPRESPERSONNAME_W,T1.MCG_AJEBATCHID,T1.MCG_FOLIONUMBER,T1.MCG_PROGRAMNAME,T1.MODIFIEDDATETIME,T1.
RECVERSION,T1.PARTITION,T1.RECID FROM LEDGERJOURNALTRANS T1
WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND ((VOUCHER=@P3) AND (TRANSDATE=@P4)))

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Finding Source of Query Blocking

    Thanks for the reply.  It was blocking an update to LedgerJournalTrans.

    The SQL Server session information was telling me is that it was that spid blocking, and that query was showing up as the last query run by that spid.  So my only thought is that it started a transaction and never finished it, and then it was running this other query (over and over).  So that query may not be blocking it, but an open transaction by the spid that was running that query.  So I need to track down what code that worker was executing if that's possible, or set something up so that if it happens again I can trace it back to find what is causing it to lock the table for hours.  No batches running in that timeframe...

  • Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    RE: Finding Source of Query Blocking

    Are you sure it's this query? It basically says:

    select firstOnly LedgerJournalTrans where Voucher == {something}

    What resource did it lock?

    Nevertheless this can't take too much time anyway - the delay would be caused by something happening later in the same transaction, preventing the lock from being released.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,232 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans