Deleting Unnecessary SQL files from tables

Unknown

Hello,

We have several very large tables in our SQL database and would like to know what we can do determine and delete the unnecessary files. We have one table particularly that is over 20 Gigs named flow_data_delta_storage.

Thanks!

Unknown

Hi,

****IMPORTANT: Backup your database before running these queries.****

Here are some general cleanup scripts that can be run to cleanup unnecessary data in your Decisions SQL tables. Before making any significant changes I do advise that you take a backup as an extra precaution.

Orphan Data Cleanup:

delete from document_dbstorage where document_storage_id not in (select document__storage_id from document)

delete from document_thumbnail_dbstorage where document_thumbnail_storage_id not in (select doc_thumbnail_storage_id from document where doc_thumbnail_storage_id is not null)

delete from element_configuration_data where element_configuration_data.component_config_data_id not in
(select component_registration_data_id from element_registration where component_registration_data_id is not null)

delete from element_registration_data_join where component_registration_id not in (select component_registration_id from element_registration)

delete from output_scenario where component_registration_id not in
(select component_registration_id from element_registration)

delete from output_scenario_data_join where output_scenario_id not in
(select output_scenario_id from output_scenario)

delete from data_declaration where data_declaration_id not in
(
select data_declaration_id from element_registration_data_join where component_registration_id in (select component_registration_id from element_registration)
)
and data_declaration_id not in
(
select data_declaration_id from output_scenario_data_join where output_scenario_id in (select output_scenario_id from output_scenario)
)

delete from flow_state_storage_data where deleted = 1

delete from flow_data_delta_storage where flow_data_delta_storage.primary_flow_tracking_id not in
(select primary_flow_tracking_id from flow_state_storage_data)

delete from flow_step_run_data

delete from job_schedule_log where when_completed is not null and ended_in_error = 0

-If you have turned on Auditing Settings or Client Statisics Settings, youll also want to run the following scripts.
--delete from audit_entity
--delete from client_statistic_summary
--delete from client_statistic

Assignment Cleanup:

update entity_assignment set completed = 1, next_check_time = null, state = FLOW NOT FOUND where completed = 0 and flow_tracking_id is not null and flow_tracking_id not in (select flow_tracking_id from flow_state_storage_data where (deleted = 0 or deleted is null) and (completed = 0 or completed is null) and (ended_in_exception = 0 or ended_in_exception is null))

[i]edited by Brendan@decisions.com on 12/6/2019[/i]
[i]edited by mike.marushia@decisions.com on 12/6/2019[/i]