• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

Compacting dbase via unattended scheduled task - DELAYED WRITE FAILED

I will explain what I am doing, then the error I keep recieiving.

I run a series of ACCESS 2000 macros at night and have scheduled them to run using a batch file.  The batch file uses the names of tasks that have been set up in windows task scheduler.

MY BATCH FILE:

::--Start script.cmd---
@echo off
setlocal
::Put your task names in order of execution here.
::Surround names with qoutes. Use space as delimiter.
set tasklist="ImportLVTCData" "Compact Sales" "Import Web Data" "Compact Webdata" "Import Website" "Compact Website" "Compact LVTC1_DATA"


::Main routine
for %%c in (%tasklist%) do (
schtasks /Run /TN %%c
call :loop %%c
)
endlocal
goto :eof


::Task status checker
:loop
:: Pause. If you have sleep.exe
:: or other pause utility, put it
:: here instead of ping command
ping -n 5 127.0.0.1 > nul 2>&1

for /f "tokens=1,3,4 delims=," %%i in ('schtasks /QUERY /FO CSV /NH') do (
    If %%i==%1 (
        set var1=%%~j
        set var2=%%~k))
If "%var1:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
If "%var2:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
echo Task %1 ended.
goto :eof

::--End script.cmd---

Sometimes it works with no problems, but more often than not, I am getting the following error during the task IMPORTLVTCDATA.  

DELAYED WRITE FAILED:
Unable to save all the data to db1.mdb.  All the data will be lost.  May be caused by a failure of hardware or network connection.

. . . when I try to open the dbase it tells me that I cannot open it because it is currently in use by someone else - even if I reboot - and it IS NOT being used by anyone else!!!

This particular task imports 8 *.dbf (dbase4) tables then compacts on close.  I believe that the error is occuring during the compact - this task used to ONLY import the tables followed by another task that would compact the dbase(via the /compact switch) & I used to always get that error during the compact task - now that I compact on close, the error seems to be happening after the importing of the tables.

ACCESS has added a table called: MSysCompactError - it has the following columns:

ErrorCode      ErrorDescription      ErrorRecid      ErrorTable
-1611      Could not find field 'Description'.            INVENTORY
-1611      Could not find field 'Description'.            MSysObjects

. . . under the 3rd column, it lists a row for each table & query in the dbase plus 6 rows with the "MSysObjects"????

The dbase that I am trying to compact is on a network drive (not local), but I am sure that nobody is using it at the time it is being compacted.  I am using XP pro - I recently switched from Win 98 & never had this problem.

Any thoughts on this would be greatly appreciated.

MAx
0
MaxwellTurner
Asked:
MaxwellTurner
  • 14
  • 10
  • 8
  • +1
3 Solutions
 
jadedataMS Access Systems CreatorCommented:
Greetings MaxwellTurner!
  I think this is where you might want to post a 20pt question in the Windows OS TA's to get one of those crew in here.  This reaks to me of a WinXP deal, with a touch of disk write commits interferring in the compact operation.

  Are you specifying Exclusive DB use during these operations?

 Have you considered getting a copy of FMS Inc's  Total Access Agent??  (www.fmsinc.com)  This REALLLLY saves a buttload of effort managing and maintaining Dbs in a shared production environment.  (and it can back up data files WHILE there are users on line!!!!)

regards
jack
0
 
MaxwellTurnerAuthor Commented:
jadedata,

I typed this in the DOS section, then cut & pasted it into here , then into XP then back here - I couldn't decide where the best place was to place it but I thought that the error probably was an ACCESS related error so I ended up here.

Good idea to post alink elsewhere though - I will do that.

Oh yeah, I should probably note that none of my tables, queries, or macros have or mention a field called "description"!

Max
0
 
LucFEMEA Server EngineerCommented:
Hi MaxwellTurner,

>>DELAYED WRITE FAILED<<
This normally indicates that your drive is attached with a 40-wire IDE cable instead of the 80-wire IDE cable which is used nowadays.
I suggest you to check this and replace it if neccesery.

Greetings,

LucF
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Pete LongTechnical ConsultantCommented:
Delayed Write Failed" Error Message When You Manage Files in Windows XP
http://support.microsoft.com/default.aspx?scid=kb;en-us;330174
0
 
