patternMinor
Why did my UPDATE statement not complete?
Viewed 0 times
whyupdatestatementdidcompletenot
Problem
Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the aSyncOperationBase table nightly to save on DB size. Here is the job code:
The problem that I recently noticed is that this job, which starts at 4am, runs for at least 4 hours and does not complete. As I tried run the first update st
Begin Transaction T1
Declare @p30Days as DateTime
Declare @p3Years as DateTime
Set @p30Days = DateAdd(d, -30, GetDate())
Set @p3Years = DateAdd(d, -1095, GetDate())
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon 10
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10
delete from workflowlogbase
where AsyncOperationid in (select AsyncOperationid from AsyncOperationBase where deletionstatecode=2)
update DuplicateRecordBase
set DeletionStateCode = 2
where asyncoperationid in
(select DuplicateRecordBase.asyncoperationid
from DuplicateRecordBase
left join asyncoperationbase on (DuplicateRecordBase.asyncoperationid=asyncoperationbase.asyncoperationid and
asyncoperationbase.deletionstatecode = 0)
where asyncoperationbase.asyncoperationid is null)
update BulkDeleteOperationBase
set DeletionStateCode = 2
where asyncoperationid in
(select BulkDeleteOperationBase.asyncoperationid
from BulkDeleteOperationBase
left join asyncoperationbase on (BulkDeleteOperationBase.asyncoperationid=asyncoperationbase.asyncoperationid and
asyncoperationbase.deletionstatecode = 0)
where asyncoperationbase.asyncoperationid is null)
delete from asyncoperationbase
where deletionstatecode = 2 and
completedon < @p30Days
commit transaction T1The problem that I recently noticed is that this job, which starts at 4am, runs for at least 4 hours and does not complete. As I tried run the first update st
Solution
Check to see if these statements are blocking each other. Since they are in the same transaction. Do they somehow lock conflicting rows?
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon 10
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10Code Snippets
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p30Days
and OperationType <> 10
update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10Context
StackExchange Database Administrators Q#3438, answer score: 2
Revisions (0)
No revisions yet.