record database file size every half hr 1

[JaredTaylor74]

hey guys,
I have a database that keeps bloating over 500%, but not all the time. some days it stays the same size for 3 or 4 days then one day it jumps from 89M to 470M. since there is no accurate way to see the table space of tables or the amount of bytes they are using, I am looking to narrow the time of when this happens. I'd like to have a module that every half hr it goes out and records the file size and puts the date/time and size in a table. this can either be in the database itself or in a seperate one, doesn't matter.
problem is, I am a novice at building code from scratch. once it's there, i can fuddle my way through it.
anyway, if anyone can point me in the right direction, I would appreciate it. (this database is used 24/7)

Thanks for your help,
Jared

 

[drctx]

create a table called dbFilesize with a time/date field named Timestamp and a text field named Filesize.

create a new form, set the timer interval to 1000, add the code below to the on timer event:

Dim fs, f
Dim dbPath As String, dbName As String, dbPathName As String
Dim myTIME As String

' format the time to 24hr 0030 and get just the minutes
myTIME = Right(Format(Time(), "hhmm"), 2)
If myTIME = "30" Then
'path to database c:\mydb
dbPath = Application.CurrentProject.Path
'database name mydbname.mdb
dbName = Application.CurrentProject.Name
dbPathName = dbPath & "\" & dbName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(dbPathName)

'append the date/time and filesize converted to MB
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO dbFilesize ( [Timestamp], Filesize ) SELECT '" & Now() & "' AS Expr1, '" & (f.Size / 1048576) & " MB' AS Expr2;"
DoCmd.SetWarnings True
End If
create a macro named autoexec to open the form in the hidden window mode.

 

[JaredTaylor74]

drctx,

Thank you for the perfect solution. it works great. the only thing i changed was the timer... at 1000, it was recording about 100 records or so, so i changed it to 60000, so i only get 1 record each time.
but that was easy.
one quick question though... why "1048576" on the file size? what does that do?

Thanks,
Jared

 

[PHV]

1048576 = 1024 * 1024

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JaredTaylor74]

ahhh... eye sea...

thanks PHV

later,
Jared