LucFEMEA Server EngineerCommented:
Some further reading:

"Delayed Write Failed" Error Message When You Manage Files in Windows XP
http://support.microsoft.com/?kbid=330174

0
 
LucFEMEA Server EngineerCommented:
Hmm, sorry Pete.
0
 
jadedataMS Access Systems CreatorCommented:
I think I should get a little credit for pegging this as a WinXP disk write issue on sheer intuition!!!  :) :)
I'm happy to split with these hardware geeks anyday!

Nice matching Links there boys!!
0
 
LucFEMEA Server EngineerCommented:
:))
0
 
MaxwellTurnerAuthor Commented:
jadedata - EVERYONE GETS POINTS IF I CAN RESOLVE THIS!!!

QUESTION:  The database that I am trying to compact is on a directory on my companies server, not on my local machine.  I assume that the 80 wire cable that I would want to check is on the server not my local machine - not sure though because I think the db1.mdb is trying to be written to my "My Documents" folder.  Also, if I am using a 40 wire cable, does that means that the hard drive is also 40 wire and will have to be upgraded - I have a brand new IBM workstation, so I doubt that it is 40 wire on my machine?  My "Enable write caching on the disk" is turned on.

Max
0
 
LucFEMEA Server EngineerCommented:
In that case, take a look here:
"Delayed Write Failed" Error Message When You Write a File to a Server
http://support.microsoft.com/?kbid=321733
0
 
jadedataMS Access Systems CreatorCommented:
What would be the issues surrounding bringing the data file to be compacted over to the local drive for processing, and then transporting and overwriting at the server after the operation certified correct completion?
0
 
MaxwellTurnerAuthor Commented:
Okay,

The dbase that I am compacting is on F: - our company server - but the error message indicated that the db1.mdb was being written to "C:\Documents and Settings\Maxwell Turner\My Documents" - on my local machine.  

I assume that I should be checking my local machine because that is where the write error is occuring - is that correct?

I just checked & my local machine does have an 80 wire cable - I physically counted 20 & it was about 1/4 the way across so its definitely not 40 pin.

Also, my "Enable write caching on the disk" is ENABLED.  What are the consequenses of turning this off?  Will it really hurt my computers performance?

LucF:

Your link described my probelm exactly - it is for Win 2000 though, I noticed.  Do you figure it will work for XP as well?

Maxwell
0
 
LucFEMEA Server EngineerCommented:
Could be that your version of the page is different than mine (I get a dutch version) and it clearly explains it's for both win2k and winXP to me. But as I've seen several times it could be different, let me know and I'll try to find another one for you.
0
 
jadedataMS Access Systems CreatorCommented:
This is making more sense in light of the way Access will write a temporary copy of a database to a location pending successful completion of the compact.  I don't think this would change even if you brought the file over to the local drive for processing,...
now that I think of it...
0
 
MaxwellTurnerAuthor Commented:
LucF,

Sorry - you were right - I didn't look hard enough.  Sounds like I will have to contact Microsoft for the fix.

BTW - I attempted to change my registry as the workaround describes, but it was already the way it was supposed to be.

Max
0
 
LucFEMEA Server EngineerCommented:
This is just an assumption, but it could be this problem will be fixed in SP2 for winXP, you might want to check if it's allready available for you.
0
 
MaxwellTurnerAuthor Commented:
I have called IBM, who in turn told me to call Microsoft & they actually emailed me the HOT FIX described in the article that LucF recommended (http://support.microsoft.com/?kbid=321733) - Its a zip with 2 Application files - I don't know which one to open so I am currently on-hold with MS for installation instructions.

I am pretty sure that this will work - I will keep you posted.

Max
0
 
LucFEMEA Server EngineerCommented:
Ok, good luck ;-)
0
 
MaxwellTurnerAuthor Commented:
Still on hold . . . . . . .

Max
0
 
MaxwellTurnerAuthor Commented:
I recieved and applied the HOT FIX so I guess when my tasks run tonite, I will know if it was fixed.

Thanks everybody for such prompt replies!!

Max
0
 
LucFEMEA Server EngineerCommented:
Glad to help and let us know the outcome.

