I was mistaken about above, that was a good container, the bad container in question, the numbers are off.
instances_snapshots has a count of 36, while the storage_volumes_snapshots has a count of 32.
I looked at the outputs:
SELECT vs.* FROM storage_volumes AS v INNER JOIN storage_volumes_snapshots AS vs ON v.id = vs.storage_volume_id WHERE v.name = 'container-name';
SELECT vs.* FROM instances AS v INNER JOIN instances_snapshots AS vs ON v.id = vs.instance_id WHERE v.name = 'container-name';
And determined by name which ones don’t match:
SELECT i.id
FROM
(SELECT vs.* FROM instances AS v INNER JOIN instances_snapshots AS vs ON v.id = vs.instance_id WHERE v.name = 'container-name') AS i
LEFT JOIN
(SELECT vs.* FROM storage_volumes AS v INNER JOIN storage_volumes_snapshots AS vs ON v.id = vs.storage_volume_id WHERE v.name = 'container-name') AS v ON i.name = v.name
WHERE v.name IS NULL;
Which output numbers 98,94,149,945
Then I did:
lxd sql global "DELETE FROM instances_snapshots WHERE id IN(98,94,149,945)"
and was then able to take a snapshot of the container.