SnTT: tracking down space hogs in Notes

You all have them. People who keep every stinking e-mail they ever received for the past 10 years. They can't possibly delete that e-mail response that simply says "thanks" because it would destroy their CYA audit trail. Your storage budget looks like the national deficit so finally management is asking why you need that much space. You want to give them a clean report showing who is using their mail as a vast garbage dump.

Creating a Notes agent to loop through the mail directory, open each database, and extract the space used and percent free is trivial. But on your server with 1000+ mail databases and dozens or hundreds that are over a gig, it takes a while to run and bogs down the server. So what can you do?

Enter the Lotus C API. Notes doesn't use the same entry point you do when it's working with databases. Notes uses the spiffy C API, which runs at a lower level. LotusScript code has to be interpreted, so there is some lag. And once it's interpreted it ends up calling the C API. You can bypass the middle man and hit the C API directly.

In this case you only need three C API calls: NSFDbOpen, NSFDbClose and NSFDbSpaceUsed. Add in a NotesDBDirectory and a loop and you're good to go. The following will create documents and put them into a view that has the first column set to "# in view". The second column is the dbsize. It then walks the view to write the position in the view to each record.

(Declarations)
Declare Function NSFDbOpen Lib "nnotes.dll" Alias "NSFDbOpen" (Byval dbName As String, hDb As Long) As Integer
Declare Function NSFDbClose Lib "nnotes.dll" Alias "NSFDbClose" (hDb As Long) As Integer
Declare Function NSFDbSpaceUsage Lib "nnotes.dll" Alias "NSFDbSpaceUsage" (ByVal hDB As Long, retAllocatedBytes As Long, retFreeBytes As Long) As Integer

Sub Initialize
    Dim s As New NotesSession
    Dim rdoc As NotesDocument
    Dim mfile As String
    Dim success As Variant
    Dim pmail As String
    Dim dbdir As New NotesDbDirectory("server/domain")
    Dim db As NotesDatabase
    Dim thisDb As NotesDatabase
    Dim view As NotesView
    Dim nvec As NotesViewEntryCollection
    Dim eOne As NotesViewEntry
    Dim eTWo As NotesViewEntry
    Dim dbHandle As Long
    Dim usedBytes As Long
    Dim freeBytes As Long

    'Using NotesDBDirectory gives us a handle to the database
    'and limited information about it. The rest of the
    'information, such as PercentUsed, won't be populated until
    'db.Open is called, which we don't want to do because
    'that's what drags the server down. Instead we'll combine
    'information from the closed database and some Notes C API
    'calls to get the specific information we want.

    Set db = dbdir.GetFirstDatabase(DATABASE)

    While Not db Is Nothing
         mfile = db.FilePath
         'Only get databases in the mail subdirectory
         If Left$(mfile, 4) = "mail" Then
              'Get a handle to the database. The NotesDatabase object
              'has a LotusScript handle, we need a C API handle.

              Call NSFDbOpen ("server/domain!!" + mfile, dbHandle)
              If dbHandle <> 0 Then
                   'Peek inside and get the used bytes and free bytes
                   Call NSFDbSpaceUsage(dbHandle, usedBytes, freeBytes)
                   'We have what we need so close the C API handle to prevent a memory leak
                   Call NSFDbClose(dbHandle)
              End If

              Set rdoc = New NotesDocument(s.CurrentDatabase)
              rdoc.form = "EmailRecord"
              rdoc.dbFilename = FilePath
              rdoc.title = db.title
              rdoc.mailfile = db.FilePath
              rdoc.dbsize = db.size
              rdoc.pctused = Round((usedBytes / db.size, 2) * 100
              rdoc.server = db.Server
              rdoc.Username = db.Title
              Call rdoc.Save(True,False)
              mfile = ""
         End If

         Set db = dbdir.GetNextDatabase
    Wend

    ' Next we walk the all docs view and write the user's ranking to their document
    Set thisDB = s.CurrentDatabase
    Set view = thisDb.GetView("AllDocs")

    Set nvec = view.AllEntries
    Set eOne = nvec.GetFirstEntry
    Do Until eOne Is Nothing
        Set doc = eOne.Document
        Print "On doc " + Cstr( eOne.GetPosition("."))
        doc.Ranking = Cstr( eOne.GetPosition("."))
        Call doc.Save(True, False)
        Set eTwo = eOne
        Set eOne = nvec.GetNextEntry( eTwo )
    Loop
End Sub

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

[UPDATED 11/5/2009 9:25 AM to include the NSFDbClose code to prevent a memory leak.]