HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Why did my UPDATE statement not complete?

Submitted by: @import:stackexchange-dba··
0
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:

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 T1


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

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 = 10

Code 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 = 10

Context

StackExchange Database Administrators Q#3438, answer score: 2

Revisions (0)

No revisions yet.