3 Replies Latest reply: Jan 26, 2017 1:00 PM by Olly Bond RSS

    Monarch (v13.5) export to .accdb creates multiple TEMP .MDF & .LDF files. Why?

    Janice Gallagher

      Using Monarch (v13.5). When I export to .accdb I noticed that multiple .MDF & .LDF files are being created in App Data\Local\Temp folder. 

       

      For example, today I exported two outputs from Monarch to one Access Database.  When I checked the Temp folder there were 12 MDF/LDF files toaling 440MB.   After closing the Monarch program, if I try to immediately delete any of these TEMP files I get this message:  "File In Use. The action can’t be completed because the file is open in SQL Server Windows NT – 64 Bit. Close the file and try again."   

      Before I contact my IT support, I thought I would ask the Monarch forum -- is this creation of temp files a 'normal' behavior for Monarch?  Any insights would be appreciated.  Thanks in advance.

       

       

        • Re: Monarch (v13.5) export to .accdb creates multiple TEMP .MDF & .LDF files. Why?
          Olly Bond

          Hello Janice,

           

          Normally Monarch should handle the export of data to MDB or ACCDB formats quite gracefully and, like a good house guest, tidy up after itself. Leaving files in TEMP isn't a massive problem - but might be a sign of one.

           

          If Windows thinks that temp file is still owned by SQL Server, then it's because the underlying database engine of Monarch v13, SQL Server, hasn't let go of that temp file yet. That might be just that you've still got a Monarch session running somewhere, and might be cured by a reboot.

           

          But it might be a sign, and as you mention the size of the files I wanted to point this out, that you're trying to pour a gallon into a pint jug. Access can hold up to 2GB of data. If you export one table of 1.1 GB to ACCDB, and then try to export another table of 1.1GB to a new table in that same database, the poor database will stop responding intelligently because it can't cope.

           

          For an in-depth technical analysis of what happens in the underlying data structure, here's a link to a YouTube video: https://www.youtube.com/watch?v=aczPDGC3f8U

           

          So it might be that your first export is working fine, but the second export is too much, and is hanging, leaving the temp files owned by SQL Server because the Monarch job never managed to finish running.

           

          Best wishes,

           

          Olly

            • Re: Monarch (v13.5) export to .accdb creates multiple TEMP .MDF & .LDF files. Why?
              Janice Gallagher

              Greetings Olly,

              Thanks for the reply. And thanks, I think?, for the video link. An injection of humor is always welcome.

              I think we can rule out the size of the Access Database as a possible factor. I’m nowhere near the 2GB max workable zone for Access.

              As I continued testing, I exported Monarch outputs to a new (empty) Access Database.

              I ran two different models against a report in Monarch. 

              The first model produced 8,057 output rows. The second model produced 2,558 output rows.

              I exported the results of each model to the Access Database.

              With each export to Access, a pair of TEMP*  .LDF & .MDF files were created in the C:\Users\...\AppData\Temp folder. 

              I ran the exercise multiple times and with each export another set of .LDF & .MDF files were created in the C:\Users\...\AppData\Temp folder. 

              FWIW, the machine I’m using is a Hosted Virtual Desktop running Microsoft Windows 7 Professional 64-bit, Version 6.1.7601 (Service Pack: 1.0) and Microsoft Office 2013 32-bit.  The Monarch application is Datawatch Monarch Complete v13.5  32-bit.  The error I get when trying to delete the TEMP files refers to the file being open in “SQL Server Windows NT – 64 Bit.”   I submitted my question today to our internal IT support as well because it may be a 'personal problem' on our end.

              Thanks for playing in the Monarch community sandbox with me.