Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    (Preventing) corruption due to no disk space (97+)

    Hi,
    on an (ir)regular basis, we can't save a(ny) file (doc, xls,...) because the network drive is out of memory. Then we have to save our file to our local disk and wait (or do efforts) untill there's enough free memory again.
    Now I wonder if this might be one of the causes of corruption issues with our shared database... and I would like to take some preventive measures.

    One method I could think of is
    (i) set a minimum under which Access can't function properly;
    (ii) check free memory space (*) and intervene at crucial situations.
    In our set up (front-end mde + back-end mdb), I guess these stages could be when users:
    - open database: mention free memory at main menu label or message box;
    - edit or add data (BeforeUpdate event): inform user of memory status, cancel add/edit and ask to try again later.

    Fwiw...
    - as a safe minimum, I would take e.g. 10MB... knowing that remaining disk space can get filled pretty fast. The drive is shared with many hundreds of users... and when the server is getting full, it's often because of a member uploading pictures or whatever large (number of) file(s);
    - this procedure is to be used along with regular compact & repair (as far as possible) and backup;
    - renewal or upgrade of servers is not an issue. Upgrade to informix back-end on another server might be... but may drag on for another couple or months or year...

    Any comments or suggestions?

    Hasse

    (*) I'll probably use the API GetDiskFreeSpaceEx function on the back end's drive.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Preventing) corruption due to no disk space (97+)

    Did you really mean 10 MB? Even on a local hard disk, that is far too little as free space, but on a network drive, it's nothing. 10 GB would be more appropriate.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: (Preventing) corruption due to no disk space (

    Hi, Hans,
    tx for your reply... and I get your point but unfortunately I would be a very happy camper with a few hundred MB at our disposal with a relative certainty ... it's just not in my hands... some people need to learn to work with (almost) nothing <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>.
    FYI: I'm talking here about the network drive where the back-end is located... <font color=blue>[Edit: I guessed 10MB should be enough space for adding or changing a record.]</font color=blue> The local pc's hard drive (in my case >35GB free memory) which harbours the front-end is less a problem.
    Hasse

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Preventing) corruption due to no disk space (

    I don't think I'd want to have a production database on a disk with only 10 MB free space... The operating system needs far more free space to be able to re-write files etc.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: (Preventing) corruption due to no disk space (

    How much would it take... Would five times the actual database size be enough?
    (for clarity's sake... I suppose you are like me talking about the back end 's drive)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Preventing) corruption due to no disk space (

    The problem is that we're talking about a shared network drive, where - I assume - many people will be creating and editing files. With so little free space, somebody would only need to duplicate a not even overly large file to bring the whole thing down because nobody can write anything to the network any more.

    In the various large organizations I've worked for, users have (very) limited disk space in their personal network folder, but disk space for production files is ample. And disk space is very cheap these days.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: (Preventing) corruption due to no disk space (

    Your assumption is right... except that disk space being very cheap these days has no positive effect on the available disk space over here... don't know why :-p.

    I guess I'll make the buffer larger... like 50MB at least (for the backend of about 3-5MB)... which is unlikely to vanish within the time Access needs to save the data after the BeforeUpdate event (right?).

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Preventing) corruption due to no disk space (

    It depends on what other people are doing. But it's better than no check at all.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: (Preventing) corruption due to no disk space (

    Thanks Hans - Hasse

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SOLVED: Preventing corruption due to no disk space

    FYI... my solution...

    1. Public function checks free disk space
    => saved in module basGetDiskSpace

    <code>Option Compare Database
    Option Explicit

    'Sources
    'http://www.kbalertz.com/225144/GetDiskFreeSpaceEx.Retrieve.Drive.Information.aspx
    'Adapted from form_click into Msgbox function: 'http://www.developerfusion.co.uk/show/153/
    'Additional reading: VBA Developers Handbook (Getz & Gilbert, Sybex)

    Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
    End Type

    Private Declare Function GetDiskFreeSpaceEx Lib "kernel32" Alias "GetDiskFreeSpaceExA" (ByVal lpRootPathName As String, lpFreeBytesAvailableToCaller As LARGE_INTEGER, lpTotalNumberOfBytes As LARGE_INTEGER, lpTotalNumberOfFreeBytes As LARGE_INTEGER) As Long

    'CUSTOMISED FUNCTION: checks only available space, uses customised format & MB
    'E.g. MsgBox GetAvailableDiskSpace("D:")
    Public Function fGetAvailableDiskSpace(sDrive As String) As Double
    Dim lResult As Long
    Dim liAvailable As LARGE_INTEGER
    Dim liTotal As LARGE_INTEGER
    Dim liFree As LARGE_INTEGER
    Dim dblAvailable As Double
    If Right(sDrive, 1) <> "" Then sDrive = sDrive & ""
    'Determine the Available Space, Total Size and Free Space of a drive
    lResult = GetDiskFreeSpaceEx(sDrive, liAvailable, liTotal, liFree)

    'Convert the return values from LARGE_INTEGER to doubles
    dblAvailable = CLargeInt(liAvailable.lowpart, liAvailable.highpart)

    'Display the results
    fGetAvailableDiskSpace = Format(dblAvailable / 1024 ^ 2, "#,###.0")
    End Function

    Private Function CLargeInt(Lo As Long, Hi As Long) As Double
    'This function converts the LARGE_INTEGER data type to a double
    Dim dblLo As Double, dblHi As Double

    If Lo < 0 Then
    dblLo = 2 ^ 32 + Lo
    Else
    dblLo = Lo
    End If

    If Hi < 0 Then
    dblHi = 2 ^ 32 + Hi
    Else
    dblHi = Hi
    End If
    CLargeInt = dblLo + dblHi * 2 ^ 32
    End Function
    </code>

    2. Prevent users of adding, editing or deleting data
    => added in module of each form where users can add, modify or delete data

    <code>Private Sub Form_BeforeUpdate(Cancel As Integer)
    If fGetAvailableDiskSpace("G:") < 50 Then '... MB
    MsgBox "Insufficient space on the network (< 50MB): no data entry or changes allowed. Please press 'escape' to cancel and try again later."
    Cancel = True
    End If
    End Sub

    Private Sub Form_Delete(Cancel As Integer)
    If fGetAvailableDiskSpace("G:") < 50 Then '... MB
    MsgBox "Insufficient space on the network (< 50MB): no data deletions allowed. Please press 'escape' to cancel and try again later."
    Cancel = True
    End If
    End Sub</code>


    3. Monitoring... (optional)

    - user form - TimerInterval property & timer event to control the situation every ... minutes and whenever necessary warn the user with a message that the network is running out of memory;
    - main form - text box mentioning the free disk space status, e.g. Source="Free disk space: " & fGetAvailableDiskSpace("G:") & " MB (refresh: F9)"

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: (Preventing) corruption due to no disk space (

    We always used a rule of thumb that we needed to have at least 10% of the drive space free, and really felt better if we had at least 20%. And that assumes you have no huge file that by itself is larger than the amount of free space you have. From what you are saying, I'd strongly suggest you get a bigger server! The cost of the new server will be minor compared to the costs you can incur with problems caused by too little free space.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: (Preventing) corruption due to no disk space (

    Mark... I completely agree... but unfortunately that's in the central management's hands. So I have to (start cleaning out garbage & old files we probably won't use anymore on a regular basis or) deal with the situation as it is...
    Anyway... with the backend being less than 5MB in size, I think the 50MB holds a fair margin.
    Meanwhile, I imported all healthy tables in a new database, an earlier version of the corrupt ones from a backup, assembled the recent input from the intermediate backups cleaned the corrupted records (fortunately not too many as I advised to start registering on paper)... and we're back [img]/forums/images/smilies/smile.gif[/img].
    Thanks for all replies... and take care,
    Hasse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •