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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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 .
FYI: I’m talking here about the network drive where the back-end is located… [Edit: I guessed 10MB should be enough space for adding or changing a record.] The local pc’s hard drive (in my case >35GB free memory) which harbours the front-end is less a problem.
Hasse

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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?).

Reply | Quote

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

Reply | Quote

Thanks Hans – Hasse

Reply | Quote

FYI… my solution…

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

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


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

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

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)”

Reply | Quote

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.

Reply | Quote

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 .
Thanks for all replies… and take care,
Hasse

Reply | Quote