SQL Server Performance

Will there be a Service Pack 4 for SQL Server 2005?

Discussion in 'Getting Started' started by lcerni, Jul 29, 2009.

  1. lcerni New Member

    Will there be a Service Pack 4 for SQL Server 2005? I have tried surfing the web but I don't see any mention of one.
  2. moh_hassan20 New Member

    No news for SP4
    what problems you suffer from sp3 ?
    you can find accumulative update for sp3 which resolve some issues in sp3 at: http://support.microsoft.com/?Kbid=970279
  3. FrankKalis Moderator

    There are a couple of cumulative updates out for SP 3. Do you have any issues?
  4. FrankKalis Moderator

    [quote user="moh_hassan20"]
    No news for SP4
    what problems you suffer from sp3 ?
    you can find accumulative update for sp3 which resolve some issues in sp3 at: http://support.microsoft.com/?Kbid=970279
    [/quote]
    Doh, should have read your post before replying... [:)]
  5. moh_hassan20 New Member

    welcome Frank
    Good to insure my post[:D]
  6. lcerni New Member

    Currently working on 64 bit server, SQL Server 2005 SP3.
    I did install http://support.microsoft.com/kb/959195/LN/ hoping that this would address our issue. But it did not. Below are 6 examples of Deadlocks Encountered. Any thoughts?
    --Deadlock 1
    2009-07-17 14:36:07.36 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 14:36:07.36 spid4s Wait-for graph
    2009-07-17 14:36:07.36 spid4s
    2009-07-17 14:36:07.36 spid4s Node:1
    2009-07-17 14:36:07.36 spid4s KEY: 7:72057595280621568 (cf02671b4569) CleanCnt:3 Mode:S Flags: 0x0
    2009-07-17 14:36:07.36 spid4s Grant List 3:
    2009-07-17 14:36:07.36 spid4s Owner:0x00000001AA6EEF80 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:70 ECID:0 XactLockInfo: 0x0000000080035890
    2009-07-17 14:36:07.36 spid4s SPID: 70 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 14:36:07.36 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 14:36:07.36 spid4s Requested By:
    2009-07-17 14:36:07.36 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000207FAE700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x000000020664A598) Value:0x9ceb54c0 Cost:(0/1280)
    2009-07-17 14:36:07.36 spid4s
    2009-07-17 14:36:07.36 spid4s Node:2
    2009-07-17 14:36:07.36 spid4s KEY: 7:72057595282915328 (1300a5fb2a15) CleanCnt:2 Mode:X Flags: 0x0
    2009-07-17 14:36:07.36 spid4s Grant List 0:
    2009-07-17 14:36:07.36 spid4s Owner:0x0000000080271100 Mode: X Flg:0x0 Ref:1 Life:02000001 SPID:77 ECID:0 XactLockInfo: 0x0000000207FAE738
    2009-07-17 14:36:07.36 spid4s SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 265
    2009-07-17 14:36:07.36 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 831394081]
    2009-07-17 14:36:07.36 spid4s Requested By:
    2009-07-17 14:36:07.36 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000080035858 Mode: S SPID:70 BatchID:0 ECID:0 TaskProxy:(0x000000020CD9A598) Value:0x99f8d480 Cost:(0/0)
    2009-07-17 14:36:07.37 spid4s
    2009-07-17 14:36:07.37 spid4s Victim Resource Owner:
    2009-07-17 14:36:07.37 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000080035858 Mode: S SPID:70 BatchID:0 ECID:0 TaskProxy:(0x000000020CD9A598) Value:0x99f8d480 Cost:(0/0)
    2009-07-17 14:36:07.37 spid18s deadlock-list
    2009-07-17 14:36:07.37 spid18s deadlock victim=process5969c18
    2009-07-17 14:36:07.37 spid18s process-list
    2009-07-17 14:36:07.37 spid18s process id=processbae478 taskpriority=0 logused=1280 waitresource=KEY: 7:72057595280621568 (cf02671b4569) waittime=4562 ownerId=45521033 transactionname=user_transaction lasttranstarted=2009-07-17T14:36:02.767 XDES=0x207fae700 lockMode=X schedulerid=1 kpid=3300 status=suspended spid=77 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T14:36:02.770 lastbatchcompleted=2009-07-17T14:36:02.767 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=45521033 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    2009-07-17 14:36:07.37 spid18s executionStack
    2009-07-17 14:36:07.37 spid18s frame procname=InsidEdge.dbo.usp_codeoff_save line=265 stmtstart=20730 stmtend=23408 sqlhandle=0x0300070021118e310abc2601469c00000100000000000000
    2009-07-17 14:36:07.37 spid18s UPDATE order_labor SET time_in = M.time_in,
    2009-07-17 14:36:07.37 spid18s time_out = M.time_out,
    2009-07-17 14:36:07.37 spid18s status_cd = 'C',
    2009-07-17 14:36:07.37 spid18s mileage = M.mileage,
    2009-07-17 14:36:07.37 spid18s emp_consultant_ind = M.emp_consultant_ind,
    2009-07-17 14:36:07.37 spid18s extended_price = M.extended_price,
    2009-07-17 14:36:07.37 spid18s emp_zone = M.emp_zone,
    2009-07-17 14:36:07.37 spid18s --Modified By SPisal 06/08/09 Site local time
    2009-07-17 14:36:07.37 spid18s completed_dt = CASE
    2009-07-17 14:36:07.37 spid18s WHEN order_labor.completed_dt IS NULL THEN @site_local_time
    2009-07-17 14:36:07.37 spid18s ELSE order_labor.completed_dt END
    2009-07-17 14:36:07.37 spid18s ,
    2009-07-17 14:36:07.37 spid18s last_update_dt = @date_now,
    2009-07-17 14:36:07.37 spid18s last_update_userid = M.last_update_userid,
    2009-07-17 14:36:07.37 spid18s completed_by=M.last_update_userid,
    2009-07-17 14:36:07.37 spid18s codeoff_comp_ind=M.codeoff_comp_ind,
    2009-07-17 14:36:07.37 spid18s gl_account_num_rev=[dbo].[f_get_gl_account_code] ('R','OLS',M.order_num,0,0,M.service_type_cd,''),
    2009-07-17 14:36:07.37 spid18s billing_appr_ind=M.billing_appr_ind,
    2009-07-17 14:36:07.37 spid18s --Modified By Sachin Pisal 06/22/09
    2009-07-17 14:36:07.37 spid18s call_out_ind=case
    2009-07-17 14:36:07.37 spid18s WHEN M.call_out_ind IS NOT NULL THEN M.call_out_ind
    2009-07-17 14:36:07.37 spid18s ELSE order_labor.call_out_ind end
    2009-07-17 14:36:07.37 spid18s FROM @tempM M ,@tempO O
    2009-07-17 14:36:07.37 spid18s WHERE order_labor.ols_tkt_num = M.ols_tkt_num
    2009-07-17 14:36:07.37 spid18s AND :confused:ls_tkt_num
    2009-07-17 14:36:07.37 spid18s inputbuf
    2009-07-17 14:36:07.37 spid18s Proc [Database Id = 7 Object Id = 831394081]
    2009-07-17 14:36:07.37 spid18s process id=process5969c18 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (1300a5fb2a15) waittime=4359 ownerId=45520773 transactionname=INSERT lasttranstarted=2009-07-17T14:36:01.750 XDES=0x80035858 lockMode=S schedulerid=8 kpid=668 status=suspended spid=70 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T14:36:00.510 lastbatchcompleted=2009-07-17T14:36:00.503 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=5688 loginname=sched_360_user isolationlevel=read committed (2) xactid=45520773 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 14:36:07.37 spid18s executionStack
    2009-07-17 14:36:07.37 spid18s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 14:36:07.37 spid18s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 14:36:07.37 spid18s --PULL INSTALLS
    2009-07-17 14:36:07.37 spid18s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 14:36:07.37 spid18s CASE
    2009-07-17 14:36:07.37 spid18s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 14:36:07.37 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 14:36:07.37 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 14:36:07.37 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 14:36:07.37 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 14:36:07.37 spid18s END As tzone,
    2009-07-17 14:36:07.37 spid18s -- --###################################################
    2009-07-17 14:36:07.37 spid18s -- --Need to check the logic on how dst is followed.
    2009-07-17 14:36:07.37 spid18s -- CASE
    2009-07-17 14:36:07.37 spid18s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 14:36:07.37 spid18s frame procname=InsidEdge.dbo.f_360_get_prerequisites line=54 stmtstart=4308 stmtend=5146 sqlhandle=0x03000700462f216fbc9de600f29b00000000000000000000
    2009-07-17 14:36:07.37 spid18s DELETE FROM @tbl_prerequisites
    2009-07-17 14:36:07.37 spid18s WHERE
    2009-07-17 14:36:07.37 spid18s -- (ols_tkt_num2 NOT IN(SELECT ols_tkt_num FROM @tickets)
    2009-07-17 14:36:07.37 spid18s -- (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(@from_dt, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 14:36:07.37 spid18s (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 14:36:07.37 spid18s OR
    2009-07-17 14:36:07.37 spid18s ols_tkt_num1 IS NULL)
    2009-07-17 14:36:07.37 spid18s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=99 stmtstart=7686 stmtend=8162 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 14:36:07.37 spid18s INSERT INTO @prerequisites(ols_tkt_num1, ols_tkt_num2)
    2009-07-17 14:36:07.37 spid18s SELECT ols_tkt_num1, ols_tkt_num2
    2009-07-17 14:36:07.37 spid18s FROM f_360_get_prerequisites(@xml_ticket_list)
    2009-07-17 14:36:07.37 spid18s --UPDATE SNAPSHOT TABLES - sched_360_in_xxx
    2009-07-17 14:36:07.37 spid18s --sched_360_in_activity
    2009-07-17 14:36:07.37 spid18s inputbuf
    2009-07-17 14:36:07.37 spid18s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 14:36:07.37 spid18s resource-list
    2009-07-17 14:36:07.37 spid18s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lock152cd6c00 mode=S associatedObjectId=72057595280621568
    2009-07-17 14:36:07.37 spid18s owner-list
    2009-07-17 14:36:07.37 spid18s owner id=process5969c18 mode=S
    2009-07-17 14:36:07.37 spid18s waiter-list
    2009-07-17 14:36:07.37 spid18s waiter id=processbae478 mode=X requestType=wait
    2009-07-17 14:36:07.37 spid18s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lock1531f1f00 mode=X associatedObjectId=72057595282915328
    2009-07-17 14:36:07.37 spid18s owner-list
    2009-07-17 14:36:07.37 spid18s owner id=processbae478 mode=X
    2009-07-17 14:36:07.37 spid18s waiter-list
    2009-07-17 14:36:07.37 spid18s waiter id=process5969c18 mode=S requestType=wait

    --Deadlock 2
    2009-07-17 15:55:04.98 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 15:55:04.98 spid4s Wait-for graph
    2009-07-17 15:55:04.98 spid4s
    2009-07-17 15:55:04.98 spid4s Node:1
    2009-07-17 15:55:04.98 spid4s KEY: 7:72057595280621568 (71024e305bb4) CleanCnt:3 Mode:S Flags: 0x0
    2009-07-17 15:55:04.98 spid4s Grant List 3:
    2009-07-17 15:55:04.98 spid4s Owner:0x000000025BB63400 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:75 ECID:0 XactLockInfo: 0x000000022E92D8B0
    2009-07-17 15:55:04.98 spid4s SPID: 75 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 15:55:04.98 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 15:55:04.98 spid4s Requested By:
    2009-07-17 15:55:04.98 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000021E4A2370 Mode: X SPID:70 BatchID:0 ECID:0 TaskProxy:(0x000000022B66A598) Value:0xe5bd2340 Cost:(0/316)
    2009-07-17 15:55:04.98 spid4s
    2009-07-17 15:55:04.98 spid4s Node:2
    2009-07-17 15:55:04.98 spid4s KEY: 7:72057595282915328 (5700701f9a84) CleanCnt:2 Mode:X Flags: 0x0
    2009-07-17 15:55:04.98 spid4s Grant List 0:
    2009-07-17 15:55:04.98 spid4s Owner:0x00000003C4AB0500 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:70 ECID:0 XactLockInfo: 0x000000021E4A23A8
    2009-07-17 15:55:04.98 spid4s SPID: 70 ECID: 0 Statement Type: UPDATE Line #: 50
    2009-07-17 15:55:04.98 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 15:55:04.98 spid4s Requested By:
    2009-07-17 15:55:04.98 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000022E92D878 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x000000022F2EE598) Value:0x54709d40 Cost:(0/0)
    2009-07-17 15:55:04.98 spid4s
    2009-07-17 15:55:04.98 spid4s Victim Resource Owner:
    2009-07-17 15:55:04.98 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000022E92D878 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x000000022F2EE598) Value:0x54709d40 Cost:(0/0)
    2009-07-17 15:55:04.98 spid18s deadlock-list
    2009-07-17 15:55:04.98 spid18s deadlock victim=process5953eb8
    2009-07-17 15:55:04.98 spid18s process-list
    2009-07-17 15:55:04.98 spid18s process id=processbae478 taskpriority=0 logused=316 waitresource=KEY: 7:72057595280621568 (71024e305bb4) waittime=3656 ownerId=45796423 transactionname=user_transaction lasttranstarted=2009-07-17T15:55:01.333 XDES=0x21e4a2370 lockMode=X schedulerid=1 kpid=3300 status=suspended spid=70 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T15:55:01.333 lastbatchcompleted=2009-07-17T15:55:01.333 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=45796423 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 15:55:04.98 spid18s executionStack
    2009-07-17 15:55:04.98 spid18s frame procname=InsidEdge.dbo.usp_routing_upd line=50 stmtstart=4278 stmtend=9656 sqlhandle=0x03000700a03c9e46379d1e01259c00000100000000000000
    2009-07-17 15:55:04.98 spid18s UPDATE order_labor SET
    2009-07-17 15:55:04.98 spid18s emp_id = CASE
    2009-07-17 15:55:04.98 spid18s WHEN @emp_id > 0 THEN @emp_id
    2009-07-17 15:55:04.98 spid18s ELSE NULL END,
    2009-07-17 15:55:04.98 spid18s escalation_ind = @escalation_ind,
    2009-07-17 15:55:04.98 spid18s scheduled_dt = CASE
    2009-07-17 15:55:04.98 spid18s WHEN @emp_id > 0 AND @scheduled_dt > '1/1/1900' THEN @scheduled_dt -- PShah 6/2/09 added schedule_dt > 1/1/1900 condition
    2009-07-17 15:55:04.98 spid18s ELSE NULL END,
    2009-07-17 15:55:04.98 spid18s confirmed_apptmt_ind = @confirmed_apptmt_ind,
    2009-07-17 15:55:04.98 spid18s due_dt = CASE
    2009-07-17 15:55:04.98 spid18s WHEN isnull(@due_time, '') = '' THEN order_labor.due_dt -- keep as is
    2009-07-17 15:55:04.98 spid18s WHEN @scheduled_dt > '1/1/1900'
    2009-07-17 15:55:04.98 spid18s THEN convert(char(10), @scheduled_dt, 101) + ' ' + @due_time -- @sched_dt + @due_time
    2009-07-17 15:55:04.98 spid18s ELSE convert(char(10), order_labor.due_dt, 101) + ' ' + @due_time -- old due_dt + @due_time
    2009-07-17 15:55:04.98 spid18s END,
    2009-07-17 15:55:04.98 spid18s mobile_status_ind = @mobile_status_ind,
    2009-07-17 15:55:04.98 spid18s mobile_status_time = @mobile_status_time,
    2009-07-17 15:55:04.98 spid18s status_cd = /*
    2009-07-17 15:55:04.98 spid18s CASE
    2009-07-17 15:55:04.98 spid18s WHEN order_labor.status_cd = 'H' AND isnull(@due_time, '') = '' THEN 'H'
    2009-07-17 15:55:04.98 spid18s WHEN @scheduled_dt > '1/1/1900' AND @emp_id > 0 THEN 'S'
    2009-07-17 15:55:04.98 spid18s ELSE
    2009-07-17 15:55:04.98 spid18s inputbuf
    2009-07-17 15:55:04.98 spid18s Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 15:55:04.98 spid18s process id=process5953eb8 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (5700701f9a84) waittime=3484 ownerId=45796166 transactionname=INSERT lasttranstarted=2009-07-17T15:55:00.263 XDES=0x22e92d878 lockMode=S schedulerid=7 kpid=3932 status=suspended spid=75 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T15:55:00.263 lastbatchcompleted=2009-07-17T15:55:00.260 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=5404 loginname=sched_360_user isolationlevel=read committed (2) xactid=45796166 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 15:55:04.98 spid18s executionStack
    2009-07-17 15:55:04.98 spid18s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 15:55:04.98 spid18s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 15:55:04.98 spid18s --PULL INSTALLS
    2009-07-17 15:55:04.98 spid18s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 15:55:04.98 spid18s CASE
    2009-07-17 15:55:04.98 spid18s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 15:55:04.98 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 15:55:04.98 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 15:55:04.98 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 15:55:04.98 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 15:55:04.98 spid18s END As tzone,
    2009-07-17 15:55:04.98 spid18s -- --###################################################
    2009-07-17 15:55:04.98 spid18s -- --Need to check the logic on how dst is followed.
    2009-07-17 15:55:04.98 spid18s -- CASE
    2009-07-17 15:55:04.98 spid18s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 15:55:04.98 spid18s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=78 stmtstart=6302 stmtend=7020 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 15:55:04.98 spid18s INSERT INTO @jobs_distinct(
    2009-07-17 15:55:04.98 spid18s order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 15:55:04.98 spid18s SELECT f.order_num, f.ols_tkt_num, f.site_id, f.tzone, f.job_value, f.response, f.service_type
    2009-07-17 15:55:04.98 spid18s FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt) f, @tickets t
    2009-07-17 15:55:04.98 spid18s WHERE f.ols_tkt_num = t.ols_tkt_num
    2009-07-17 15:55:04.98 spid18s --PULL PREREQUISITES
    2009-07-17 15:55:04.98 spid18s inputbuf
    2009-07-17 15:55:04.98 spid18s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 15:55:04.98 spid18s resource-list
    2009-07-17 15:55:04.98 spid18s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lock104a02b00 mode=S associatedObjectId=72057595280621568
    2009-07-17 15:55:04.98 spid18s owner-list
    2009-07-17 15:55:04.98 spid18s owner id=process5953eb8 mode=S
    2009-07-17 15:55:04.98 spid18s waiter-list
    2009-07-17 15:55:04.98 spid18s waiter id=processbae478 mode=X requestType=wait
    2009-07-17 15:55:04.98 spid18s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lock154040400 mode=X associatedObjectId=72057595282915328
    2009-07-17 15:55:04.98 spid18s owner-list
    2009-07-17 15:55:04.98 spid18s owner id=processbae478 mode=X
    2009-07-17 15:55:04.98 spid18s waiter-list
    2009-07-17 15:55:04.98 spid18s waiter id=process5953eb8 mode=S requestType=wait
    --Deadlock 3

    2009-07-17 16:20:04.35 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 16:20:04.35 spid4s Wait-for graph
    2009-07-17 16:20:04.35 spid4s
    2009-07-17 16:20:04.35 spid4s Node:1
    2009-07-17 16:20:04.35 spid4s KEY: 7:72057595280621568 (6403857be96c) CleanCnt:3 Mode:S Flags: 0x0
    2009-07-17 16:20:04.35 spid4s Grant List 3:
    2009-07-17 16:20:04.35 spid4s Owner:0x00000003C7E11AC0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:79 ECID:0 XactLockInfo: 0x000000008042B310
    2009-07-17 16:20:04.35 spid4s SPID: 79 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 16:20:04.35 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 16:20:04.35 spid4s Requested By:
    2009-07-17 16:20:04.35 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000804D1450 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy:(0x0000000417AE8598) Value:0xa9ecf1c0 Cost:(0/360)
    2009-07-17 16:20:04.35 spid4s
    2009-07-17 16:20:04.35 spid4s Node:2
    2009-07-17 16:20:04.35 spid4s KEY: 7:72057595282915328 (6900e2906e94) CleanCnt:2 Mode:X Flags: 0x0
    2009-07-17 16:20:04.35 spid4s Grant List 2:
    2009-07-17 16:20:04.35 spid4s Owner:0x0000000099F8C400 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x00000000804D1488
    2009-07-17 16:20:04.35 spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 50
    2009-07-17 16:20:04.35 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 16:20:04.35 spid4s Requested By:
    2009-07-17 16:20:04.35 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000008042B2D8 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x00000001AD8A2598) Value:0xc77ac480 Cost:(0/0)
    2009-07-17 16:20:04.35 spid4s
    2009-07-17 16:20:04.35 spid4s Victim Resource Owner:
    2009-07-17 16:20:04.35 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000008042B2D8 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x00000001AD8A2598) Value:0xc77ac480 Cost:(0/0)
    2009-07-17 16:20:04.35 spid14s deadlock-list
    2009-07-17 16:20:04.35 spid14s deadlock victim=process5953eb8
    2009-07-17 16:20:04.35 spid14s process-list
    2009-07-17 16:20:04.35 spid14s process id=process513d978 taskpriority=0 logused=360 waitresource=KEY: 7:72057595280621568 (6403857be96c) waittime=1765 ownerId=45891572 transactionname=user_transaction lasttranstarted=2009-07-17T16:20:02.583 XDES=0x804d1450 lockMode=X schedulerid=6 kpid=2864 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T16:20:02.587 lastbatchcompleted=2009-07-17T16:20:02.583 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=45891572 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 16:20:04.35 spid14s executionStack
    2009-07-17 16:20:04.35 spid14s frame procname=InsidEdge.dbo.usp_routing_upd line=50 stmtstart=4278 stmtend=9656 sqlhandle=0x03000700a03c9e46379d1e01259c00000100000000000000
    2009-07-17 16:20:04.35 spid14s UPDATE order_labor SET
    2009-07-17 16:20:04.35 spid14s emp_id = CASE
    2009-07-17 16:20:04.35 spid14s WHEN @emp_id > 0 THEN @emp_id
    2009-07-17 16:20:04.35 spid14s ELSE NULL END,
    2009-07-17 16:20:04.35 spid14s escalation_ind = @escalation_ind,
    2009-07-17 16:20:04.35 spid14s scheduled_dt = CASE
    2009-07-17 16:20:04.35 spid14s WHEN @emp_id > 0 AND @scheduled_dt > '1/1/1900' THEN @scheduled_dt -- PShah 6/2/09 added schedule_dt > 1/1/1900 condition
    2009-07-17 16:20:04.35 spid14s ELSE NULL END,
    2009-07-17 16:20:04.35 spid14s confirmed_apptmt_ind = @confirmed_apptmt_ind,
    2009-07-17 16:20:04.35 spid14s due_dt = CASE
    2009-07-17 16:20:04.35 spid14s WHEN isnull(@due_time, '') = '' THEN order_labor.due_dt -- keep as is
    2009-07-17 16:20:04.35 spid14s WHEN @scheduled_dt > '1/1/1900'
    2009-07-17 16:20:04.35 spid14s THEN convert(char(10), @scheduled_dt, 101) + ' ' + @due_time -- @sched_dt + @due_time
    2009-07-17 16:20:04.35 spid14s ELSE convert(char(10), order_labor.due_dt, 101) + ' ' + @due_time -- old due_dt + @due_time
    2009-07-17 16:20:04.35 spid14s END,
    2009-07-17 16:20:04.35 spid14s mobile_status_ind = @mobile_status_ind,
    2009-07-17 16:20:04.35 spid14s mobile_status_time = @mobile_status_time,
    2009-07-17 16:20:04.35 spid14s status_cd = /*
    2009-07-17 16:20:04.35 spid14s CASE
    2009-07-17 16:20:04.35 spid14s WHEN order_labor.status_cd = 'H' AND isnull(@due_time, '') = '' THEN 'H'
    2009-07-17 16:20:04.35 spid14s WHEN @scheduled_dt > '1/1/1900' AND @emp_id > 0 THEN 'S'
    2009-07-17 16:20:04.35 spid14s ELSE
    2009-07-17 16:20:04.35 spid14s inputbuf
    2009-07-17 16:20:04.35 spid14s Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 16:20:04.35 spid14s process id=process5953eb8 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (6900e2906e94) waittime=1593 ownerId=45891349 transactionname=INSERT lasttranstarted=2009-07-17T16:20:01.943 XDES=0x8042b2d8 lockMode=S schedulerid=7 kpid=3932 status=suspended spid=79 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T16:20:00.220 lastbatchcompleted=2009-07-17T16:20:00.217 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=5764 loginname=sched_360_user isolationlevel=read committed (2) xactid=45891349 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 16:20:04.35 spid14s executionStack
    2009-07-17 16:20:04.35 spid14s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 16:20:04.35 spid14s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 16:20:04.35 spid14s --PULL INSTALLS
    2009-07-17 16:20:04.35 spid14s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 16:20:04.35 spid14s CASE
    2009-07-17 16:20:04.35 spid14s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 16:20:04.35 spid14s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 16:20:04.35 spid14s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 16:20:04.35 spid14s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 16:20:04.35 spid14s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 16:20:04.35 spid14s END As tzone,
    2009-07-17 16:20:04.35 spid14s -- --###################################################
    2009-07-17 16:20:04.35 spid14s -- --Need to check the logic on how dst is followed.
    2009-07-17 16:20:04.35 spid14s -- CASE
    2009-07-17 16:20:04.35 spid14s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 16:20:04.35 spid14s frame procname=InsidEdge.dbo.f_360_get_prerequisites line=54 stmtstart=4308 stmtend=5146 sqlhandle=0x03000700462f216fbc9de600f29b00000000000000000000
    2009-07-17 16:20:04.35 spid14s DELETE FROM @tbl_prerequisites
    2009-07-17 16:20:04.35 spid14s WHERE
    2009-07-17 16:20:04.35 spid14s -- (ols_tkt_num2 NOT IN(SELECT ols_tkt_num FROM @tickets)
    2009-07-17 16:20:04.35 spid14s -- (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(@from_dt, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 16:20:04.35 spid14s (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 16:20:04.35 spid14s OR
    2009-07-17 16:20:04.35 spid14s ols_tkt_num1 IS NULL)
    2009-07-17 16:20:04.35 spid14s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=99 stmtstart=7686 stmtend=8162 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 16:20:04.35 spid14s INSERT INTO @prerequisites(ols_tkt_num1, ols_tkt_num2)
    2009-07-17 16:20:04.35 spid14s SELECT ols_tkt_num1, ols_tkt_num2
    2009-07-17 16:20:04.35 spid14s FROM f_360_get_prerequisites(@xml_ticket_list)
    2009-07-17 16:20:04.35 spid14s --UPDATE SNAPSHOT TABLES - sched_360_in_xxx
    2009-07-17 16:20:04.35 spid14s --sched_360_in_activity
    2009-07-17 16:20:04.35 spid14s inputbuf
    2009-07-17 16:20:04.35 spid14s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 16:20:04.35 spid14s resource-list
    2009-07-17 16:20:04.35 spid14s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lock801d1580 mode=X associatedObjectId=72057595282915328
    2009-07-17 16:20:04.35 spid14s owner-list
    2009-07-17 16:20:04.35 spid14s owner id=process513d978 mode=X
    2009-07-17 16:20:04.35 spid14s waiter-list
    2009-07-17 16:20:04.35 spid14s waiter id=process5953eb8 mode=S requestType=wait
    2009-07-17 16:20:04.35 spid14s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lock3c505a400 mode=S associatedObjectId=72057595280621568
    2009-07-17 16:20:04.35 spid14s owner-list
    2009-07-17 16:20:04.35 spid14s owner id=process5953eb8 mode=S
    2009-07-17 16:20:04.35 spid14s waiter-list
    2009-07-17 16:20:04.35 spid14s waiter id=process513d978 mode=X requestType=wait
    --Deadlock 4
    2009-07-17 17:14:01.93 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 17:14:01.93 spid4s Wait-for graph
    2009-07-17 17:14:01.93 spid4s
    2009-07-17 17:14:01.93 spid4s Node:1
    2009-07-17 17:14:01.93 spid4s KEY: 7:72057595280621568 (1d037d85cebc) CleanCnt:3 Mode:S Flags: 0x0
    2009-07-17 17:14:01.93 spid4s Grant List 3:
    2009-07-17 17:14:01.93 spid4s Owner:0x00000001547DF3C0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x00000003FD853D00
    2009-07-17 17:14:01.93 spid4s SPID: 63 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 17:14:01.93 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 17:14:01.93 spid4s Requested By:
    2009-07-17 17:14:01.93 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000021E4A26C0 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy:(0x0000000417AE8598) Value:0x8018e140 Cost:(0/316)
    2009-07-17 17:14:01.93 spid4s
    2009-07-17 17:14:01.93 spid4s Node:2
    2009-07-17 17:14:01.93 spid4s KEY: 7:72057595282915328 (5a00adc7f076) CleanCnt:2 Mode:X Flags: 0x0
    2009-07-17 17:14:01.93 spid4s Grant List 0:
    2009-07-17 17:14:01.93 spid4s Owner:0x000000008025E900 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x000000021E4A26F8
    2009-07-17 17:14:01.93 spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 50
    2009-07-17 17:14:01.93 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 17:14:01.93 spid4s Requested By:
    2009-07-17 17:14:01.93 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000003FD853CC8 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x0000000268452598) Value:0x4427740 Cost:(0/0)
    2009-07-17 17:14:01.93 spid4s
    2009-07-17 17:14:01.93 spid4s Victim Resource Owner:
    2009-07-17 17:14:01.93 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000003FD853CC8 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x0000000268452598) Value:0x4427740 Cost:(0/0)
    2009-07-17 17:14:01.93 spid18s deadlock-list
    2009-07-17 17:14:01.93 spid18s deadlock victim=process5968c58
    2009-07-17 17:14:01.93 spid18s process-list
    2009-07-17 17:14:01.93 spid18s process id=processbafac8 taskpriority=0 logused=316 waitresource=KEY: 7:72057595280621568 (1d037d85cebc) waittime=1109 ownerId=46124503 transactionname=user_transaction lasttranstarted=2009-07-17T17:14:00.827 XDES=0x21e4a26c0 lockMode=X schedulerid=1 kpid=5580 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T17:14:00.827 lastbatchcompleted=2009-07-17T17:14:00.827 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=46124503 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 17:14:01.93 spid18s executionStack
    2009-07-17 17:14:01.93 spid18s frame procname=InsidEdge.dbo.usp_routing_upd line=50 stmtstart=4278 stmtend=9656 sqlhandle=0x03000700a03c9e46379d1e01259c00000100000000000000
    2009-07-17 17:14:01.93 spid18s UPDATE order_labor SET
    2009-07-17 17:14:01.93 spid18s emp_id = CASE
    2009-07-17 17:14:01.93 spid18s WHEN @emp_id > 0 THEN @emp_id
    2009-07-17 17:14:01.93 spid18s ELSE NULL END,
    2009-07-17 17:14:01.93 spid18s escalation_ind = @escalation_ind,
    2009-07-17 17:14:01.93 spid18s scheduled_dt = CASE
    2009-07-17 17:14:01.93 spid18s WHEN @emp_id > 0 AND @scheduled_dt > '1/1/1900' THEN @scheduled_dt -- PShah 6/2/09 added schedule_dt > 1/1/1900 condition
    2009-07-17 17:14:01.93 spid18s ELSE NULL END,
    2009-07-17 17:14:01.93 spid18s confirmed_apptmt_ind = @confirmed_apptmt_ind,
    2009-07-17 17:14:01.93 spid18s due_dt = CASE
    2009-07-17 17:14:01.93 spid18s WHEN isnull(@due_time, '') = '' THEN order_labor.due_dt -- keep as is
    2009-07-17 17:14:01.93 spid18s WHEN @scheduled_dt > '1/1/1900'
    2009-07-17 17:14:01.93 spid18s THEN convert(char(10), @scheduled_dt, 101) + ' ' + @due_time -- @sched_dt + @due_time
    2009-07-17 17:14:01.93 spid18s ELSE convert(char(10), order_labor.due_dt, 101) + ' ' + @due_time -- old due_dt + @due_time
    2009-07-17 17:14:01.93 spid18s END,
    2009-07-17 17:14:01.93 spid18s mobile_status_ind = @mobile_status_ind,
    2009-07-17 17:14:01.93 spid18s mobile_status_time = @mobile_status_time,
    2009-07-17 17:14:01.93 spid18s status_cd = /*
    2009-07-17 17:14:01.93 spid18s CASE
    2009-07-17 17:14:01.93 spid18s WHEN order_labor.status_cd = 'H' AND isnull(@due_time, '') = '' THEN 'H'
    2009-07-17 17:14:01.93 spid18s WHEN @scheduled_dt > '1/1/1900' AND @emp_id > 0 THEN 'S'
    2009-07-17 17:14:01.93 spid18s ELSE
    2009-07-17 17:14:01.93 spid18s inputbuf
    2009-07-17 17:14:01.93 spid18s Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 17:14:01.93 spid18s process id=process5968c58 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (5a00adc7f076) waittime=718 ownerId=46124354 transactionname=INSERT lasttranstarted=2009-07-17T17:14:00.383 XDES=0x3fd853cc8 lockMode=S schedulerid=8 kpid=1996 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T17:14:00.380 lastbatchcompleted=2009-07-17T17:14:00.377 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=4080 loginname=sched_360_user isolationlevel=read committed (2) xactid=46124354 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 17:14:01.93 spid18s executionStack
    2009-07-17 17:14:01.93 spid18s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 17:14:01.93 spid18s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 17:14:01.93 spid18s --PULL INSTALLS
    2009-07-17 17:14:01.93 spid18s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 17:14:01.93 spid18s CASE
    2009-07-17 17:14:01.93 spid18s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 17:14:01.93 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 17:14:01.93 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 17:14:01.93 spid18s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 17:14:01.93 spid18s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 17:14:01.93 spid18s END As tzone,
    2009-07-17 17:14:01.93 spid18s -- --###################################################
    2009-07-17 17:14:01.93 spid18s -- --Need to check the logic on how dst is followed.
    2009-07-17 17:14:01.93 spid18s -- CASE
    2009-07-17 17:14:01.93 spid18s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 17:14:01.93 spid18s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=78 stmtstart=6302 stmtend=7020 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 17:14:01.93 spid18s INSERT INTO @jobs_distinct(
    2009-07-17 17:14:01.93 spid18s order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 17:14:01.93 spid18s SELECT f.order_num, f.ols_tkt_num, f.site_id, f.tzone, f.job_value, f.response, f.service_type
    2009-07-17 17:14:01.93 spid18s FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt) f, @tickets t
    2009-07-17 17:14:01.93 spid18s WHERE f.ols_tkt_num = t.ols_tkt_num
    2009-07-17 17:14:01.93 spid18s --PULL PREREQUISITES
    2009-07-17 17:14:01.93 spid18s inputbuf
    2009-07-17 17:14:01.93 spid18s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 17:14:01.93 spid18s resource-list
    2009-07-17 17:14:01.93 spid18s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lockbdadeb00 mode=X associatedObjectId=72057595282915328
    2009-07-17 17:14:01.93 spid18s owner-list
    2009-07-17 17:14:01.93 spid18s owner id=processbafac8 mode=X
    2009-07-17 17:14:01.93 spid18s waiter-list
    2009-07-17 17:14:01.93 spid18s waiter id=process5968c58 mode=S requestType=wait
    2009-07-17 17:14:01.93 spid18s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lock196a3bb00 mode=S associatedObjectId=72057595280621568
    2009-07-17 17:14:01.93 spid18s owner-list
    2009-07-17 17:14:01.93 spid18s owner id=process5968c58 mode=S
    2009-07-17 17:14:01.93 spid18s waiter-list
    2009-07-17 17:14:01.93 spid18s waiter id=processbafac8 mode=X requestType=wait
    --Deadlock 5
    2009-07-17 17:18:01.63 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 17:18:01.63 spid4s Wait-for graph
    2009-07-17 17:18:01.63 spid4s
    2009-07-17 17:18:01.63 spid4s Node:1
    2009-07-17 17:18:01.63 spid4s KEY: 7:72057595282915328 (9100aaaebcd7) CleanCnt:3 Mode:X Flags: 0x0
    2009-07-17 17:18:01.63 spid4s Grant List 3:
    2009-07-17 17:18:01.63 spid4s Owner:0x0000000151BEDA00 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x0000000255C13898
    2009-07-17 17:18:01.63 spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 50
    2009-07-17 17:18:01.63 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 17:18:01.63 spid4s Requested By:
    2009-07-17 17:18:01.63 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000096385C48 Mode: S SPID:71 BatchID:0 ECID:0 TaskProxy:(0x00000002656A2598) Value:0xad45f940 Cost:(0/0)
    2009-07-17 17:18:01.63 spid4s
    2009-07-17 17:18:01.63 spid4s Node:2
    2009-07-17 17:18:01.63 spid4s KEY: 7:72057595280621568 (0f03c6e319eb) CleanCnt:2 Mode:S Flags: 0x0
    2009-07-17 17:18:01.63 spid4s Grant List 1:
    2009-07-17 17:18:01.63 spid4s Owner:0x000000008018FB00 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:71 ECID:0 XactLockInfo: 0x0000000096385C80
    2009-07-17 17:18:01.63 spid4s SPID: 71 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 17:18:01.63 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 17:18:01.63 spid4s Requested By:
    2009-07-17 17:18:01.63 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000255C13860 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy:(0x0000000417AE8598) Value:0x5bb4dc80 Cost:(0/360)
    2009-07-17 17:18:01.63 spid4s
    2009-07-17 17:18:01.63 spid4s Victim Resource Owner:
    2009-07-17 17:18:01.63 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000096385C48 Mode: S SPID:71 BatchID:0 ECID:0 TaskProxy:(0x00000002656A2598) Value:0xad45f940 Cost:(0/0)
    2009-07-17 17:18:01.63 spid17s deadlock-list
    2009-07-17 17:18:01.63 spid17s deadlock victim=process4d112e8
    2009-07-17 17:18:01.63 spid17s process-list
    2009-07-17 17:18:01.63 spid17s process id=process4d112e8 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (9100aaaebcd7) ownerId=46134313 transactionname=INSERT lasttranstarted=2009-07-17T17:18:00.407 XDES=0x96385c48 lockMode=S schedulerid=4 kpid=3616 status=suspended spid=71 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T17:18:00.407 lastbatchcompleted=2009-07-17T17:18:00.403 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=3588 loginname=sched_360_user isolationlevel=read committed (2) xactid=46134313 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 17:18:01.63 spid17s executionStack
    2009-07-17 17:18:01.63 spid17s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 17:18:01.63 spid17s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 17:18:01.63 spid17s --PULL INSTALLS
    2009-07-17 17:18:01.63 spid17s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 17:18:01.63 spid17s CASE
    2009-07-17 17:18:01.63 spid17s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 17:18:01.63 spid17s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 17:18:01.63 spid17s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 17:18:01.63 spid17s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 17:18:01.63 spid17s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 17:18:01.63 spid17s END As tzone,
    2009-07-17 17:18:01.63 spid17s -- --###################################################
    2009-07-17 17:18:01.63 spid17s -- --Need to check the logic on how dst is followed.
    2009-07-17 17:18:01.63 spid17s -- CASE
    2009-07-17 17:18:01.63 spid17s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 17:18:01.63 spid17s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=78 stmtstart=6302 stmtend=7020 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 17:18:01.63 spid17s INSERT INTO @jobs_distinct(
    2009-07-17 17:18:01.63 spid17s order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 17:18:01.63 spid17s SELECT f.order_num, f.ols_tkt_num, f.site_id, f.tzone, f.job_value, f.response, f.service_type
    2009-07-17 17:18:01.63 spid17s FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt) f, @tickets t
    2009-07-17 17:18:01.63 spid17s WHERE f.ols_tkt_num = t.ols_tkt_num
    2009-07-17 17:18:01.63 spid17s --PULL PREREQUISITES
    2009-07-17 17:18:01.63 spid17s inputbuf
    2009-07-17 17:18:01.63 spid17s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 17:18:01.63 spid17s process id=process5953438 taskpriority=0 logused=360 waitresource=KEY: 7:72057595280621568 (0f03c6e319eb) waittime=234 ownerId=46134511 transactionname=user_transaction lasttranstarted=2009-07-17T17:18:01.403 XDES=0x255c13860 lockMode=X schedulerid=7 kpid=1536 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T17:18:01.407 lastbatchcompleted=2009-07-17T17:18:01.403 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=46134511 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 17:18:01.63 spid17s executionStack
    2009-07-17 17:18:01.63 spid17s frame procname=InsidEdge.dbo.usp_routing_upd line=50 stmtstart=4278 stmtend=9656 sqlhandle=0x03000700a03c9e46379d1e01259c00000100000000000000
    2009-07-17 17:18:01.63 spid17s UPDATE order_labor SET
    2009-07-17 17:18:01.63 spid17s emp_id = CASE
    2009-07-17 17:18:01.63 spid17s WHEN @emp_id > 0 THEN @emp_id
    2009-07-17 17:18:01.63 spid17s ELSE NULL END,
    2009-07-17 17:18:01.63 spid17s escalation_ind = @escalation_ind,
    2009-07-17 17:18:01.63 spid17s scheduled_dt = CASE
    2009-07-17 17:18:01.63 spid17s WHEN @emp_id > 0 AND @scheduled_dt > '1/1/1900' THEN @scheduled_dt -- PShah 6/2/09 added schedule_dt > 1/1/1900 condition
    2009-07-17 17:18:01.63 spid17s ELSE NULL END,
    2009-07-17 17:18:01.63 spid17s confirmed_apptmt_ind = @confirmed_apptmt_ind,
    2009-07-17 17:18:01.63 spid17s due_dt = CASE
    2009-07-17 17:18:01.63 spid17s WHEN isnull(@due_time, '') = '' THEN order_labor.due_dt -- keep as is
    2009-07-17 17:18:01.63 spid17s WHEN @scheduled_dt > '1/1/1900'
    2009-07-17 17:18:01.63 spid17s THEN convert(char(10), @scheduled_dt, 101) + ' ' + @due_time -- @sched_dt + @due_time
    2009-07-17 17:18:01.63 spid17s ELSE convert(char(10), order_labor.due_dt, 101) + ' ' + @due_time -- old due_dt + @due_time
    2009-07-17 17:18:01.63 spid17s END,
    2009-07-17 17:18:01.63 spid17s mobile_status_ind = @mobile_status_ind,
    2009-07-17 17:18:01.63 spid17s mobile_status_time = @mobile_status_time,
    2009-07-17 17:18:01.63 spid17s status_cd = /*
    2009-07-17 17:18:01.63 spid17s CASE
    2009-07-17 17:18:01.63 spid17s WHEN order_labor.status_cd = 'H' AND isnull(@due_time, '') = '' THEN 'H'
    2009-07-17 17:18:01.63 spid17s WHEN @scheduled_dt > '1/1/1900' AND @emp_id > 0 THEN 'S'
    2009-07-17 17:18:01.63 spid17s ELSE
    2009-07-17 17:18:01.63 spid17s inputbuf
    2009-07-17 17:18:01.63 spid17s Proc [Database Id = 7 Object Id = 1184775328]
    2009-07-17 17:18:01.63 spid17s resource-list
    2009-07-17 17:18:01.63 spid17s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lockbdaddc00 mode=X associatedObjectId=72057595282915328
    2009-07-17 17:18:01.63 spid17s owner-list
    2009-07-17 17:18:01.63 spid17s owner id=process5953438 mode=X
    2009-07-17 17:18:01.63 spid17s waiter-list
    2009-07-17 17:18:01.63 spid17s waiter id=process4d112e8 mode=S requestType=wait
    2009-07-17 17:18:01.63 spid17s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lock14ff07a80 mode=S associatedObjectId=72057595280621568
    2009-07-17 17:18:01.63 spid17s owner-list
    2009-07-17 17:18:01.63 spid17s owner id=process4d112e8 mode=S
    2009-07-17 17:18:01.63 spid17s waiter-list
    2009-07-17 17:18:01.63 spid17s waiter id=process5953438 mode=X requestType=wait

    --Deadlock 6
    2009-07-17 20:17:04.41 spid4s Deadlock encountered .... Printing deadlock information
    2009-07-17 20:17:04.41 spid4s Wait-for graph
    2009-07-17 20:17:04.41 spid4s
    2009-07-17 20:17:04.41 spid4s Node:1
    2009-07-17 20:17:04.41 spid4s KEY: 7:72057595282915328 (f3004dec0f71) CleanCnt:3 Mode:X Flags: 0x0
    2009-07-17 20:17:04.41 spid4s Grant List 3:
    2009-07-17 20:17:04.41 spid4s Owner:0x0000000080271680 Mode: X Flg:0x0 Ref:1 Life:02000001 SPID:61 ECID:0 XactLockInfo: 0x00000001793E5808
    2009-07-17 20:17:04.41 spid4s SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 265
    2009-07-17 20:17:04.41 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 831394081]
    2009-07-17 20:17:04.41 spid4s Requested By:
    2009-07-17 20:17:04.41 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000096385358 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0000000281B10598) Value:0x53ea1ac0 Cost:(0/0)
    2009-07-17 20:17:04.41 spid4s
    2009-07-17 20:17:04.41 spid4s Node:2
    2009-07-17 20:17:04.41 spid4s KEY: 7:72057595280621568 (bc04b4cde8f7) CleanCnt:2 Mode:S Flags: 0x0
    2009-07-17 20:17:04.41 spid4s Grant List 1:
    2009-07-17 20:17:04.41 spid4s Owner:0x0000000152BE03C0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:55 ECID:0 XactLockInfo: 0x0000000096385390
    2009-07-17 20:17:04.41 spid4s SPID: 55 ECID: 0 Statement Type: INSERT Line #: 40
    2009-07-17 20:17:04.41 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 20:17:04.41 spid4s Requested By:
    2009-07-17 20:17:04.41 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000001793E57D0 Mode: X SPID:61 BatchID:0 ECID:0 TaskProxy:(0x0000000280392598) Value:0x80188f00 Cost:(0/1244)
    2009-07-17 20:17:04.41 spid4s
    2009-07-17 20:17:04.41 spid4s Victim Resource Owner:
    2009-07-17 20:17:04.41 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000096385358 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0000000281B10598) Value:0x53ea1ac0 Cost:(0/0)
    2009-07-17 20:17:04.41 spid20s deadlock-list
    2009-07-17 20:17:04.41 spid20s deadlock victim=process4d10ef8
    2009-07-17 20:17:04.41 spid20s process-list
    2009-07-17 20:17:04.41 spid20s process id=process4d10ef8 taskpriority=0 logused=0 waitresource=KEY: 7:72057595282915328 (f3004dec0f71) waittime=1312 ownerId=46460243 transactionname=INSERT lasttranstarted=2009-07-17T20:17:02.307 XDES=0x96385358 lockMode=S schedulerid=4 kpid=5116 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-17T20:17:01.057 lastbatchcompleted=2009-07-17T20:17:01.050 clientapp=.Net SqlClient Data Provider hostname=CLE-DSQL-002 hostpid=3700 loginname=sched_360_user isolationlevel=read committed (2) xactid=46460243 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-07-17 20:17:04.41 spid20s executionStack
    2009-07-17 20:17:04.41 spid20s frame procname=InsidEdge.dbo.f_360_get_jobs line=40 stmtstart=3150 stmtend=32074 sqlhandle=0x030007007f531570acf7c400259c00000000000000000000
    2009-07-17 20:17:04.41 spid20s INSERT INTO @tbl_jobs(order_num, ols_tkt_num, site_id, tzone, job_value, response, service_type)
    2009-07-17 20:17:04.41 spid20s --PULL INSTALLS
    2009-07-17 20:17:04.41 spid20s SELECT DISTINCT ol.order_num, ol.ols_tkt_num, o.site_id,
    2009-07-17 20:17:04.41 spid20s CASE
    2009-07-17 20:17:04.41 spid20s WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_past_gmt
    2009-07-17 20:17:04.41 spid20s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'Y' THEN '0' + CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(1))
    2009-07-17 20:17:04.41 spid20s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'Y' THEN CAST((f.hours_past_gmt - dbo.f_is_daylightsavingstime(getdate())) AS varchar(2))
    2009-07-17 20:17:04.41 spid20s WHEN f.hours_past_gmt < 10 AND f.day_light_saving_ind = 'N' THEN '0' + CAST(f.hours_past_gmt AS varchar(1))
    2009-07-17 20:17:04.41 spid20s WHEN f.hours_past_gmt >= 10 AND f.day_light_saving_ind = 'N' THEN CAST(f.hours_past_gmt AS varchar(2))
    2009-07-17 20:17:04.41 spid20s END As tzone,
    2009-07-17 20:17:04.41 spid20s -- --###################################################
    2009-07-17 20:17:04.41 spid20s -- --Need to check the logic on how dst is followed.
    2009-07-17 20:17:04.41 spid20s -- CASE
    2009-07-17 20:17:04.41 spid20s -- WHEN f.hours_past_gmt IS NULL THEN @adj_local_hrs_pa
    2009-07-17 20:17:04.41 spid20s frame procname=InsidEdge.dbo.f_360_get_prerequisites line=54 stmtstart=4308 stmtend=5146 sqlhandle=0x03000700462f216fbc9de600f29b00000000000000000000
    2009-07-17 20:17:04.41 spid20s DELETE FROM @tbl_prerequisites
    2009-07-17 20:17:04.41 spid20s WHERE
    2009-07-17 20:17:04.41 spid20s -- (ols_tkt_num2 NOT IN(SELECT ols_tkt_num FROM @tickets)
    2009-07-17 20:17:04.41 spid20s -- (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(@from_dt, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 20:17:04.41 spid20s (ols_tkt_num1 NOT IN(SELECT ols_tkt_num FROM dbo.f_360_get_jobs(null, @thru_dt, @adjLocalHrsPastGmt)) --03/17/09 RY
    2009-07-17 20:17:04.41 spid20s OR
    2009-07-17 20:17:04.41 spid20s ols_tkt_num1 IS NULL)
    2009-07-17 20:17:04.41 spid20s frame procname=InsidEdge.dbo.usp_360_chg_get_add_activity line=99 stmtstart=7686 stmtend=8162 sqlhandle=0x030007004901e1083f41a300259c00000100000000000000
    2009-07-17 20:17:04.41 spid20s INSERT INTO @prerequisites(ols_tkt_num1, ols_tkt_num2)
    2009-07-17 20:17:04.41 spid20s SELECT ols_tkt_num1, ols_tkt_num2
    2009-07-17 20:17:04.41 spid20s FROM f_360_get_prerequisites(@xml_ticket_list)
    2009-07-17 20:17:04.41 spid20s --UPDATE SNAPSHOT TABLES - sched_360_in_xxx
    2009-07-17 20:17:04.41 spid20s --sched_360_in_activity
    2009-07-17 20:17:04.41 spid20s inputbuf
    2009-07-17 20:17:04.41 spid20s Proc [Database Id = 7 Object Id = 148963657]
    2009-07-17 20:17:04.41 spid20s process id=process5969198 taskpriority=0 logused=1244 waitresource=KEY: 7:72057595280621568 (bc04b4cde8f7) waittime=1640 ownerId=46460373 transactionname=user_transaction lasttranstarted=2009-07-17T20:17:02.767 XDES=0x1793e57d0 lockMode=X schedulerid=8 kpid=4908 status=suspended spid=61 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-17T20:17:02.770 lastbatchcompleted=2009-07-17T20:17:02.767 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=2304 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=46460373 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    2009-07-17 20:17:04.41 spid20s executionStack
    2009-07-17 20:17:04.41 spid20s frame procname=InsidEdge.dbo.usp_codeoff_save line=265 stmtstart=20730 stmtend=23408 sqlhandle=0x0300070021118e310abc2601469c00000100000000000000
    2009-07-17 20:17:04.41 spid20s UPDATE order_labor SET time_in = M.time_in,
    2009-07-17 20:17:04.41 spid20s time_out = M.time_out,
    2009-07-17 20:17:04.41 spid20s status_cd = 'C',
    2009-07-17 20:17:04.41 spid20s mileage = M.mileage,
    2009-07-17 20:17:04.41 spid20s emp_consultant_ind = M.emp_consultant_ind,
    2009-07-17 20:17:04.41 spid20s extended_price = M.extended_price,
    2009-07-17 20:17:04.41 spid20s emp_zone = M.emp_zone,
    2009-07-17 20:17:04.41 spid20s --Modified By SPisal 06/08/09 Site local time
    2009-07-17 20:17:04.41 spid20s completed_dt = CASE
    2009-07-17 20:17:04.41 spid20s WHEN order_labor.completed_dt IS NULL THEN @site_local_time
    2009-07-17 20:17:04.41 spid20s ELSE order_labor.completed_dt END
    2009-07-17 20:17:04.41 spid20s ,
    2009-07-17 20:17:04.41 spid20s last_update_dt = @date_now,
    2009-07-17 20:17:04.41 spid20s last_update_userid = M.last_update_userid,
    2009-07-17 20:17:04.41 spid20s completed_by=M.last_update_userid,
    2009-07-17 20:17:04.41 spid20s codeoff_comp_ind=M.codeoff_comp_ind,
    2009-07-17 20:17:04.41 spid20s gl_account_num_rev=[dbo].[f_get_gl_account_code] ('R','OLS',M.order_num,0,0,M.service_type_cd,''),
    2009-07-17 20:17:04.41 spid20s billing_appr_ind=M.billing_appr_ind,
    2009-07-17 20:17:04.41 spid20s --Modified By Sachin Pisal 06/22/09
    2009-07-17 20:17:04.41 spid20s call_out_ind=case
    2009-07-17 20:17:04.41 spid20s WHEN M.call_out_ind IS NOT NULL THEN M.call_out_ind
    2009-07-17 20:17:04.41 spid20s ELSE order_labor.call_out_ind end
    2009-07-17 20:17:04.41 spid20s FROM @tempM M ,@tempO O
    2009-07-17 20:17:04.41 spid20s WHERE order_labor.ols_tkt_num = M.ols_tkt_num
    2009-07-17 20:17:04.41 spid20s AND :confused:ls_tkt_num
    2009-07-17 20:17:04.41 spid20s inputbuf
    2009-07-17 20:17:04.41 spid20s Proc [Database Id = 7 Object Id = 831394081]
    2009-07-17 20:17:04.41 spid20s resource-list
    2009-07-17 20:17:04.41 spid20s keylock hobtid=72057595280621568 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=_dta_index_order_labor_13_494624805__K2_K16_K17_K1_K6_K3_K5_K4_15_19 id=lockbdca4a00 mode=S associatedObjectId=72057595280621568
    2009-07-17 20:17:04.41 spid20s owner-list
    2009-07-17 20:17:04.41 spid20s owner id=process4d10ef8 mode=S
    2009-07-17 20:17:04.41 spid20s waiter-list
    2009-07-17 20:17:04.41 spid20s waiter id=process5969198 mode=X requestType=wait
    2009-07-17 20:17:04.41 spid20s keylock hobtid=72057595282915328 dbid=7 objectname=InsidEdge.dbo.order_labor indexname=PK_order_labor id=lock154031480 mode=X associatedObjectId=72057595282915328
    2009-07-17 20:17:04.41 spid20s owner-list
    2009-07-17 20:17:04.41 spid20s owner id=process5969198 mode=X
    2009-07-17 20:17:04.41 spid20s waiter-list
    2009-07-17 20:17:04.41 spid20s waiter id=process4d10ef8 mode=S requestType=wait
  7. lcerni New Member

  8. lcerni New Member

    I believe that by deleting the above index that has resolved most of our deadlock issues. Today a page deadlock occurred.
    2009-08-03 20:41:57.76 spid4s Deadlock encountered .... Printing deadlock information
    2009-08-03 20:41:57.76 spid4s Wait-for graph
    2009-08-03 20:41:57.76 spid4s
    2009-08-03 20:41:57.76 spid4s Node:1
    2009-08-03 20:41:57.76 spid4s PAGE: 7:1:824318 CleanCnt:3 Mode:S Flags: 0x2
    2009-08-03 20:41:57.76 spid4s Grant List 1:
    2009-08-03 20:41:57.76 spid4s Owner:0x0000000080252FC0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:54 ECID:0 XactLockInfo: 0x00000000800451F0
    2009-08-03 20:41:57.76 spid4s SPID: 54 ECID: 0 Statement Type: SELECT Line #: 2
    2009-08-03 20:41:57.76 spid4s Input Buf: Language Event: (@status_cd nvarchar(1),@zone int,@emp_id int,@office_cd nvarchar(2))
    SELECT DISTINCT
    count(
    order_labor.ols_tkt_num)

    2009-08-03 20:41:57.76 spid4s Requested By:
    2009-08-03 20:41:57.76 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000133A18370 Mode: IX SPID:74 BatchID:0 ECID:0 TaskProxy:(0x0000000273E64598) Value:0xf8016f40 Cost:(0/2920)
    2009-08-03 20:41:57.76 spid4s
    2009-08-03 20:41:57.76 spid4s Node:2
    2009-08-03 20:41:57.76 spid4s PAGE: 7:1:824324 CleanCnt:2 Mode:IX Flags: 0x2
    2009-08-03 20:41:57.76 spid4s Grant List 0:
    2009-08-03 20:41:57.76 spid4s Owner:0x00000000E0B13F80 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:74 ECID:0 XactLockInfo: 0x0000000133A183A8
    2009-08-03 20:41:57.76 spid4s SPID: 74 ECID: 0 Statement Type: UPDATE Line #: 266
    2009-08-03 20:41:57.76 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 831394081]
    2009-08-03 20:41:57.76 spid4s Requested By:
    2009-08-03 20:41:57.76 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000800451B8 Mode: S SPID:54 BatchID:0 ECID:0 TaskProxy:(0x0000000207214598) Value:0xf7ec7e40 Cost:(0/0)
    2009-08-03 20:41:57.76 spid4s
    2009-08-03 20:41:57.76 spid4s Victim Resource Owner:
    2009-08-03 20:41:57.76 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000800451B8 Mode: S SPID:54 BatchID:0 ECID:0 TaskProxy:(0x0000000207214598) Value:0xf7ec7e40 Cost:(0/0)
    2009-08-03 20:41:57.76 spid16s deadlock-list
    2009-08-03 20:41:57.76 spid16s deadlock victim=processf71eb8
    2009-08-03 20:41:57.76 spid16s process-list
    2009-08-03 20:41:57.76 spid16s process id=processccf978 taskpriority=0 logused=2920 waitresource=PAGE: 7:1:824318 waittime=3093 ownerId=62618238 transactionname=user_transaction lasttranstarted=2009-08-03T20:41:54.650 XDES=0x133a18370 lockMode=IX schedulerid=1 kpid=2992 status=suspended spid=74 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-08-03T20:41:54.653 lastbatchcompleted=2009-08-03T20:41:54.650 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=3048 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=62618238 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    2009-08-03 20:41:57.76 spid16s executionStack
    2009-08-03 20:41:57.76 spid16s frame procname=InsidEdge.dbo.usp_codeoff_save line=266 stmtstart=20892 stmtend=23570 sqlhandle=0x0300070021118e31187b1a01509c00000100000000000000
    2009-08-03 20:41:57.76 spid16s UPDATE order_labor SET time_in = M.time_in,
    2009-08-03 20:41:57.76 spid16s time_out = M.time_out,
    2009-08-03 20:41:57.76 spid16s status_cd = 'C',
    2009-08-03 20:41:57.76 spid16s mileage = M.mileage,
    2009-08-03 20:41:57.76 spid16s emp_consultant_ind = M.emp_consultant_ind,
    2009-08-03 20:41:57.76 spid16s extended_price = M.extended_price,
    2009-08-03 20:41:57.76 spid16s emp_zone = M.emp_zone,
    2009-08-03 20:41:57.76 spid16s --Modified By SPisal 06/08/09 Site local time
    2009-08-03 20:41:57.76 spid16s completed_dt = CASE
    2009-08-03 20:41:57.76 spid16s WHEN order_labor.completed_dt IS NULL THEN @site_local_time
    2009-08-03 20:41:57.76 spid16s ELSE order_labor.completed_dt END
    2009-08-03 20:41:57.76 spid16s ,
    2009-08-03 20:41:57.76 spid16s last_update_dt = @date_now,
    2009-08-03 20:41:57.76 spid16s last_update_userid = M.last_update_userid,
    2009-08-03 20:41:57.76 spid16s completed_by=M.last_update_userid,
    2009-08-03 20:41:57.76 spid16s codeoff_comp_ind=M.codeoff_comp_ind,
    2009-08-03 20:41:57.76 spid16s gl_account_num_rev=[dbo].[f_get_gl_account_code] ('R','OLS',M.order_num,0,0,M.service_type_cd,''),
    2009-08-03 20:41:57.76 spid16s billing_appr_ind=M.billing_appr_ind,
    2009-08-03 20:41:57.76 spid16s --Modified By Sachin Pisal 06/22/09
    2009-08-03 20:41:57.76 spid16s call_out_ind=case
    2009-08-03 20:41:57.76 spid16s WHEN M.call_out_ind IS NOT NULL THEN M.call_out_ind
    2009-08-03 20:41:57.76 spid16s ELSE order_labor.call_out_ind end
    2009-08-03 20:41:57.76 spid16s FROM @tempM M ,@tempO O
    2009-08-03 20:41:57.76 spid16s WHERE order_labor.ols_tkt_num = M.ols_tkt_num
    2009-08-03 20:41:57.76 spid16s AND :confused:ls_tkt_num
    2009-08-03 20:41:57.76 spid16s inputbuf
    2009-08-03 20:41:57.76 spid16s Proc [Database Id = 7 Object Id = 831394081]
    2009-08-03 20:41:57.76 spid16s process id=processf71eb8 taskpriority=0 logused=0 waitresource=PAGE: 7:1:824324 waittime=3093 ownerId=62618248 transactionname=SELECT lasttranstarted=2009-08-03T20:41:54.663 XDES=0x800451b8 lockMode=S schedulerid=4 kpid=4172 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-08-03T20:41:54.663 lastbatchcompleted=2009-08-03T20:41:54.663 clientapp=.Net SqlClient Data Provider hostname=CLE-IEAS-04 hostpid=3048 loginname=CORPORATE1InsidEdgeSvc isolationlevel=read committed (2) xactid=62618248 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-08-03 20:41:57.76 spid16s executionStack
    2009-08-03 20:41:57.76 spid16s frame procname=adhoc line=2 stmtstart=176 sqlhandle=0x020000004dda602b99f39daea6aab757a393d25fc8105951
    2009-08-03 20:41:57.76 spid16s SELECT DISTINCT
    2009-08-03 20:41:57.76 spid16s count(
    2009-08-03 20:41:57.76 spid16s order_labor.ols_tkt_num)
    2009-08-03 20:41:57.76 spid16s FROM order_labor
    2009-08-03 20:41:57.76 spid16s INNER JOIN orders
    2009-08-03 20:41:57.76 spid16s ON orders.order_num = order_labor.order_num
    2009-08-03 20:41:57.76 spid16s INNER JOIN site
    2009-08-03 20:41:57.76 spid16s ON site.site_id = orders.site_id
    2009-08-03 20:41:57.76 spid16s INNER JOIN service_type
    2009-08-03 20:41:57.76 spid16s ON service_type.service_type_cd = order_labor.service_type_cd
    2009-08-03 20:41:57.76 spid16s LEFT OUTER JOIN client
    2009-08-03 20:41:57.76 spid16s ON orders.client_id = client.client_id
    2009-08-03 20:41:57.76 spid16s LEFT OUTER JOIN client_contract
    2009-08-03 20:41:57.76 spid16s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
    2009-08-03 20:41:57.76 spid16s unknown
    2009-08-03 20:41:57.76 spid16s inputbuf
    2009-08-03 20:41:57.76 spid16s (@status_cd nvarchar(1),@zone int,@emp_id int,@office_cd nvarchar(2))
    2009-08-03 20:41:57.76 spid16s SELECT DISTINCT
    2009-08-03 20:41:57.76 spid16s count(
    2009-08-03 20:41:57.76 spid16s order_labor.ols_tkt_num)
    2009-08-03 20:41:57.76 spid16s FROM order_labor
    2009-08-03 20:41:57.76 spid16s INNER JOIN orders
    2009-08-03 20:41:57.76 spid16s ON orders.order_num = order_labor.order_num
    2009-08-03 20:41:57.76 spid16s INNER JOIN site
    2009-08-03 20:41:57.76 spid16s ON site.site_id = orders.site_id
    2009-08-03 20:41:57.76 spid16s INNER JOIN service_type
    2009-08-03 20:41:57.76 spid16s ON service_type.service_type_cd = order_labor.service_type_cd
    2009-08-03 20:41:57.76 spid16s LEFT OUTER JOIN client
    2009-08-03 20:41:57.76 spid16s ON orders.client_id = client.client_id
    2009-08-03 20:41:57.76 spid16s resource-list
    2009-08-03 20:41:57.76 spid16s pagelock fileid=1 pageid=824324 dbid=7 objectname=InsidEdge.dbo.order_labor id=lockf5198280 mode=IX associatedObjectId=72057595342094336
    2009-08-03 20:41:57.76 spid16s owner-list
    2009-08-03 20:41:57.76 spid16s owner id=processccf978 mode=IX
    2009-08-03 20:41:57.76 spid16s waiter-list
    2009-08-03 20:41:57.76 spid16s waiter id=processf71eb8 mode=S requestType=wait
    2009-08-03 20:41:57.76 spid16s pagelock fileid=1 pageid=824318 dbid=7 objectname=InsidEdge.dbo.order_labor id=lock1d320c300 mode=S associatedObjectId=72057595342094336
    2009-08-03 20:41:57.76 spid16s owner-list
    2009-08-03 20:41:57.76 spid16s owner id=processf71eb8 mode=S
    2009-08-03 20:41:57.76 spid16s waiter-list
    2009-08-03 20:41:57.76 spid16s waiter id=processccf978 mode=IX requestType=wait

Share This Page