Tag Archives: Batch history AX 2012

Byadmin

Clear BatchHistory from SQL – AX 2012

Many a times, we are faced with a scenario that Batch History is stuffing up the DB and sometimes becoming a DB overhead. So to keep the DB size in check and getting rid of the unnecessary Batch History that is more than 10/20/30 days (whatever project decides) old. We can schedule the Job to run on SQL every midnight so that the DB is least effected in terms of performance for Business use.

Here is the SQL Job code:

DECLARE @Count INT

Declare @for_delete INT

Declare @chunk_size INT

SELECT @chunk_size=1000

SELECT @Count = 0

select @for_delete=count(*) from BATCHJOBHISTORY

where BATCHJOBHISTORY.CREATEDDATETIME < (GETDATE()-10<<Number of Days>>)

While (@Count < @for_delete)

BEGIN

SELECT @Count = @Count + @chunk_size

BEGIN TRAN

delete top(@chunk_size) from BATCHJOBHISTORY

where BATCHJOBHISTORY.CREATEDDATETIME < (GETDATE()-10<<Number of Days>>)

delete BATCHHISTORY

where not exists (select RECID from BATCHJOBHISTORY

where BATCHJOBHISTORY.RECID = BATCHHISTORY.BATCHJOBHISTORYID)

delete BATCHCONSTRAINTSHISTORY

where not exists (select RECID from BATCHHISTORY

where BATCHHISTORY.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)

COMMIT TRAN

END