SQL Server Performance

Transaction Replication Deadlocks

Discussion in 'SQL Server 2008 Replication' started by darknight, Apr 1, 2011.

  1. darknight New Member

    Hi All,
    Recently we have moved our Primary DB system from SQL 2005 single Instance Running on windows 2003 to SQL 2008 2 Node Cluster Running on Windows Server 2008.
    Server Config as below:
    HP DL380 G6, 2*4 Xeon X5550 2.67 GHz with 44GB Ram running SQL Server Standard Edition. Connect to HP MSA 2012FC - Dedicated SAN. Hyper threading is disabled.
    We have transaction replication configured from the Primary DB server to 2 subscribers and from there to 8 Different Servers.
    After this Migration we are encountering lots of Dead locks... In the initial Investigation it seems because of replication these Dead locks are occurring.
    I am clueless @ this moment how to resolve this. As per my understanding transaction replication reads the LOG file to replicate the data. Not sure why it’s blocking other transactions????
    Please see below for the Deadlock Info.
    Any help would be appreciated.
    2011-04-01 11:02:25.300 spid8s Deadlock encountered .... Printing deadlock information
    2011-04-01 11:02:25.300 spid8s Wait-for graph
    2011-04-01 11:02:25.300 spid8s NULL
    2011-04-01 11:02:25.300 spid8s Node:1
    2011-04-01 11:02:25.300 spid8s OBJECT: 5:2043258434:0 CleanCnt:3 Mode:Sch-M Flags: 0x1
    2011-04-01 11:02:25.300 spid8s Wait List:
    2011-04-01 11:02:25.300 spid8s Owner:0x00000006B903F940 Mode: IS Flg:0x42 Ref:1 Life:00000000 SPID:296 ECID:0 XactLockInfo: 0x00000001C20B39B0
    2011-04-01 11:02:25.300 spid8s SPID: 296 ECID: 0 Statement Type: UPDATE Line #: 431
    2011-04-01 11:02:25.300 spid8s Input Buf: Language Event: Exec [dbo].[Track_ChargeAdvertiser_AdOfferImpressionQuick]
    2011-04-01 11:02:25.300 spid8s Requested by:
    2011-04-01 11:02:25.300 spid8s ResType:LockOwner Stype:'OR'Xdes:0x0000000984623780 Mode: IS SPID:284 BatchID:0 ECID:0 TaskProxy:(0x0000000987B00538) Value:0xb88f7a40 Cost:(0/0)
    2011-04-01 11:02:25.300 spid8s NULL
    2011-04-01 11:02:25.300 spid8s Node:2
    2011-04-01 11:02:25.300 spid8s OBJECT: 5:2043258434:0 CleanCnt:3 Mode:Sch-M Flags: 0x1
    2011-04-01 11:02:25.300 spid8s Grant List 0:
    2011-04-01 11:02:25.300 spid8s Owner:0x00000006C2C31A00 Mode: Sch-M Flg:0x40 Ref:1 Life:02000000 SPID:303 ECID:0 XactLockInfo: 0x00000009981B0ED0
    2011-04-01 11:02:25.300 spid8s SPID: 303 ECID: 0 Statement Type: EXECUTE Line #: 1
    2011-04-01 11:02:25.300 spid8s Input Buf: RPC Event: Proc [Database Id = 32767 Object Id = 710746692]
    2011-04-01 11:02:25.300 spid8s Requested by:
    2011-04-01 11:02:25.300 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000001C20B3970 Mode: IS SPID:296 BatchID:0 ECID:0 TaskProxy:(0x00000008C2344538) Value:0xb903f940 Cost:(0/0)
    2011-04-01 11:02:25.300 spid8s NULL
    2011-04-01 11:02:25.300 spid8s Node:3
    2011-04-01 11:02:25.300 spid8s OBJECT: 5:77347440:0 CleanCnt:2 Mode:Sch-S Flags: 0x1
    2011-04-01 11:02:25.300 spid8s Grant List 1:
    2011-04-01 11:02:25.300 spid8s Owner:0x00000006B88F7940 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:284 ECID:0 XactLockInfo: 0x00000009846237C0
    2011-04-01 11:02:25.300 spid8s SPID: 284 ECID: 0 Statement Type: SELECT Line #: 26
    2011-04-01 11:02:25.300 spid8s Input Buf: Language Event: EXEC dbo.usp_updateDailyBudgetBalanceQuickHourly
    2011-04-01 11:02:25.300 spid8s Requested by:
    2011-04-01 11:02:25.300 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000009981B0E90 Mode: Sch-M SPID:303 BatchID:0 ECID:0 TaskProxy:(0x00000008CE6E8538) Value:0xb853c240 Cost:(0/1220)
    2011-04-01 11:02:25.300 spid8s NULL
    2011-04-01 11:02:25.300 spid8s Victim Resource Owner:
    2011-04-01 11:02:25.300 spid8s ResType:LockOwner Stype:'OR'Xdes:0x0000000984623780 Mode: IS SPID:284 BatchID:0 ECID:0 TaskProxy:(0x0000000987B00538) Value:0xb88f7a40 Cost:(0/0)
    2011-04-01 11:02:25.790 spid21s deadlock-list
    2011-04-01 11:02:25.790 spid21s deadlock victim=process461d708
    2011-04-01 11:02:25.790 spid21s process-list
    2011-04-01 11:02:25.790 spid21s process id=process461d708 taskpriority=0 logused=0 waitresource=OBJECT: 5:2043258434:0 waittime=129347 ownerId=2589361975 transactionname=DECLARE CURSOR lasttranstarted=2011-04-01T11:00:15.950 XDES=0x984623780 lockMode=IS schedulerid=3 kpid=7480 status
    2011-04-01 11:02:25.790 spid21s executionStack
    2011-04-01 11:02:25.790 spid21s frame procname=Adjug.dbo.usp_updateDailyBudgetBalanceQuickHourly line=26 stmtstart=2204 stmtend=3650 sqlhandle=0x030005005279ac00cba02800b09e00000100000000000000
    2011-04-01 11:02:25.790 spid21s DECLARE campaignUpdateDailyBudgetHourly CURSOR FOR
    2011-04-01 11:02:25.790 spid21s SELECT OfferID,AdPayTypeID FROM HourlyBudgetEnabledCampaign hbec
    2011-04-01 11:02:25.790 spid21s LEFT JOIN AdvertiserCampaignOffer aco
    2011-04-01 11:02:25.790 spid21s ON hbec.OfferID = aco.ID
    2011-04-01 11:02:25.790 spid21s WHERE hbec.IsEnabled = 1 AND aco.Statuscode IN (1, 2, 3)
    2011-04-01 11:02:25.790 spid21s AND aco.[ID] IN ( SELECT DISTINCT OfferID
    2011-04-01 11:02:25.790 spid21s FROM dbo.AdvertiserCampaignOfferSiteAdSpaceBridge WITH (READUNCOMMITTED)
    2011-04-01 11:02:25.790 spid21s WHERE Statuscode = 1)
    2011-04-01 11:02:25.790 spid21s AND @BudgetDateTime >= StartDate AND @BudgetDateTime < dateadd(day,1,EndDate)
    2011-04-01 11:02:25.790 spid21s AND (hbec.OfferID IN (SELECT sdhb.OfferID FROM systemDefinedHourlyBudget sdhb WHERE sdhb.TimeDate = @BudgetDateTime )
    2011-04-01 11:02:25.790 spid21s OR
    2011-04-01 11:02:25.790 spid21s hbec.OfferID IN (SELECT udhb.OfferID FROM UserDefinedHourlyBudget udhb WHERE udhb.TimeDate = @BudgetDateTime))
    2011-04-01 11:02:25.790 spid21s frame procname=adhoc line=1 sqlhandle=0x0100050037e05b37309385fc060000000000000000000000
    2011-04-01 11:02:25.790 spid21s EXEC dbo.usp_updateDailyBudgetBalanceQuickHourly
    2011-04-01 11:02:25.790 spid21s inputbuf
    2011-04-01 11:02:25.790 spid21s EXEC dbo.usp_updateDailyBudgetBalanceQuickHourly
    2011-04-01 11:02:25.790 spid21s process id=process461c988 taskpriority=0 logused=0 waitresource=OBJECT: 5:2043258434:0 waittime=133708 ownerId=2589357973 transactionname=user_transaction lasttranstarted=2011-04-01T11:00:11.590 XDES=0x1c20b3970 lockMode=IS schedulerid=3 kpid=5036 stat
    2011-04-01 11:02:25.790 spid21s executionStack
    2011-04-01 11:02:25.790 spid21s frame procname=AdJug_Tracking.dbo.Track_ChargeAdvertiser_AdOfferImpressionQuick line=431 stmtstart=40596 stmtend=41998 sqlhandle=0x03000900c91df302bf213500b09e00000100000000000000
    2011-04-01 11:02:25.790 spid21s UPDATE AdJug.dbo.AdvertiserCampaignOfferBalanceQuick
    2011-04-01 11:02:25.790 spid21s SET DailyBudget = CASE WHEN Offer.DailyBudget = 0 THEN 0 ELSE Balance.DailyBudget - ChargeAmount END,
    2011-04-01 11:02:25.790 spid21s Balance = Balance - ChargeAmount,
    2011-04-01 11:02:25.790 spid21s BalanceOffSet = ChargeAmount,
    2011-04-01 11:02:25.790 spid21s DailyBudgetOffSet = ChargeAmount,
    2011-04-01 11:02:25.790 spid21s HourlyBalanceOffSet = ISNULL(HourlyBalanceOffSet,0) + ChargeAmount
    2011-04-01 11:02:25.790 spid21s FROM AdJug.dbo.AdvertiserCampaignOfferBalanceQuick Balance
    2011-04-01 11:02:25.790 spid21s JOIN AdJug.dbo.AdvertiserCampaignOffer Offer ON Offer.[ID] = Balance.OfferID
    2011-04-01 11:02:25.790 spid21s JOIN (
    2011-04-01 11:02:25.790 spid21s SELECT OfferID, CurrencyID, SUM(ChargeAmount)/1000.0 As ChargeAmount
    2011-04-01 11:02:25.790 spid21s FROM @AmountToCharge
    2011-04-01 11:02:25.790 spid21s GROUP BY OfferID, CurrencyID
    2011-04-01 11:02:25.790 spid21s ) ATC ON ATC.OfferID = Balance.OfferID AND ATC.CurrencyID = Balance.CurrencyID
    2011-04-01 11:02:25.790 spid21s frame procname=adhoc line=1 sqlhandle=0x01000900535ca618c0959961070000000000000000000000
    2011-04-01 11:02:25.790 spid21s Exec [dbo].[Track_ChargeAdvertiser_AdOfferImpressionQuick]
    2011-04-01 11:02:25.790 spid21s inputbuf
    2011-04-01 11:02:25.790 spid21s Exec [dbo].[Track_ChargeAdvertiser_AdOfferImpressionQuick]
    2011-04-01 11:02:25.790 spid21s process id=process77738a088 taskpriority=0 logused=1220 waitresource=OBJECT: 5:77347440:0 waittime=4148 ownerId=2589340720 transactionname=user_transaction lasttranstarted=2011-04-01T11:00:04.187 XDES=0x9981b0e90 lockMode=Sch-M schedulerid=2 kpid=5092
    2011-04-01 11:02:25.790 spid21s executionStack
    2011-04-01 11:02:25.790 spid21s frame procname=mssqlsystemresource.sys.sp_replupdateschema line=1 sqlhandle=0x0400ff7f089c1a3d01000000000000000000000000000000
    2011-04-01 11:02:25.790 spid21s sp_replupdateschema
    2011-04-01 11:02:25.790 spid21s frame procname=mssqlsystemresource.sys.sp_MSreplupdateschema line=16 stmtstart=748 stmtend=840 sqlhandle=0x0300ff7f44225d2a13aa7b01f39d00000100000000000000
    2011-04-01 11:02:25.790 spid21s exec sys.sp_replupdateschema @object_name
    2011-04-01 11:02:25.790 spid21s inputbuf
    2011-04-01 11:02:25.790 spid21s Proc [Database Id = 32767 Object Id = 710746692]
    2011-04-01 11:02:25.790 spid21s resource-list
    2011-04-01 11:02:25.790 spid21s objectlock lockPartition=0 objid=2043258434 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOffer id=locka3415280 mode=Sch-M associatedObjectId=2043258434
    2011-04-01 11:02:25.790 spid21s owner-list
    2011-04-01 11:02:25.790 spid21s waiter-list
    2011-04-01 11:02:25.790 spid21s waiter id=process461d708 mode=IS requestType=wait
    2011-04-01 11:02:25.790 spid21s objectlock lockPartition=0 objid=2043258434 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOffer id=locka3415280 mode=Sch-M associatedObjectId=2043258434
    2011-04-01 11:02:25.790 spid21s owner-list
    2011-04-01 11:02:25.790 spid21s owner id=process77738a088 mode=Sch-M
    2011-04-01 11:02:25.790 spid21s waiter-list
    2011-04-01 11:02:25.790 spid21s waiter id=process461c988 mode=IS requestType=wait
    2011-04-01 11:02:25.790 spid21s objectlock lockPartition=0 objid=77347440 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOfferSiteAdSpaceBridge id=lock6b9f0ed00 mode=Sch-S associatedObjectId=77347440
    2011-04-01 11:02:25.790 spid21s owner-list
    2011-04-01 11:02:25.790 spid21s owner id=process461d708 mode=Sch-S
    2011-04-01 11:02:25.790 spid21s waiter-list
    2011-04-01 11:02:25.790 spid21s waiter id=process77738a088 mode=Sch-M requestType=wait
    2011-04-01 11:02:42.800 spid8s Deadlock encountered .... Printing deadlock information
    2011-04-01 11:02:42.800 spid8s Wait-for graph
    2011-04-01 11:02:42.800 spid8s NULL
    2011-04-01 11:02:42.800 spid8s Node:1
    2011-04-01 11:02:42.800 spid8s OBJECT: 5:2043258434:0 CleanCnt:3 Mode:Sch-M Flags: 0x1
    2011-04-01 11:02:42.800 spid8s Wait List:
    2011-04-01 11:02:42.800 spid8s Owner:0x000000023D26CB00 Mode: IX Flg:0x42 Ref:1 Life:00000000 SPID:279 ECID:0 XactLockInfo: 0x00000006280FD2A0
    2011-04-01 11:02:42.800 spid8s SPID: 279 ECID: 0 Statement Type: INSERT Line #: 97
    2011-04-01 11:02:42.800 spid8s Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 300736324]
    2011-04-01 11:02:42.800 spid8s Requested by:
    2011-04-01 11:02:42.800 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000001BD625970 Mode: Sch-S SPID:95 BatchID:0 ECID:0 TaskProxy:(0x0000000190010538) Value:0xbc5f6a80 Cost:(0/0)
    2011-04-01 11:02:42.800 spid8s NULL
    2011-04-01 11:02:42.800 spid8s Node:2
    2011-04-01 11:02:42.800 spid8s OBJECT: 5:2043258434:0 CleanCnt:3 Mode:Sch-M Flags: 0x1
    2011-04-01 11:02:42.800 spid8s Grant List 0:
    2011-04-01 11:02:42.800 spid8s Owner:0x00000006BD24E940 Mode: Sch-M Flg:0x40 Ref:2 Life:02000000 SPID:303 ECID:0 XactLockInfo: 0x00000009981B0ED0
    2011-04-01 11:02:42.800 spid8s SPID: 303 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 30
    2011-04-01 11:02:42.800 spid8s Input Buf: RPC Event: Proc [Database Id = 32767 Object Id = 993696157]
    2011-04-01 11:02:42.800 spid8s Requested by:
    2011-04-01 11:02:42.800 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000006280FD260 Mode: IX SPID:279 BatchID:0 ECID:0 TaskProxy:(0x0000000528AD4538) Value:0x3d26cb00 Cost:(0/0)
    2011-04-01 11:02:42.800 spid8s NULL
    2011-04-01 11:02:42.800 spid8s Node:3
    2011-04-01 11:02:42.800 spid8s OBJECT: 5:77347440:0 CleanCnt:2 Mode:S Flags: 0x1
    2011-04-01 11:02:42.800 spid8s Grant List 0:
    2011-04-01 11:02:42.800 spid8s Owner:0x00000006BB83DB40 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:95 ECID:0 XactLockInfo: 0x00000001BD6259B0
    2011-04-01 11:02:42.800 spid8s SPID: 95 ECID: 0 Statement Type: INSERT Line #: 1
    2011-04-01 11:02:42.800 spid8s Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 1495168572]
    2011-04-01 11:02:42.800 spid8s Requested by:
    2011-04-01 11:02:42.800 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000009981B0E90 Mode: Sch-M SPID:303 BatchID:0 ECID:0 TaskProxy:(0x00000008CE6E8538) Value:0xbbe3e500 Cost:(0/60052)
    2011-04-01 11:02:42.800 spid8s NULL
    2011-04-01 11:02:42.800 spid8s Victim Resource Owner:
    2011-04-01 11:02:42.800 spid8s ResType:LockOwner Stype:'OR'Xdes:0x00000001BD625970 Mode: Sch-S SPID:95 BatchID:0 ECID:0 TaskProxy:(0x0000000190010538) Value:0xbc5f6a80 Cost:(0/0)
    2011-04-01 11:02:42.990 spid22s deadlock-list
    2011-04-01 11:02:42.990 spid22s deadlock victim=process3c02bc8
    2011-04-01 11:02:42.990 spid22s process-list
    2011-04-01 11:02:42.990 spid22s process id=process3c02bc8 taskpriority=0 logused=0 waitresource=OBJECT: 5:2043258434:0 waittime=4810 ownerId=2589635429 transactionname=INSERT lasttranstarted=2011-04-01T11:02:37.997 XDES=0x1bd625970 lockMode=Sch-S schedulerid=2 kpid=3604 status=suspen
    2011-04-01 11:02:42.990 spid22s executionStack
    2011-04-01 11:02:42.990 spid22s frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x0200000042383d34b856821e72f17267a8fc76d2b96cfda3
    2011-04-01 11:02:42.990 spid22s INSERT INTO #TMPCAMPAIGNLIST SELECT DISTINCT AdCampaign.ID FROM AdvertiserCampaignOffer AdCampaign WITH (READUNCOMMITTED)
    2011-04-01 11:02:42.990 spid22s LEFT JOIN dbo.AdvertiserCampaignEchoOfferDetails EchoDetails WITH (READUNCOMMITTED) ON EchoDetails.OfferID = AdCampaign.[ID]
    2011-04-01 11:02:42.990 spid22s LEFT JOIN dbo.AdvertiserCampaignOfferBalance Balance WITH (READUNCOMMITTED) ON Balance.OfferID = AdCampaign.[ID]
    2011-04-01 11:02:42.990 spid22s LEFT JOIN dbo.AdvertiserCampaignOfferSiteAdSpaceBridge Bridge WITH (READUNCOMMITTED) ON Bridge.OfferID = AdCampaign.[ID] AND Bridge.Statuscode = 1 WHERE 1 = 1
    2011-04-01 11:02:42.990 spid22s AND Balance.CurrencyID = 1 AND ( ( 'Apr 1 2011 12:00AM' BETWEEN StartDate AND EndDate
    2011-04-01 11:02:42.990 spid22s --AND Bridge.Statuscode = 1
    2011-04-01 11:02:42.990 spid22s AND AdCampaign.Statuscode = 1 AND Balance.Balance >= AdCampaign.MaxBid ) ) AND AdCampaign.CampaignGroupID = 2155 AND AdvertiserID IN (SELECT [No] FROM DBO.SplitIntegerString('3909',','))
    2011-04-01 11:02:42.990 spid22s frame procname=Adjug.dbo.IntranetUI_GetAdCampaignDetails_ByAdvertiserIDList_Count line=158 stmtstart=16254 stmtend=16352 sqlhandle=0x030005003c761e5953a62800b09e00000100000000000000
    2011-04-01 11:02:42.990 spid22s EXEC(@SQL1)
    2011-04-01 11:02:42.990 spid22s --PRINT @SQL1
    2011-04-01 11:02:42.990 spid22s inputbuf
    2011-04-01 11:02:42.990 spid22s Proc [Database Id = 5 Object Id = 1495168572]
    2011-04-01 11:02:42.990 spid22s process id=process5152bc8 taskpriority=0 logused=0 waitresource=OBJECT: 5:2043258434:0 waittime=5135 ownerId=2589632993 transactionname=user_transaction lasttranstarted=2011-04-01T11:02:37.670 XDES=0x6280fd260 lockMode=IX schedulerid=7 kpid=2608 status
    2011-04-01 11:02:42.990 spid22s executionStack
    2011-04-01 11:02:42.990 spid22s frame procname=Adjug.dbo.usp_SysAdminUI_Advertiser_CloneCampaign line=97 stmtstart=15212 stmtend=20386 sqlhandle=0x0300050044dfec110a169d00b49e00000100000000000000
    2011-04-01 11:02:42.990 spid22s INSERT INTO dbo.AdvertiserCampaignOffer
    2011-04-01 11:02:42.990 spid22s (AdvertiserID,AdPayTypeID,Name,StatusCode,MaxBid,BidOverride,DailyBudget,TotalBudget,StartDate,EndDate,CurrentCTR,
    2011-04-01 11:02:42.990 spid22s ConversionTrackingEnabled,TimeSpanPause,UseCountryTargeting,UseRegionTargeting,UseCityTargeting,
    2011-04-01 11:02:42.990 spid22s LastActionDate,CreatedDate,DeletedDate,UseMetroTargeting,Completed,UseISPTargeting,CampaignGroupID,
    2011-04-01 11:02:42.990 spid22s UseConnectionHomeBusinessTargeting,TargetHomeUsers,UseTimeSpanTargeting,RetargetSiteListID)
    2011-04-01 11:02:42.990 spid22s SELECT AdvertiserID, AdPayTypeID, @p_CampaignName, 1, MaxBid,
    2011-04-01 11:02:42.990 spid22s 0, --Default
    2011-04-01 11:02:42.990 spid22s CASE WHEN @p_UseSameDailyBudgetCap = 1 THEN DailyBudget ELSE 0 END,
    2011-04-01 11:02:42.990 spid22s 0,--Budget is zero, it is down to the Trafficker to add money into the campaign once the campaign has been created.
    2011-04-01 11:02:42.990 spid22s dbo.FormatDate(@p_StartDate, 1),
    2011-04-01 11:02:42.990 spid22s dbo.FormatDate(@p_EndD
    2011-04-01 11:02:42.990 spid22s inputbuf
    2011-04-01 11:02:42.990 spid22s Proc [Database Id = 5 Object Id = 300736324]
    2011-04-01 11:02:42.990 spid22s process id=process3c03dc8 taskpriority=0 logused=60052 waitresource=OBJECT: 5:77347440:0 waittime=4760 ownerId=2589625656 transactionname=user_transaction lasttranstarted=2011-04-01T11:02:36.623 XDES=0x9981b0e90 lockMode=Sch-M schedulerid=2 kpid=7288 s
    2011-04-01 11:02:42.990 spid22s executionStack
    2011-04-01 11:02:42.990 spid22s frame procname=mssqlsystemresource.sys.sp_MSsetfilteredstatus line=30 stmtstart=1532 stmtend=1758 sqlhandle=0x0300ff7f9a07d53279cc7c01f39d00000100000000000000
    2011-04-01 11:02:42.990 spid22s EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
    2011-04-01 11:02:42.990 spid22s frame procname=mssqlsystemresource.sys.sp_MSrepl_changesubstatus line=888 stmtstart=62560 stmtend=62816 sqlhandle=0x0300ff7fb822203d88cd7c01f39d00000100000000000000
    2011-04-01 11:02:42.990 spid22s exec sys.sp_MSsetfilteredstatus @tabid
    2011-04-01 11:02:42.990 spid22s -- clear nonsqlsub status for this article.
    2011-04-01 11:02:42.990 spid22s frame procname=mssqlsystemresource.sys.sp_changesubstatus line=52 stmtstart=3496 stmtend=5162 sqlhandle=0x0300ff7fa215a108b3a87b01f39d00000100000000000000
    2011-04-01 11:02:42.990 spid22s EXEC @retcode = @cmd
    2011-04-01 11:02:42.990 spid22s @publication,
    2011-04-01 11:02:42.990 spid22s @article,
    2011-04-01 11:02:42.990 spid22s @subscriber,
    2011-04-01 11:02:42.990 spid22s @status,
    2011-04-01 11:02:42.990 spid22s @previous_status,
    2011-04-01 11:02:42.990 spid22s @destination_db,
    2011-04-01 11:02:42.990 spid22s @frequency_type,
    2011-04-01 11:02:42.990 spid22s @frequency_interval,
    2011-04-01 11:02:42.990 spid22s @frequency_relative_interval,
    2011-04-01 11:02:42.990 spid22s @frequency_recurrence_factor,
    2011-04-01 11:02:42.990 spid22s @frequency_subday,
    2011-04-01 11:02:42.990 spid22s @frequency_subday_interval,
    2011-04-01 11:02:42.990 spid22s @active_start_time_of_day,
    2011-04-01 11:02:42.990 spid22s @active_end_time_of_day,
    2011-04-01 11:02:42.990 spid22s @active_start_date,
    2011-04-01 11:02:42.990 spid22s @active_end_date,
    2011-04-01 11:02:42.990 spid22s @optional_command_line,
    2011-04-01 11:02:42.990 spid22s @distribution_jobid OUTPUT,
    2011-04-01 11:02:42.990 spid22s @from_auto_sync,
    2011-04-01 11:02:42.990 spid22s @ignore_distributor,
    2011-04-01 11:02:42.990 spid22s -- Agent offload
    2011-04-01 11:02:42.990 spid22s @offloadagent,
    2011-04-01 11:02:42.990 spid22s @offloadserver,
    2011-04-01 11:02:42.990 spid22s @dts_package_name,
    2011-04-01 11:02:42.990 spid22s @dts_package_password,
    2011-04-01 11:02:42.990 spid22s @dts_package_location,
    2011-04-01 11:02:42.990 spid22s @skipobjectactivation,
    2011-04-01 11:02:42.990 spid22s @distribution_job_name,
    2011-04-01 11:02:42.990 spid22s @publisher,
    2011-04-01 11:02:42.990 spid22s @publisher_type
    2011-04-01 11:02:42.990 spid22s ,@ignore_distributor_failure
    2011-04-01 11:02:42.990 spid22s frame procname=mssqlsystemresource.sys.sp_MSactivate_auto_sub line=92 stmtstart=4236 stmtend=4768 sqlhandle=0x0300ff7f9d993a3bdccd7c01f39d00000100000000000000
    2011-04-01 11:02:42.990 spid22s *sp_changesubstatus---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2011-04-01 11:02:42.990 spid22s inputbuf
    2011-04-01 11:02:42.990 spid22s Proc [Database Id = 32767 Object Id = 993696157]
    2011-04-01 11:02:42.990 spid22s resource-list
    2011-04-01 11:02:42.990 spid22s objectlock lockPartition=0 objid=2043258434 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOffer id=lock2415e2e80 mode=Sch-M associatedObjectId=2043258434
    2011-04-01 11:02:42.990 spid22s owner-list
    2011-04-01 11:02:42.990 spid22s waiter-list
    2011-04-01 11:02:42.990 spid22s waiter id=process3c02bc8 mode=Sch-S requestType=wait
    2011-04-01 11:02:42.990 spid22s objectlock lockPartition=0 objid=2043258434 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOffer id=lock2415e2e80 mode=Sch-M associatedObjectId=2043258434
    2011-04-01 11:02:42.990 spid22s owner-list
    2011-04-01 11:02:42.990 spid22s owner id=process3c03dc8 mode=Sch-M
    2011-04-01 11:02:42.990 spid22s waiter-list
    2011-04-01 11:02:42.990 spid22s waiter id=process5152bc8 mode=IX requestType=wait
    2011-04-01 11:02:42.990 spid22s objectlock lockPartition=0 objid=77347440 subresource=FULL dbid=5 objectname=Adjug.dbo.AdvertiserCampaignOfferSiteAdSpaceBridge id=lock6bf37b300 mode=S associatedObjectId=77347440
    2011-04-01 11:02:42.990 spid22s owner-list
    2011-04-01 11:02:42.990 spid22s owner id=process3c02bc8 mode=Sch-S
    2011-04-01 11:02:42.990 spid22s waiter-list
    2011-04-01 11:02:42.990 spid22s waiter id=process3c03dc8 mode=Sch-M requestType=convert
  2. satya Moderator

    Welcome to the forums.
    Do you see any other errors in event viewer and SQL Server error log at the same time when this DEADLOCKS are happening?
    Do you have any alerting/monitoring in place on SQL Server?
    Just going back to basics, about what can cause deadlocks:
    • Locks
    • Worker threads
    • Memory
    • Parallel query execution-related resources
    • Multiple Active Result Sets (MARS) resources.

    Most of them can be resolve by referring to the queries/stored procedure code that is used by the processes referred in deadlock log. Also modify the code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur.
  3. darknight New Member

    Hi Satya,
    Thanks for your response!!!
    There are no other errors apart from Deadlock and we do have SQL Monitor from Redgate monitoring our system.
    The biggest concern - this code is running for more than 2 years now on SQL Server 2005 without any issues…. and suddenly after migration it started behaving weirdL.
    The secondly problem is every time it’s a new Query… there are no in particular to optimize.
    Also please correct me if I am wrong… My understanding of transaction replication is LogReader Agent scans the transaction log for transactions marked for replication and writes to the distribution DB, then why it’s holding locks on the tables???
    Thanks,
    Viral Shah
  4. satya Moderator

    So did you get a chance to look at SQLMonitor to see if any particular time this deadlock is thrown out.
    In a way you are right that Transaction log is important for Trans.replicaton, see what is defined in BOL :
    Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
    The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
    Incremental changes made at the Publisher flow to Subscribers according to the schedule of the Distribution Agent, which can run continuously for minimal latency, or at scheduled intervals. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur. For more information, see /msdn.microsoft.com/mshelp" />How Updatable Subscriptions Work.Say if the action is read out from Tlog, based on the tasks or calling a stored procedure will have such a problem of deadlock.
    In this case I would recommend you to look at the code that has been running in SQL 2005 without any issues and why its failing in SQL2008. Perform a testing in both 2005 and 2008 instance to see the execution results and process.
  5. darknight New Member

    Hi Satya, Thanks for this... This issue is resolved... One of the Jr. DBA had changed the Snapshot Job to run every hrs, by mistakenly. I have corrected it now… and no issues after thatJ.
  6. satya Moderator

    Hmm... strange relation in causing deadlocks and snapshot schedule [:)], glad to know problem resolved and appreciate your input too.
  7. Hi,
    Just to update on the lock issue
    When snapshot are created a shared lock is created on the table until the snap shot files are created on the folder
    Thanks

Share This Page