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