Monitoring Access DB

[needmoremoney]

I have a very large database on my file server which is used everyday. My problem is that on busy days, the database bloats up to about 950mbs. I need something to send me a warning to my email so when the database hit 900mbs, so that I can compact it back down to about 800mbs. The database does get purged annually though.

Does anyone here know of a way where I can have a warning sent out to me when the database size gets to 900mbs? Let me know, thanks much.

 

[CautionMP]

You can run a routine that will email you from the database when a certain size is reached, but...[ul][li]In most cases Access will notify the user that an email is being sent, and they can cancel the message.[/li][li]Depending on how often users go in and out of the database you could get a lot of emails before you could do anything, and...[/li][li]You can't Compact if there are any users attached to the database.[/li][/ul]

A couple of thoughts.[ul]
[li]Turn the Compact On Close option on, this should compact the database when the last active user closes the database (and I don't think it throws errors for everyone else).[/li]
[li]If you have a computer that is on all the time, have it automatically compact the database every night by using a scheduled task (in Windows with a batch file)[/li][li]Create a macro in Outlook that checks the size of the database file and alerts you if the database is too big ([tt]Startup()[/tt] or [tt]Quit()[/tt] event).[/li][/ul]

Hope this is food for thought,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[RoyVidar]

Here's a faq showing how to check out the size of the (current) db at runtime faq705-2852, here are two faqs on emailing from Access faq702-4509, faq702-2921. Recent posts on the topic CautionMP talks about, the newer security stuff, recommends a (web) search on "outlook object model guard", and/or check out

http://www.dimastr.com/redemption/

for how to avoid this.

Roy-Vidar

 

[mp9]

To keep your db size down, check out the Handy Access Launcher (HAL) for scheduling db compacts and other admin tasks. It's from

http://www22.brinkster.com/accessory/resources/001.shtml

 

[Crowley16]

with such a big dbfile, it might be worth using a more robust database...

--------------------
Procrastinate Now!

 

[needmoremoney]

Thanks for the posts everyone.

I'm using AccessAuto pilot to compact the database everynight. What I was looking for was a "real time" file size monitoring tool to monitor and send me the alerts of the file sizes during the day. Incase the database bloats up to where it could possibly reach the maximun size, I would like to compact it immediately instead of waiting for the nightly compact.

Unfortunately, there is no possible way that the company can move away from using Access to SQL since the data is the core operation of the company.

Any ideas, please let me know.

 

[CautionMP]

Create a macro in Outlook that checks the size of the database file and alerts you if the database is too big (Startup() or Quit() event).

You could use any office application, but If you like most of us you have Outlook open all day, it could periodically check the size of the .mdb file and alert you when it hits a certain size.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[acent]

This may be a dead and burried topic, but nevertheless, I would like to ask two questions:

1. I think we're operating under the assumption the database is split? If not, why not split it? I have a a database that is very small (less than 10mb), but I have been able to compact the backend during the day.

and 2. Why are you purging the database annualy? Is it because of the amount of dzta? If so, I would agree with Crowley16 that a more robust database such as PostgreSQL or MySQL would be a better option than Access.

Hope this helps.

"God is a comedian playing to an audience too afraid to laugh."
-- Francois Marie Arouet (Voltaire)

 

[CautionMP]

Just to illistrate the point, this will start a timer when Outlook opens that every hour checks to see if the file [tt]cstFileName[/tt] gone over 9Mb in size, and if so it will throw up a message box to alert you.

Create a new blank module in Outlook an paste the following code into it (be sure to change the line [tt]Const cstFileName = "C:\pagefile.sys"[/tt]):

Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, _
      ByVal nIDEvent As Long, ByVal uElapse As Long, _
      ByVal lpTimerFunc As Long) As Long

Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, _
      ByVal nIDEvent As Long) As Long

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
               ByVal idEvent As Long, ByVal dwTime As Long)
