Red hand on a lot of transactions

Question

We suddenly have a lot of red hands in "Open vendor transactions". We had weird problems due to a lack of database maintenance plan, now the system is up and running, but too many red hands are showing.

I saw this on MS: http://support.microsoft.com/kb/894412

But this is not very practical as it's not one offending record, but many.

Is there any way to spot marked transactions that should not be, quickly, in transact SQL ?

Is finding orphans in SpecTrans VS VendTransOpen a good start ?

Thanks!

EDIT: Thinking of it, finding orphaned records in SpecTrans probably won't help as a red hand probably means the transaction was found in SpecTrans...

EDIT#2: I found this to unmark certain transactions, but when I do what is being proposed, I cannot find any associated journal. This might be the issue.

Answer 1

After backing up the table, this is what I ended up doing:

delete from SPECTRANS where SPECTRANS.RECID in
(
     select st.RECID from VENDTRANS vt
     inner join VENDTRANSOPEN vto on vto.REFRECID = vt.RECID
     inner join SPECTRANS st on st.REFRECID = vto.RECID
     where st.SPECTABLEID=470 and st.DATAAREAID in ('company1', 'company2')
)