Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Good morning. Running SQL 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 < @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

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 statement against 1 record, the SQL process did not complete and became the lead blocker in the process chain. Does anyone have any ideas on what could be causing this behavior? I have looked at the Activity Monitor and there are no other processes which are causing major blockages.

share|improve this question
    
What are the indexes you have on the tables in this job? –  Jeff Jun 22 '11 at 14:22
    
I have non-clustered indexes on the columns in the first update query that I tried to update 1 row from. I re-built all of the indexes but no change in execution. –  Gryphoenix Jun 22 '11 at 15:20
    
What's the wait information for the query? What's the execution plan look like? –  mrdenny Jun 22 '11 at 19:14
    
@Gryphoenix Try to see if there are connections blocking your current transaction. Maybe you have already a long running transaction, or there is a transaction left open. You can find here some help about finding information regarding blocking/blocked processes. –  Marian Jun 22 '11 at 21:15
    
How many rows are deleted? Do you shrink your databases at all? –  gbn Jun 23 '11 at 12:57
show 2 more comments

2 Answers

I would try running a trace with profiler and and checking for blocking deadlocking. Might need to enable some trace flags to see whats really going on.

share|improve this answer
add comment

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 < @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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.