'Change C:\pagefile.sys to match your database location/name
Const cstFileName = "C:\pagefile.sys"
Dim lngFileSize As Long
lngFileSize = FileLen(cstFileName)
'9Mb * 1,024,000 = 9,216,000,000
If lngFileSize > 9216000000# Then
  MsgBox "The file " & cstFileName & " has reached " & _
         Format(lngFileSize / 1024000000, "##.0") & "Mb in size." _
         , vbInformation, "Status at: " & Now
End If
End Sub

Then in the [tt]ThisOutlookSession[/tt] module paste in the following:

Dim lngTimerID As Long

Private Sub Application_Quit()
lngTimerID = KillTimer(0, lngTimerID)
End Sub

Private Sub Application_Startup()
'3,600,000 will Check every hour
lngTimerID = SetTimer(0, 0, 3600000, AddressOf TimerProc)
End Sub

Close Outlook (Yes to save the modules when prompted) and reopen Outlook.


(GMT-07:00) Mountain Time (US & Canada)

 

[needmoremoney]

Thanks CautionMP,

I'll try to set that up to see if it works. I think some of the other post might have misunderstood what I was looking for or most likely, I was unclear.

The main goal is to just be able to monitor the database size growth during the day.

I'll try to set outlook up and let you know how it works.

Thanks much.

 

[needmoremoney]

Hello CautionMP,

I couldn't get Outlook to work. No alerts appears. I did change the location of the file to C:\pmove.mdb and also set the macro security to low.

Any tips on what I can do to get it working.


I'm using Outlook 2003.

 

[CautionMP]

Is [tt]C:\pmove.mdb[/tt] bigger than 9MB?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[MichaelRed]

Another approach might be to check the normalization of the db. It is somewhat common for larger Ms. A. databases to have a LOT of de-normalized data structures. In particular, orphan tables and fields typically abound. Many are still populated by some function for a purpose which has long been relegated tot he proverbial dustbin.

RE-normalizing can have dramatic effects here.


Another issue is the (often automatic) creation of indicies which are never used. They take both time and storage, thus givine a double hit on your performance.


You might try to analyze the database to achive some leve of partition. Again, many larger Ms. A. apps tend towards the universal panacea approach. The all inclusive front and back ends support all departments at the same time. thus bloating the data structures. Identify the users actual needs, seperate the data into smaller databases which support a group of users. Likewise, seperate the FE functions to the same groups. Individually, each app will be smaller. For the "Heroes", you can link the necessary tables from the multiple BE sets and still achieve the 'seamless' working.

Finally, look into the use of temporary tables (ones which are emptied and re-filled on the fly for whatever reason. Move them to the front end. In formal jargon, data structures are refered to as "persistient" or transient. Transient data (structures) do not need to be saved anywhere, but the founding fathers of Ms. A. deemed that 'split' apps should place ALL data in the BE. Since these are the structures which do typically generate the largeer bloation rates, the need to be removed from the BE (and thus become the local / deskttop problem in the future).




MichaelRed

 

[needmoremoney]

Yes, the db is larger than 9mb.

 

[RoyVidar]

CautionMP - are you testing for ~ 9 216 MB ~ 9 GB?

Is there anything wrong with doing this from within Access? For instance have a form in the FE with a timer? To avoid having multiple e-mails, you could just set a flag in a table in the BE, and check that too in the on timer event ...

Roy-Vidar

 

[CautionMP]

Apparently, I guess Sudafed and math don't mix. Did I at least get the correct number of milliseconds in an hour?

Could it be done from the database? Yes.

Should it be done from the database? I don't know.

I was simply offering another option that keeps the users doing user stuff, and the admin/developer doing admin/developer stuff. In my old world, the more seperation between the developer and user (from a daily operations standpoint) the more productive both were.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[needmoremoney]

Hello all. Thanks for all your time and inputs. I'll try to find a way to monitor the db size.

Thanks again..