You’re offline. This is a read only version of the page.
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
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...
Are you sure it's this query? It basically says:
Share
Report
All responses (
Answers (
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,232
Super User 2024 Season 2
#2
Martin Dráb
230,064
Most Valuable Professional
#3
nmaenpaa
101,156