(you'll have to delete your pointer in the OS-TA now as it has no use anymore)

Take care,

LucF
0
 
MaxwellTurnerAuthor Commented:
Well,

I didn't work - same error on my machine this morning.

My local machine has a 80 wire cable, but the "Enable write caching on the disk" is ENABLED - I am abit leary to disable this - I don't really want to sacrifice performance even though I have a pretty quick computer (P4 2.8).  I am going to call Microsoft again to see if they have any other ideas before I resort to that.

I have the option also of moving my macros off to another Win 98 box which previously never had these problems.

Max

0
 
LucFEMEA Server EngineerCommented:
Bummer...

In the second link I gave, check the servers registry for that setting, try the workaround there. (maybe you didn't read carefully enough and missed that the workaround had to be performed on the server)
0
 
MaxwellTurnerAuthor Commented:
I have a quick question:

When it is said:

"Ensure that the UDMA hard disk controller is using an 80-wire"

this is refering to the actual cable used to connect the hard drive? Correct?

Max
0
 
LucFEMEA Server EngineerCommented:
Yep, old IDE cables had 40 pins and 40 wires, newer cables have 40 pins and 80 wires where every second wire is only used for electro magnetic shielding because of the high frequencies of signals used through the cable.
0
 
jadedataMS Access Systems CreatorCommented:
Just as a lark,... try setting the Access ODBC time out default higher to allow more time to be able to retrieve data over the network.  I will state that there is no reason this should work, but I would try it were I in your shoes.

or,...
  I use this process for compact/repair/backups
(pseuda
  copy productiondatafile to tempdatafile  (in your case to local drive)
  rename productiondatafile to bakdatafile(appnameyymmddhhnn.mdbbak)
  compact tempdatafile to newproddatafile
  validate bakdatafile (open it and look around a bit)
  validate newproddatafile (open it and look around a bit)
  kill tempdatafile
 

I believe that this is fight between XP wanting to cache to disk immediately and having to wait for data from the network over the lan
am I missing some piece?

0
 
LucFEMEA Server EngineerCommented:
Good point jadedata ;-)

If I'm right, disabling "Enable write caching on the disk" would stop this fight at the cost of some performance.
If yours works it's the best way I guess.

Maybe we make a great team together ;-)

LucF
0
 
MaxwellTurnerAuthor Commented:
LucF,

You are indeed correct - I just ran all my tasks 3x and not a single errer after I disabled "write caching on the disk".  Did not notice any decrease in performance either - if there is any it is very minimal.  Perhaps my computer is fast enuf that I just don't notice it.

Anyways, thanks guys (I am assuming yer guys - take no offence if yer not)!  Its been an adventure!

Max
0
 
LucFEMEA Server EngineerCommented:
You're very welcome Max ;-)

Take care,

LucF
0
 
Pete LongTechnical ConsultantCommented:
jadedata

"Hardware" Geek!!!!!  Im a network Engineer :p
0
 
jadedataMS Access Systems CreatorCommented:
Pete:  Ok a Web Geek!
0
 
Pete LongTechnical ConsultantCommented:
Im aghast I come through to re-read my old comments and find a slur on my otherwise spotless character im so upset Im going to bed g'nite Jack ;)
0
 
jadedataMS Access Systems CreatorCommented:
Your reputation is only appears spotless because it is so evenly covered in blemishes...
nitey nite!
0
 
Pete LongTechnical ConsultantCommented:
ROFL - you appear to know me too well, there aint no blemishes however on my boyish good looks ;)
http://petenetlive.hopto.org/about.htm thats not blemishes, thats a windswept and interesting aura!
0
 
jadedataMS Access Systems CreatorCommented:
OH COOL!!! I love the Stinkclaire!!!!  I have to get a picture of my Tandy MC10 with a killer 4K of RAM!!  oooo and let's not forget that mind-blowing 16K ram expander!!  Love those little chicklet keys!!
0
 
Pete LongTechnical ConsultantCommented:
:)
0
 
LucFEMEA Server EngineerCommented:
And I tought I had some old computers still running :(
I have both and MSX-1&2 the last one with a whopping 128kb of video memmory and a Single Side Double Density diskdrive (3.5")
0
 
Pete LongTechnical ConsultantCommented:
Thats most strange, my Inbox shows a post by ee_ai_construct wonder how that could have happened on a question thats been quiet for over a month? "pete ponders smirkily"
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 14
  • 10
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now