Hello,
I have a problem that I hope someone can help me with. I have an extremely large database with about 2.5 million records. I know, I know. I can hear you all now. Listen tho, they are extremely small, imported records with 2-3 fields on them. I didn’t create this db, but I inherited it.
Now, each night new import files are created from another system. There are several agents that run nightly that each take one of these import files, deletes all of the associated records in the notes database, then imports the new set. Fun huh?
The imports don’t take long. The box is a beast and even full text indexing the database takes under an hour.
My problem is this: the database size grows by about 500 meg a week, but the document count doesn’t change. I have a compact -B that runs weekly on the database (agents are not set to run on weekends). The log tells me it reduced the size, yet the database continues to grow. View index sizes do not change. It’s gotten to the point that monthly I have to create a new db, run all of the imports, delete the existing db and start from scratch. The reason for this is that it has reached 12 gig and gone completely corrupt with an endless consistency check.
This is more maintenance then I wish to do. Does anyone know why the database continues to grow? Does it have something to do with whitespace? The agents are deleting records nightly, so you would think this would reduce the size of the database, but I am suspecting that this nightly import process is causing the database to grow in size. If these agents do not run the db size stays the same, but obviously we need these agents.
I have included code from one of the agents. They are all exactly the same except for the type of records it is deleting and creating.
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim dcResp As NotesDocumentCollection
Dim doc As NotesDocument
Dim docResp As NotesDocument
Dim item As NotesItem
Dim view As NotesView
Dim nn As NotesName
Dim k As Long
Dim i As Integer
Dim isMod As Integer
Dim ss As String
Dim dateTime As New NotesDateTime(“01/01/1950”)
Dim con As New ODBCConnection
Dim query As New ODBCQuery
Dim result As New ODBCResultSet
Dim dataSourceName$
Dim dbUserID$
Dim password$
Dim display$
Dim conStatus As Integer
Dim resultStatus As Integer
'connect to the ODBC database
dataSourceName$ = “Avl”
conStatus = con.ConnectTo (dataSourceName$)
'check for connection errors
If conStatus = False Then
Msgbox con.GetExtendedErrorMessage
Exit Sub
End If
'prevent display of dialog boxes
con.SilentMode = True
'********************************************************
'setup the key query for AVL
Set query.Connection = con
query.SQL = “SELECT * FROM EXPAVL”
'catch result set errors
On Error Goto trapError
result.cacheLimit = 275
result.FetchBatchSize = 200
'generate the results set
Set result.Query = query
resultStatus = result.Execute
Set db = s.CurrentDatabase
'clear the db of existing records first
ss = “Form = ““avl”””
Set dc = db.Search(ss, dateTime,0)
Call dc.RemoveAll(False)
Do
result.NextRow
k = k+1
'Print "Importing document " & k
Set doc = New NotesDocument (db)
'doc.Reference = result.GetValue(“ProblemNumber”)
doc.AcctCode = result.GetValue(“ACCT_CODE”)
doc.InternalNo = result.GetValue(“INTERNALNO”)
doc.Mfg = result.GetValue(“MFG”)
doc.form = “avl”
doc.PartNo = result.GetValue(“PARTNO”)
Call doc.Save (True, True)
Loop Until result.IsEndOfData
result.Close (DB_CLOSE)
'catch other errors
On Error Goto trapError
Print “Done”
Exit Sub
trapODBCError:
Msgbox result.GetExtendedErrorMessage & " (" & Erl & “)”
Exit Sub
trapError:
Msgbox Error & " (" & Erl & “)”
Exit Sub
End Sub