Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Record Locks

0 views

Skip to first unread message

DoubleD

unread,

Jan 10, 2007, 3:26:02 PM1/10/07

Hi, for about 3 weeks now, every ~15 minutes, our CRM Environment becomes
irresponsive due to a few pending processes on the database side:

these are the problem statements: (we had to put a listener to kill any
process waiting for more than 3 minutes just so that the system would respond
to all other requests). Prior to this, everything was working great without
any issues at all. Your help would be greatly appreciated.

SPID 84:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int)insert into #SyncEntry(oid, otc, versionnumber) select accountid, '1',
versionnumber from ( select distinct account.AccountId as
'accountid',account.VersionNumber as 'versionnumber' from Account as account
(NOLOCK) where account.DeletionStateCode in (0) and (account.StateCode = @P1
and account.EMailAddress1 is not null and (account.EMailAddress1 <> @P2 or
account.EMailAddress1 is null )) AND (((account.OwningUser is null)
OR(account.OwningUser = @P3) OR (account.OwningBusinessUnit is not null) OR (
account.AccountId in (select POA.ObjectId from PrincipalObjectAccess POA
(NOLOCK) join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId =
sup.PrincipalId where sup.SystemUserId = @P4 and POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = account.AccountId and otc =
1))) as account

SPID 98:
(@P1 int,@P2 uniqueidentifier,@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int)insert into #SyncEntry(oid, otc, versionnumber) select contactid, '2',
versionnumber from ( select distinct contact.ContactId as
'contactid',contact.VersionNumber as 'versionnumber' from Contact as contact
(NOLOCK) where contact.DeletionStateCode in (0) and (contact.StateCode = @P1
and ((exists (select oid from #SyncEntry where (contact.ParentContactId = oid
and otc = 2) or (contact.AccountId = oid and otc = 1))) or contact.OwnerId =
@P2)) AND (((contact.OwningUser is null) OR(contact.OwningUser = @P3) OR
(contact.OwningBusinessUnit is not null) OR ( contact.ContactId in (select
POA.ObjectId from PrincipalObjectAccess POA (NOLOCK) join
SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId where
sup.SystemUserId = @P4 and POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = contact.ContactId and otc =
2))) as contact

SPID 95:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int)insert into #SyncEntry(oid, otc, versionnumber) select accountid, '1',
versionnumber from ( select distinct account.AccountId as
'accountid',account.VersionNumber as 'versionnumber' from Account as account
(NOLOCK) where account.DeletionStateCode in (0) and (account.StateCode = @P1
and account.EMailAddress1 is not null and (account.EMailAddress1 <> @P2 or
account.EMailAddress1 is null )) AND (((account.OwningUser is null)
OR(account.OwningUser = @P3) OR (account.OwningBusinessUnit is not null) OR (
account.AccountId in (select POA.ObjectId from PrincipalObjectAccess POA
(NOLOCK) join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId =
sup.PrincipalId where sup.SystemUserId = @P4 and POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = account.AccountId and otc =
1))) as account

SPID 81:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int)insert into #SyncEntry(oid, otc, versionnumber) select accountid, '1',
versionnumber from ( select distinct account.AccountId as
'accountid',account.VersionNumber as 'versionnumber' from Account as account
(NOLOCK) where account.DeletionStateCode in (0) and (account.StateCode = @P1
and account.EMailAddress1 is not null and (account.EMailAddress1 <> @P2 or
account.EMailAddress1 is null )) AND (((account.OwningUser is null)
OR(account.OwningUser = @P3) OR (account.OwningBusinessUnit is not null) OR (
account.AccountId in (select POA.ObjectId from PrincipalObjectAccess POA
(NOLOCK) join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId =
sup.PrincipalId where sup.SystemUserId = @P4 and POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = account.AccountId and otc =
1))) as account

SPID 94:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int)insert into #SyncEntry(oid, otc, versionnumber) select contactid, '2',
versionnumber from ( select distinct contact.ContactId as
'contactid',contact.VersionNumber as 'versionnumber' from Contact as contact
(NOLOCK) where contact.DeletionStateCode in (0) and (contact.StateCode = @P1
and contact.EMailAddress1 is not null and (contact.EMailAddress1 <> @P2 or
contact.EMailAddress1 is null )) AND (((contact.OwningUser is null)
OR(contact.OwningUser = @P3) OR (contact.OwningBusinessUnit is not null) OR (
contact.ContactId in (select POA.ObjectId from PrincipalObjectAccess POA
(NOLOCK) join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId =
sup.PrincipalId where sup.SystemUserId = @P4 and POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = contact.ContactId and otc =
2))) as contact

SPID 101:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
uniqueidentifier,@P6 int)insert into #SyncEntry(oid, otc, versionnumber)
select accountid, '1', versionnumber from ( select distinct
account.AccountId as 'accountid',account.VersionNumber as 'versionnumber'
from Account as account (NOLOCK) where account.DeletionStateCode in (0) and
(account.StateCode = @P1 and account.EMailAddress1 is not null and
(account.EMailAddress1 <> @P2 or account.EMailAddress1 is null )) AND
(((account.OwningUser is null) OR(account.OwningUser = @P3) OR
(account.OwningBusinessUnit in (select biz.SubBusinessId from BusinessUnitMap
as biz (NOLOCK) where biz.BusinessId = @P4)) OR ( account.AccountId in
(select POA.ObjectId from PrincipalObjectAccess POA (NOLOCK) join
SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId where
sup.SystemUserId = @P5 and POA.ObjectTypeCode = @P6 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = account.AccountId and otc =
1))) as account

SPID 103:
(@P1 uniqueidentifier,@P2 int,@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
int,@P6 uniqueidentifier,@P7 uniqueidentifier,@P8 int)insert into
#SyncEntry(oid, otc, versionnumber) select activityid, '4201', versionnumber
from ( select distinct appointment.ActivityId as
'activityid',appointment.VersionNumber as 'versionnumber' from Appointment as
appointment (NOLOCK) inner join ActivityParty as appointmentactivityid
(NOLOCK) on (appointment.ActivityId = appointmentactivityid.ActivityId) and
(appointmentactivityid.PartyId = @P1 and
(appointmentactivityid.ParticipationTypeMask <> @P2 or
appointmentactivityid.ParticipationTypeMask is null )) where
appointment.DeletionStateCode in (0) AND (((appointment.OwningUser is null)
OR(appointment.OwningUser = @P3) OR (appointment.OwningBusinessUnit is not
null) OR ( appointment.ActivityId in (select POA.ObjectId from
PrincipalObjectAccess POA (NOLOCK) join SystemUserPrincipals sup (NOLOCK) on
POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @P4 and
POA.ObjectTypeCode = @P5 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1))) AND
(((appointmentactivityid.OwningUser is null)
OR(appointmentactivityid.OwningUser = @P6) OR
(appointmentactivityid.OwningBusinessUnit is not null) OR (
appointmentactivityid.ActivityId in (select POA.ObjectId from
PrincipalObjectAccess POA (NOLOCK) join SystemUserPrincipals sup (NOLOCK) on
POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @P7 and
POA.ObjectTypeCode = @P8 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1))))) AND (not
exists (select oid from #SyncEntry where oid = appointment.ActivityId and otc
= 4201))) as appointment

SPID 99:
(@P1 int,@P2 nvarchar(1),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5
uniqueidentifier,@P6 int)insert into #SyncEntry(oid, otc, versionnumber)
select accountid, '1', versionnumber from ( select distinct
account.AccountId as 'accountid',account.VersionNumber as 'versionnumber'
from Account as account (NOLOCK) where account.DeletionStateCode in (0) and
(account.StateCode = @P1 and account.EMailAddress1 is not null and
(account.EMailAddress1 <> @P2 or account.EMailAddress1 is null )) AND
(((account.OwningUser is null) OR(account.OwningUser = @P3) OR
(account.OwningBusinessUnit in (select biz.SubBusinessId from BusinessUnitMap
as biz (NOLOCK) where biz.BusinessId = @P4)) OR ( account.AccountId in
(select POA.ObjectId from PrincipalObjectAccess POA (NOLOCK) join
SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId where
sup.SystemUserId = @P5 and POA.ObjectTypeCode = @P6 and
((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) AND (not
exists (select oid from #SyncEntry where oid = account.AccountId and otc =
1))) as account

SPID 72:
set implicit_transactions on

SPID 80:
(@P1 nvarchar(6),@P2 nvarchar(7),@P3 nvarchar(9),@P4 nvarchar(7),@P5
nvarchar(9),@P6 nvarchar(7),@P7 nvarchar(10),@P8 nvarchar(7),@P9
nvarchar(7),@P10 nvarchar(10),@P11 nvarchar(8),@P12 nvarchar(7),@P13
nvarchar(7),@P14 nvarchar(6),@P15 nvarchar(5),@P16 nvarchar(6),@P17
nvarchar(7),@P18 nvarchar(18),@P19 nvarchar(8),@P20 nvarchar(6),@P21
nvarchar(6),@P22 nvarchar(14),@P23 nvarchar(7),@P24 nvarchar(7),@P25
nvarchar(8),@P26 nvarchar(8),@P27 nvarchar(10),@P28 nvarchar(24),@P29
nvarchar(4),@P30 nvarchar(5),@P31 nvarchar(5),@P32 nvarchar(8),@P33
nvarchar(7),@P34 nvarchar(5),@P35 nvarchar(10),@P36 nvarchar(7),@P37
nvarchar(15),@P38 nvarchar(15),@P39 nvarchar(14),@P40 nvarchar(7),@P41
nvarchar(8),@P42 nvarchar(8),@P43 nvarchar(18),@P44 nvarchar(7),@P45
nvarchar(5),@P46 nvarchar(11),@P47 nvarchar(17),@P48 nvarchar(7),@P49
nvarchar(7),@P50 nvarchar(8),@P51 nvarchar(10),@P52 nvarchar(4),@P53
nvarchar(7),@P54 nvarchar(6),@P55 nvarchar(5),@P56 nvarchar(6),@P57
nvarchar(7),@P58 nvarchar(5),@P59 nvarchar(6),@P60 nvarchar(7),@P61
nvarchar(9),@P62 nvarchar(17),@P63 nvarchar(13),@P64 nvarchar(6),@P65
nvarchar(5),@P66 nvarchar(8),@P67 nvarchar(9),@P68 nvarchar(7),@P69
nvarchar(7),@P70 nvarchar(5),@P71 nvarchar(9),@P72 nvarchar(4),@P73
nvarchar(4),@P74 nvarchar(7),@P75 nvarchar(6),@P76 nvarchar(5),@P77
nvarchar(7),@P78 nvarchar(5),@P79 nvarchar(6),@P80 nvarchar(21),@P81
nvarchar(5),@P82 nvarchar(8),@P83 nvarchar(6),@P84 nvarchar(10),@P85
nvarchar(4),@P86 nvarchar(6),@P87 nvarchar(7),@P88 nvarchar(7),@P89
nvarchar(7),@P90 nvarchar(5),@P91 nvarchar(11),@P92 nvarchar(9),@P93
nvarchar(10),@P94 nvarchar(9),@P95 nvarchar(10),@P96 nvarchar(6),@P97
nvarchar(8),@P98 nvarchar(8),@P99 nvarchar(4),@P100 nvarchar(5),@P101
nvarchar(16),@P102 nvarchar(9),@P103 nvarchar(6),@P104 nvarchar(7),@P105
nvarchar(6),@P106 nvarchar(8),@P107 nvarchar(7),@P108 nvarchar(10),@P109
nvarchar(7),@P110 nvarchar(7),@P111 nvarchar(5),@P112 nvarchar(5),@P113
nvarchar(11),@P114 nvarchar(11),@P115 nvarchar(9),@P116 nvarchar(5),@P117
nvarchar(7),@P118 nvarchar(15),@P119 nvarchar(11),@P120 nvarchar(17),@P121
nvarchar(6),@P122 nvarchar(4),@P123 nvarchar(8),@P124 nvarchar(5),@P125
nvarchar(6),@P126 nvarchar(16),@P127 nvarchar(8),@P128 nvarchar(4),@P129
nvarchar(11),@P130 nvarchar(6),@P131 nvarchar(8),@P132 nvarchar(11),@P133
nvarchar(5),@P134 nvarchar(7),@P135 nvarchar(6),@P136 nvarchar(6),@P137
nvarchar(10),@P138 nvarchar(21),@P139 nvarchar(12),@P140 nvarchar(7),@P141
nvarchar(10),@P142 nvarchar(12),@P143 nvarchar(9),@P144 nvarchar(8),@P145
nvarchar(8),@P146 nvarchar(15),@P147 nvarchar(7),@P148 nvarchar(12),@P149
nvarchar(11),@P150 nvarchar(5),@P151 nvarchar(9),@P152 nvarchar(18),@P153
nvarchar(8),@P154 nvarchar(29),@P155 nvarchar(5),@P156 nvarchar(8),@P157
nvarchar(9),@P158 nvarchar(6),@P159 nvarchar(11),@P160 nvarchar(5),@P161
nvarchar(6),@P162 nvarchar(6),@P163 nvarchar(9),@P164 nvarchar(8),@P165
nvarchar(8),@P166 nvarchar(4),@P167 nvarchar(5),@P168 nvarchar(19),@P169
nvarchar(7),@P170 nvarchar(6),@P171 nvarchar(12),@P172 nvarchar(6),@P173
nvarchar(6),@P174 nvarchar(7),@P175 nvarchar(20),@P176 nvarchar(14),@P177
nvarchar(7),@P178 nvarchar(3),@P179 nvarchar(10),@P180 nvarchar(7),@P181
nvarchar(9),@P182 nvarchar(7),@P183 nvarchar(13),@P184 nvarchar(5),@P185
nvarchar(5),@P186 nvarchar(6),@P187 nvarchar(8),@P188 nvarchar(10),@P189
nvarchar(7),@P190 nvarchar(6),@P191 nvarchar(11),@P192 nvarchar(7),@P193
nvarchar(10),@P194 nvarchar(7),@P195 nvarchar(7),@P196 nvarchar(10),@P197
nvarchar(7),@P198 nvarchar(16),@P199 nvarchar(8),@P200 nvarchar(5),@P201
nvarchar(11),@P202 nvarchar(13),@P203 nvarchar(8),@P204 nvarchar(4),@P205
nvarchar(6),@P206 nvarchar(6),@P207 nvarchar(10),@P208 nvarchar(4),@P209
nvarchar(10),@P210 nvarchar(8),@P211 nvarchar(8),@P212 nvarchar(8),@P213
nvarchar(5),@P214 nvarchar(12),@P215 nvarchar(8),@P216 nvarchar(21),@P217
nvarchar(5),@P218 nvarchar(15),@P219 nvarchar(7),@P220 nvarchar(14),@P221
nvarchar(9),@P222 nvarchar(10),@P223 nvarchar(5),@P224 int,@P225
nvarchar(6),@P2

SPID 76:
(@P1 nvarchar(6),@P2 nvarchar(7),@P3 nvarchar(9),@P4 nvarchar(7),@P5
nvarchar(9),@P6 nvarchar(7),@P7 nvarchar(10),@P8 nvarchar(7),@P9
nvarchar(7),@P10 nvarchar(10),@P11 nvarchar(8),@P12 nvarchar(7),@P13
nvarchar(7),@P14 nvarchar(6),@P15 nvarchar(5),@P16 nvarchar(6),@P17
nvarchar(7),@P18 nvarchar(18),@P19 nvarchar(8),@P20 nvarchar(6),@P21
nvarchar(6),@P22 nvarchar(14),@P23 nvarchar(7),@P24 nvarchar(7),@P25
nvarchar(8),@P26 nvarchar(8),@P27 nvarchar(10),@P28 nvarchar(24),@P29
nvarchar(4),@P30 nvarchar(5),@P31 nvarchar(5),@P32 nvarchar(8),@P33
nvarchar(7),@P34 nvarchar(5),@P35 nvarchar(10),@P36 nvarchar(7),@P37
nvarchar(15),@P38 nvarchar(15),@P39 nvarchar(14),@P40 nvarchar(7),@P41
nvarchar(8),@P42 nvarchar(8),@P43 nvarchar(18),@P44 nvarchar(7),@P45
nvarchar(5),@P46 nvarchar(11),@P47 nvarchar(17),@P48 nvarchar(7),@P49
nvarchar(7),@P50 nvarchar(8),@P51 nvarchar(10),@P52 nvarchar(4),@P53
nvarchar(7),@P54 nvarchar(6),@P55 nvarchar(5),@P56 nvarchar(6),@P57
nvarchar(7),@P58 nvarchar(5),@P59 nvarchar(6),@P60 nvarchar(7),@P61
nvarchar(9),@P62 nvarchar(17),@P63 nvarchar(13),@P64 nvarchar(6),@P65
nvarchar(5),@P66 nvarchar(8),@P67 nvarchar(9),@P68 nvarchar(7),@P69
nvarchar(7),@P70 nvarchar(5),@P71 nvarchar(9),@P72 nvarchar(4),@P73
nvarchar(4),@P74 nvarchar(7),@P75 nvarchar(6),@P76 nvarchar(5),@P77
nvarchar(7),@P78 nvarchar(5),@P79 nvarchar(6),@P80 nvarchar(21),@P81
nvarchar(5),@P82 nvarchar(8),@P83 nvarchar(6),@P84 nvarchar(10),@P85
nvarchar(4),@P86 nvarchar(6),@P87 nvarchar(7),@P88 nvarchar(7),@P89
nvarchar(7),@P90 nvarchar(5),@P91 nvarchar(11),@P92 nvarchar(9),@P93
nvarchar(10),@P94 nvarchar(9),@P95 nvarchar(10),@P96 nvarchar(6),@P97
nvarchar(8),@P98 nvarchar(8),@P99 nvarchar(4),@P100 nvarchar(5),@P101
nvarchar(16),@P102 nvarchar(9),@P103 nvarchar(6),@P104 nvarchar(7),@P105
nvarchar(6),@P106 nvarchar(8),@P107 nvarchar(7),@P108 nvarchar(10),@P109
nvarchar(7),@P110 nvarchar(7),@P111 nvarchar(5),@P112 nvarchar(5),@P113
nvarchar(11),@P114 nvarchar(11),@P115 nvarchar(9),@P116 nvarchar(5),@P117
nvarchar(7),@P118 nvarchar(15),@P119 nvarchar(11),@P120 nvarchar(17),@P121
nvarchar(6),@P122 nvarchar(4),@P123 nvarchar(8),@P124 nvarchar(5),@P125
nvarchar(6),@P126 nvarchar(16),@P127 nvarchar(8),@P128 nvarchar(4),@P129
nvarchar(11),@P130 nvarchar(6),@P131 nvarchar(8),@P132 nvarchar(11),@P133
nvarchar(5),@P134 nvarchar(7),@P135 nvarchar(6),@P136 nvarchar(6),@P137
nvarchar(10),@P138 nvarchar(21),@P139 nvarchar(12),@P140 nvarchar(7),@P141
nvarchar(10),@P142 nvarchar(12),@P143 nvarchar(9),@P144 nvarchar(8),@P145
nvarchar(8),@P146 nvarchar(15),@P147 nvarchar(7),@P148 nvarchar(12),@P149
nvarchar(11),@P150 nvarchar(5),@P151 nvarchar(9),@P152 nvarchar(18),@P153
nvarchar(8),@P154 nvarchar(29),@P155 nvarchar(5),@P156 nvarchar(8),@P157
nvarchar(9),@P158 nvarchar(6),@P159 nvarchar(11),@P160 nvarchar(5),@P161
nvarchar(6),@P162 nvarchar(6),@P163 nvarchar(9),@P164 nvarchar(8),@P165
nvarchar(8),@P166 nvarchar(4),@P167 nvarchar(5),@P168 nvarchar(19),@P169
nvarchar(7),@P170 nvarchar(6),@P171 nvarchar(12),@P172 nvarchar(6),@P173
nvarchar(6),@P174 nvarchar(7),@P175 nvarchar(20),@P176 nvarchar(14),@P177
nvarchar(7),@P178 nvarchar(3),@P179 nvarchar(10),@P180 nvarchar(7),@P181
nvarchar(9),@P182 nvarchar(7),@P183 nvarchar(13),@P184 nvarchar(5),@P185
nvarchar(5),@P186 nvarchar(6),@P187 nvarchar(8),@P188 nvarchar(10),@P189
nvarchar(7),@P190 nvarchar(6),@P191 nvarchar(11),@P192 nvarchar(7),@P193
nvarchar(10),@P194 nvarchar(7),@P195 nvarchar(7),@P196 nvarchar(10),@P197
nvarchar(7),@P198 nvarchar(16),@P199 nvarchar(8),@P200 nvarchar(5),@P201
nvarchar(11),@P202 nvarchar(13),@P203 nvarchar(8),@P204 nvarchar(4),@P205
nvarchar(6),@P206 nvarchar(6),@P207 nvarchar(10),@P208 nvarchar(4),@P209
nvarchar(10),@P210 nvarchar(8),@P211 nvarchar(8),@P212 nvarchar(8),@P213
nvarchar(5),@P214 nvarchar(12),@P215 nvarchar(8),@P216 nvarchar(21),@P217
nvarchar(5),@P218 nvarchar(15),@P219 nvarchar(7),@P220 nvarchar(14),@P221
nvarchar(9),@P222 nvarchar(10),@P223 nvarchar(5),@P224 int,@P225
nvarchar(6),@P2

0 new messages