Environment: SOI 4.x
We often see ca_ssa_ci_detail, ca_ssa_notebooks etc table may consume lot of disk space, even after shrinking transaction log didn't reduce the overall Db size. In such cases, it should be safe to remove entries for notebooks and sheets that no longer exist. You may first want to get a count, and then do a delete.
If the result sets are very large, you want to make sure that you have enough free disk space for the transaction log while the queries are executing. Once the queries are done you should be able to shrink the database (possibly needs a temporary change to a **** recovery model). Needless to say that before any major change you want to save a backup of the DB. It should be possible to do this with services running since it only affects historic data, but running them with services stopped is preferred.
Select Query
select COUNT(*) from ca_ssa_ci_timestamp where id not in (select id from ca_ssa_ci_detail)
select COUNT(*) from ca_ssa_tags where id not in (select id from ca_ssa_notebooks)
select COUNT(*) from ca_ssa_notebooks_timestamp where id not in (select id from ca_ssa_notebooks)
…
Delete query
delete from ca_ssa_ci_timestamp where id not in (select id from ca_ssa_ci_detail)
delete from ca_ssa_tags where id not in (select id from ca_ssa_notebooks)
delete from ca_ssa_notebooks_timestamp where id not in (select id from ca_ssa_notebooks)