Magento 2.3 or newer
This CLI below was implemented to solve this issue.
php -d memory_limit=-1 bin/magento inventory:reservation:list-inconsistencies -r -c | xargs -L1 php -d memory_limit=-1 bin/magento inventory:reservation:create-compensations
Magento 2.2 or older
This query will return the reservation IDs from the reservations from every order that is either closed, complete or canceled, was created more than 2 weeks ago but has its reservation still different than 0 (which affects the salable quantity on the store, once their reservations were not correctly finished).
SELECT to_be_deleted.reservation_id FROM (
SELECT ir3.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir3.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir3
) to_be_deleted WHERE to_be_deleted.order_id IN (
SELECT entity_id FROM sales_order WHERE entity_id IN (
SELECT msi_info.order_id FROM (
SELECT ir1.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir1.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir1
) msi_info WHERE msi_info.order_id IN (
SELECT msi_info.order_id FROM (
SELECT ir2.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir2.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir2
) msi_info GROUP BY msi_info.order_id HAVING sum(msi_info.quantity)<>0
)
) AND created_at < NOW() - INTERVAL 2 WEEK
);
You might be able to fix it by running this query.
DELETE FROM inventory_reservation WHERE reservation_id IN (
SELECT to_be_deleted.reservation_id FROM (
SELECT ir3.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir3.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir3
) to_be_deleted WHERE to_be_deleted.order_id IN (
SELECT entity_id FROM sales_order WHERE entity_id IN (
SELECT msi_info.order_id FROM (
SELECT ir1.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir1.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir1
) msi_info WHERE msi_info.order_id IN (
SELECT msi_info.order_id FROM (
SELECT ir2.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir2.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir2
) msi_info GROUP BY msi_info.order_id HAVING sum(msi_info.quantity)<>0
)
) AND created_at < NOW() - INTERVAL 1 WEEK AND state IN ('closed', 'complete', 'canceled')
)
);