BESAuditTrailCleaner mess up database

(imported topic written by JesperLarsen)

… ok im not 100& sure about the Topic, but this is my issue:

Yesterday we were cleaning out the old actions and other old stuff. I was using the BESAuditTrailCleaner-3.0.10.75 agains the database (v. Enterprise 4.37).

Everything seemed to run smooth, and after removing a bunch of data, the fillDB-service started to report ( in FillDB.log) problems like:

Unable to connect to database: Database Error: The action site has not been created

I am pretty sure i have not done anything exceptional… .and yet, this issue rendered the system useless after running the BESAuditTrailCleaner. After a lot of wondering the DB Trace (and the verbose FillDB-logging) showed that a query was made like:

-- select CreationTime from LOCAL_OBJECT_DEFS where ID = 0 
 -- Unable to connect to database: Database Error: The action site has not been created.

Looking at another setup, the “
LOCAL_OBJECT_DEFS where ID = 0” seemed to hold “ActionSite” (the error was complaining about the ActionSite). But on the setup where the
BESAuditTrailCleaner
was executed, no ActionSite existed anymore! My conclusion was that somehow it must have been removed during the cleanup.

To restore it again i simply copied over the data from the Trash Can (being the “
LOCAL_OBJECT_DEFS where ID = 1
”) as that seemed to be identical to the ActionSite with some minor modifications:

INSERT INTO [BFEnterprise].[dbo].[LOCAL_OBJECT_DEFS] ([ID],[IsFixlet],[Version],[ParentID],[CreationTime],[Username],[ContentType],[Name],[Sitename],[Fields],[OriginServerID],[OriginSequence])
select 0,[IsFixlet],1,[ParentID],[CreationTime],[Username],0x00000001,'ActionSite',[Sitename],[Fields],[OriginServerID],0x000000000000081C
from LOCAL_OBJECT_DEFS where ID = 1

And bingo, everything seemed to run again.

I cant seem to reproduce the issue by running the BESAuditTrailCleaner again… so i suppose some kind of inconsistency in the database causes the Cleaner to decide to get rid of the “
LOCAL_OBJECT_DEFS where ID = 0
”.

While looking for a solution for this issue, I stumbled over a few other resources suggesting to rotate the signing-keys, and resign everything… but it did not help.

Im writing this here mostly for reference, but also to give a heads up to verify all logs after running the BESAuditTrailCleaner. …

And last, I was hoping to get some feedback on how to restore the Actionsite-definition … .properly, and not by doing a secret SQL statement!

Kind regards Jesper

(imported comment written by jgstew)

Thanks for the info.

Definitely a good idea to make a fullDB backup before running the audit trail cleaner.