Those damn orphans are harder to kill than you think.

Maybe you'll spot the error faster than I did -- or maybe this will help you.

I have a customer with a help desk application created in the mid 1990s. It started causing major issues so I looked into it and found it had grown to over 20gb in size. A check of the database properties showed a whopping 453,355 documents, and of course, many of those have screen shots. When I spoke to the client, she swore she'd deleted everything older than 1/1/2015 and could see only a few thousand documents.

Well, by know you know what happened is that she had been deleting main documents and leaving all the responses as orphans. The application did not have any on-Delete code to clean that sort of thing up. You'd be surprised how many do not.

I decided to write some code that would look at every document in the database, make sure it was not a configuration record, see if it was a response, and if so if the parent document existed. If not, kill the document. I did lots of fancy things with list elements to cache known and unknown unids and things to speed it up, but basically that's what it did. It also was designed to make repeated passes through the database so that it could pick out response-to-response level that became orphaned in the previous pass. Yes, I could have done this form within a view, or by following the chain of parent documents all the way up with each document to avoid repeating the loop, but that has it's own issues as well and I didn't feel like writing a recursive function just for this "simple" task.

The thing is, it didn't work. It kept not finding orphans. It turns out an old and well known problem was manifesting in a whole new way.

To find a parent, I was using code like this (simplified by removing declarations and all the hash based caching to avoid repeated document loads)

if doc.isresponse then
    set parentDoc = nothing ' make sure I don't have an old one still there
    on error resume next ' don't throw errors for bad UNIDs
    set parentDoc = thisdb.getDocumentByUniversalID( doc.parentdocumentunid )
    on error goto errorhandle ' re-establish my normal error handling
    if parentDoc is nothing then
        ' *** Do whatever it is I do to an orphan document ***
    end if
end if

Can you spot why it failed?

Sadly it took me a long time to realize that if the parent formerly existed, there would be a deletion stub. The deletion stub still in the database mean that the "parentDoc" object was still set to a document object, just not a valid one. Testing it to see if it was "Nothing" wouldn't work. After way too many hours, I changed the code to look like this:

if doc.isresponse then
    set parentDoc = nothing ' make sure I don't have an old one still there
    on error resume next ' don't throw errors for bad UNIDs
    set parentDoc = thisdb.getDocumentByUniversalID( doc.parentdocumentunid )
    on error goto errorhandle ' re-establish my normal error handling
    haveParentBoolean = true
    if parentDoc is nothing then
        haveParentBoolean = false
    else
        if not parentDoc.isValid then haveParentBoolean = false
    end if
    if haveParentBoolean = false then
        ' *** Do whatever it is I do to an orphan document ***
    end if
end if

There are two different ways to fail looking up the parent. Either getting nothing at all, or getting an invalid (deleted) document handle. This is very much the same reason why you always have to check for .isvalid when looping through a collection. A deleted document handle is not "nothing", it's just not useable.

The result -- The database size on disk is down from 20gb to 261mb, and from 453,355 documents to 8,110.