April 1, 2011 at 5:11 am
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 SETDailyBudget = 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
May 10, 2011 at 6:57 am
I have to admit I have no idea as to your problem - even with the output - what i would say is that there are differences between versions of sql server - I'd check that some of your procs are still running as you expect , usual suspects are poor plans, parallelism, missing indexes.
However it could be something as odd as latency between servers there's a lot of changes in windows 2008 too. Replication usually works in my experience, but always tricky to make a suggestion when you're not there on the ground so to speak.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 10:46 pm
Check this query for the missing Indexes on the primary Server as well as secondary Server on the specific database
Select * from sys.dm_db_missing_index_details
How much RAM have you allocated for the SQL Server and what is showing on Task Mananger of OS about allocated Total RAM
Check this query for the waitResource
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where status !='sleeping'
order by sp.cpu desc
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 9:18 am
We are also getting deadlock while trying to add subscribers. I can't figure it out.
We caught some of the blockers, listed below:
sys.slp_MSRepl_changesubstatus
sys.sp_MSLockMatchID
the command called in the query that was running was sp_addsubscription . we used to be able to add subscriptions without causing massive blocking on our publishers. thankfully, it's not lasting very long, but we tried to add a publisher and it was much worse.
May 11, 2011 at 9:42 am
it sounds as if your box is just running slow - I'd suggest a general health check for any config chnages that might have been missed. You might want to check your network connections for bandwidth - possibly your fc network too. I'd monitor disk read/write latency and network latency - copy some big files and see how long it takes - then do the maths to work out the bandwidth - or use robocopy with logged ouput which will do it for you.
Do you have any monitoring tools you can use?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 9:59 am
our ops team is using SQL Sentry, so they have been able to provide us the deadlocking.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply