Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Replication (2008)
 Transaction Replication Deadlocks

Author  Topic 

darknight
Starting Member

4 Posts
Posted - 2011-04-01 : 06:54:26

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

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-04-01 : 12:35:16
Have you looked at your topic? It's ENORMOUS! Seriously fix it so that we can help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

darknight
Starting Member

4 Posts
Posted - 2011-04-01 : 12:55:18
Hi Tara,

I am not sure if I got you correctly... I am new forum.

If you are refering to DeadLock and why I have posted it under Replication Head then I believe this is a issue related to Replication and thts the reason behind it... if the moderator feels that this is not the right place to discuss this then please change it to correct head.

Thanks - Viral
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-04-01 : 13:03:23
I am referring to your enormous font. It is not readable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

darknight
Starting Member

4 Posts
Posted - 2011-04-01 : 13:34:52
My apologies... reposting it again.

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-04-01 : 13:39:48
Can you implement READ_COMMITTED_SNAPSHOT isolation level?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

darknight
Starting Member

4 Posts
Posted - 2011-04-02 : 06:21:19
Hi All,

Its resolved... The junior DBA has changed the schedule of snapshot agent to run every hour...

Once again thanks to every one for all the help!!!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-04-02 : 12:08:57
Why are you even having to do a snapshot? What changes are you making so frequently that cause a snapshot to be needed?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site