Business Process Output Global Delete - for specific process?

Jaimie Lane
Jaimie Lane
Tenth Anniversary Participant Facilitator 1 Loyalty Badge
in Blackbaud CRM™ General Discussions
We recently discovered using the database growth management tool that a set of export process outputs (running daily) is causing exponential database growth.  However, the Business Process Global Delete (and underlying query) doesn't let you get specific to which export processes you want to globally delete the outputs of.  


While we do globally delete processes over x months old, for several reasons we like to keep most of the exports within the past year.  These few export processes are a problem and right now our only option would be to manually delete the results each day.  Is there any option for global deletion of a specific export process's output?

Thanks!

Jaimie

Comments

  • Satyavathi Atluri
    Satyavathi Atluri
    Tenth Anniversary Name Dropper Participant Facilitator 1
    Jaimie, we have recently been playing around with the database growth management tool to delete business processes and have ran into the same issue that you have specified. There is currently no way within the tool provided by blackbaud to specify certain export processes. I have asked support and they have confirmed that is not a possibility using the tool. I am also investigating a way to delete specific export processes. Please do share what you find out. 


    Thanks!

    Satya
  • Rick Root
    Rick Root
    Tenth Anniversary Kudos 2 Name Dropper Participant
    This may or may not provide you any help, but we have a global change that does something like this:

      

    while 1=1
    BEGIN
    	delete top(100) from BUSINESSPROCESSOUTPUT 
    	where 
    		TABLEKEY='output'  
    		and SUBSTRING(tablename,1,6)='EXPORT'
    		AND DATEADDED < GETDATE()-60
    @ROWCOUNT = 0 BREAK
    END

    You should be able to expand on this to delete specific export processes...


    Here's a query that shows the business process output rows with their related export process

      

    select EP.NAME, BPO.*
    from 
    	BUSINESSPROCESSOUTPUT BPO
    	inner join BUSINESSPROCESSSTATUS BPS on BPO.BUSINESSPROCESSSTATUSID=BPS.ID
    	INNER JOIN EXPORTPROCESS EP ON BPS.BUSINESSPROCESSPARAMETERSETID=EP.ID
    where 
    	BPO.TABLEKEY='output'  
    	and SUBSTRING(BPO.tablename,1,6)='EXPORT'
    	AND BPO.DATEADDED < GETDATE()-60
    Hope you find this helpful!
  • Allen Roth
    Allen Roth
    Tenth Anniversary Kudos 1 Name Dropper Participant
    Perhaps this should be added to the Idea Bank?

Categories