SQL stored procedures for querying the database background block

SQL stored procedures for querying the database background block

SETAnsi_nulls onGOSETQuoted_identifierOFFGO/*Logging of SQL Server blocking conditions Wang 2008.8.11 (@P1 nvarchar (8), @P2 nvarchar, @P3 int, @P4 nvarchar, @P5 int, @P6 int, @P7 nvarchar ( 8), @P8 int) SELECT TOP 1 a.itemid,a.statusissue,a.intercompanyinventdimtransfer3,a.datephysical,a.qty,a. Costamountposted,a.currencycode,a.transtype,a.transrefid,a.invoiceid,a.voucher,a.dateexpected,a.datefinancial, A.costamountphysical,a.inventtransid,a.statusreceipt,a.packingslipreturned,a.invoicereturned,a.packingslipid,a . Voucherphysical,a.costamountadjustment,a.shippingdaterequested,a.shippingdateconfirmed,a.qtysettled,a. Costamountsettled,a.valueopen,a.direction,a.datestatus,a.costamountstd,a.dateclosed,a.inventtransidfather,a. Costamountoperations,a.itemrouteid,a.itembomid,a.inventtransidreturn,a.inventdimid,a.inventdimfixed,a. Dateinvent,a.custvendac,a.transchildrefid,a.transchildtype,a.timeexpected,a.revenueamountphysical,a.assetid,a. Taxamountphysical,a.assetbookid,a.inventreftransid,a.modifieddate,a.modifiedtime,a.modifiedby,a.createddate,a. Createdtime,a.createDby,a.recversion,a.recid from InventTrans A WHERE ([email protected]) and ((([email protected]) an D ([email protected])) and ([email protected])) and ([email protected]) OR ([email protected] ))) and EXISTS (SELECT TOP 1 ' x ' from InventDim b,inventlocation C WHERE (B.inventdimid=a.inventdimid) and ([EMA Il protected]) and ((C.inventlocationid=b.inventlocationid) and ([email protected])))--kill 282 2008.9.6 Add new rows to the Blocked data, blocked_xxx these fields as obsolete exec axreport. [dbo]. [up_log_serverblocked] 1,1--kill 394 exec [dbo]. [Up_log_serversql]         ' AXlivep1 ' select top object_name (Rsc_objid), * from Sys.syslockinfo where req_spid= ' "the" SELECT * FROM sys (@P1 ntext, @P2 ntext, @P3 ntext, @P4 ntext, @P5 ntext, @P6 nvarchar, @P7 nvarchar (122), @P8 nvarchar (a), @P9 int, @P1 0 int, @P11 int, @P12 int, @P13 int, @P14 datetime, @P15 Numeric (28,12), @P16 Numeric (28,12), @P17 datetime, @P18 datetime, @P19 DatetiMe, @P20 datetime, @P21 datetime, @P22 Numeric (28,12), @P23 Numeric (28,12), @P24 Numeric (28,12), @P25 Numeric (28,12), @P26 Numeric (28,12), @P27 datetime, @P28 int, @P29 int, @P30 nvarchar, @P31 nvarchar, @P32 nvarchar (22 ), @P34 nvarchar, @P35 nvarchar, @P36 nvarchar, @P37 nvarchar, @P38 nvarchar (+), @P39 int, @P40 nvarchar (42 ), @P41 nvarchar, @P42 int, @P43 nvarchar, @P44 datetime, @P45 int, @P46 nvarchar, @P47 int, @P48 datetime, @P49 int , @P50 int, @P51 nvarchar, @P52 numeric (28,12), @P53 nvarchar, @P54 nvarchar, @P55 nvarchar (+), @P56 nvarchar ( @P57 int, @P58 int, @P59 datetime, @P60 nvarchar, @P61 nvarchar, @P62 int, @P63 int, @P64 int, @P65 int, @P66 nvarchar, @P67 int, @P68 nvarchar (+), @P69 int, @P70 nvarchar, @P71 int, @P72 numeric (28,12), @P73 int, @P74 datetime , @P75 nvarchar, @P76 datetime, @P77 int, @P78 datetime, @P79 datetime, @P80 Numeric (28,12), @P81 Numeric (28,12), @P82 Numeric (28,12), @P83 Numeric (28,12), @P84 nvarchar (+), @P85 numeric (28,@P86 Numeric (28,12), @P87 nvarchar, @P88 numeric (28,12), @P89 Numeric (28,12), @P90 Numeric (28,12), @P91 Numeric ( 28,12), @P92 Numeric (28,12), @P93 Numeric (28,12), @P94 Numeric (28,12), @P95 nvarchar, @P96 int, @P97 int, @P98 datetime, @P99 datetime, @P100 int, @P101 numeric (28,12), @P102 Numeric (28,12), @P103 int, @P104 datetime, @P105 int, @P106 int, @P107 nvarchar, @P108 datetime, @P109 int, @P110 int, @P111 int, @P112 datetime, @P113 int, @P114 int, @P115 nvarchar, @P116 Numeric (28,12), @P117 nvarchar, @P118 nvarchar, @P119 nvarchar, @P120 datetime, @P121 int, @P122 int, @P123 int, @P124 int, @P125 numeric (28,12), @P126 Numeric (28,12), @P127 int, @P128 nvarchar (+), @P129 datetime, @P130 int, @P131 nvarchar, @P132 datetime, @P133 int, @P134 nvarchar, @P135 nvarchar (8), @P136 int, @P137 bigint) INSERT into Prodtabl E (Sml_remark,sml_prodstoppedremark,sml_prodbomremark,sml_proddelayremark,sml_vendremarks,itemid,name, Prodgroupid,prodstatus,prodprio,prodlocked,prodtype,schedstatus,scheddate,qtyscHed,qtystup,dlvdate,stupdate,finisheddate,schedstart,schedend,height,width,depth,density,qtycalc,realdate, Reservation,prodpostingtype,inventtransid,dimension,dimension2_,dimension3_,dimension4_,dimension5_,dimension6 _,dimension7_,dimension8_,inventreftype,inventrefid,inventreftransid,collectreflevel,collectrefprodid,bomdate, Backorderstatus,prodpoolid,profitset,calcdate,routejobs,checkroute,propertyid,remaininventphysical,bomid, Routeid,reqplanidsched,reqpoid,reflookup,latestscheddirection,latestscheddate,prodid,inventdimid,schedtotime, Schedfromtime,latestschedtime,dlvtime,prodorigid,ganttcolorid,packageqty,sml_prodstopped,sml_refreplaceprodid, Sml_prodtablestatustrack,sml_prodtolerance,sml_prodbomapproved,sml_origdlvdate,sml_prodbomapprover,sml_ Prodbomapprovaldate,sml_prodbomapprovaltime,sml_proddlvdate,sml_produrgentorderdate,sml_produrgentorderqty,sml _platepcsrow,sml_platepcscol,sml_platepcsadd,sml_pattno,sml_lrows_ss,sml_ribbons,sml_proddemand,sml_ Platesizewidth,sml_platesizeheight,smL_prodtotalhrs,sml_prodplatecalcprodqty,sml_prodplatecalcprintrun,sml_prodplatecalcplatecnt,sml_ Prodplatecalcnoofcopies,sml_refreplaceparentprodid,sml_prodplatecalculated,sml_pnum,sml_ Prodoutsourcingreplydate,sml_urgentorderapprovaldate,sml_trademode,sml_prodplatecalcprintrunscrap,sml_ Prodplatecalcprintrun30042,sml_toelabel,sml_ordercreateddate,sml_prodledty,sml_produrgentordertype,sml_ Pattcode,sml_reffinisheddate,sml_counted,sml_incleplatformpdf,sml_isurgentorder,sml_maindlvdate,sml_ Isreplaceprodorder,sml_isprodsysraf,sml_vendaccount,sml_overrafqty,sml_prodmethodid,sml_classno,wrkctrid,sml_ Mainfinisheddate,sml_mainfinishedtime,sml_ppctrl_test,sml_ppctrl_qc,sml_ppctrl_process,sml_oilcontent,sml_     Weightofbobbin,sml_retentiongoodslevel,sml_packingremarks,mo*/ALTER  Proc [dbo].[up_log_serverblocked]  @LogSelfBlocked bit=0 ,  @ShowOnly bit =0,  @All bit =0 as    SetNocount on   Declare @Log_Date datetime,@Count int   Set @Log_Date=GetDate()   SelectSpid,blocked=( Case  whenspid=Blocked Then 0 ElseBlockedEnd),        db_name(dbid) asDbname,hostname,loginame, Program_name,Convert(nvarchar(4000),"') asSpid_sql,Convert(nvarchar(4000),"') asLockedobject,--Convert (nvarchar (4000), ') as Blocked_sql,        --Convert (nvarchar, ') as blocked_loginame,convert (nvarchar (), ') as Blocked_hostname,        --Convert (nvarchar (), ') as Blocked_program_name,Waittime,cpu,physical_io, memusage,login_time,last_batch,open_tran,status,waittype,waitresource,dbid,cm D into#Temp_ServerBlocked fromSys.sysprocesses with(NOLOCK)where @All=1 orspidinch        (Selectspid fromSys.sysprocesses with(NOLOCK)whereBlocked>0         Union         SelectBlocked fromSys.sysprocesses with(NOLOCK)whereBlocked>0       )   If @LogSelfBlocked=0       Delete  from#Temp_ServerBlockedwhereBlocked=0   andspid not inch(SelectBlocked from#Temp_ServerBlocked)Select @Count=Count(1) from#Temp_ServerBlockedIf @Count >0 Begin      Declare @spid int,@Blocked int,@Spid_SQL nvarchar(4000),@LockedObject nvarchar(4000)            --, @Blocked_SQL nvarchar (4000), @Blocked_Loginame nvarchar (+),            --@Blocked_hostname nvarchar (+), @Blocked_program_name nvarchar (+)      DeclareCcccursorLocal Fast_forward for          Select distinctSpid,blocked from#Temp_ServerBlockedOpenCCCFetchCcc into @spid,@blocked       while @ @fetch_status=0 begin         --User SQL         --Select @Blocked_Loginame = ', @Blocked_hostname = ', @Blocked_program_name = '         --Select @Blocked_Loginame =loginame, @Blocked_hostname =hostname, @Blocked_program_name =program_name         --From Master. sysprocesses with (nolock) where [email protected]         --Set @Blocked_SQL = ' '         ExecDbo. Up_getspid_sql@spid,@Spid_SQLOutput@LockedObjectOutput--If (@spid <> @Blocked) and @Blocked <>0         --Exec dbo. Up_getspid_sql @Blocked, @Blocked_SQL output         Update#Temp_ServerBlockedSetSpid_sql=@Spid_SQL, Lockedobject=@LockedObject                    --, [email protected]_sql,[email protected]_loginame,                    --[email protected]_hostname,[email protected]_program_name            wherespid=@spid  andBlocked=@blocked         FetchCcc into @spid,@blocked     End     CloseCCCdeallocateCCCSelect *  from#Temp_ServerBlockedDrop Table#Temp_ServerBlockedEnd

View Code

After querying to Eg:kill 992 with kill SpId, you can clear block.

SQL stored procedures for querying the database background block