13 Replies Latest reply on Oct 15, 2012 8:46 AM by BenHutson

    Delete field in Server taking days?

    farokh

      I have a database with ~400,000 records. Each record contains a small number of text/numeric/date fields and two container fields. One is an older style self contained container (the image is stored in the database) and the other field is the new reference model (the image is stored in an external file). I told FM to delete the old style container field last Monday in the morning, and it's still running at this time (we're talking almost 48 hours). The FM that client I used to delete the field simply has a dialog box that says "Records remaining to update" with a horizontal barber pole. No number, nothing.

       

      Any way to tell how much longer this is going to run? It's really causing a problem because we are unable to make any changes to the database. We can view records, but no changes can be made. We get a dialog that "? is making changes to the record" (yes that's a ?).

       

      Thanks!

        • 1. Re: Delete field in Server taking days?
          mikebeargie

          Sounds pretty frozen to me, surprised the client side hasn't timed out and force-quit.

           

          I would:

           

          -close the database from the server.

          -Make a backup copy,

          -open it with FMpro advanced using the "recover -> save as compacted file" utility, or some more advanced scanning.

          -Then if the container isn't already deleted, delete the container at that time.

          -Close from FMPro,

          -reupload to server,

          -open for hosting,

          -test connections.

           

          If it still gives you trouble deleting the field in pro. You might want to do an empty replace on the container field to clear out the contents of the database prior to deleting.

          • 2. Re: Delete field in Server taking days?
            Mike_Mitchell

            It's generally not a good idea to make large-scale schema changes on a hosted database, especially if you let users access the thing during that time.

             

            Mike is probably right. I would add you might be safer just grabbing a backup copy than trying to use the one that's frozen - unless it doesn't give you any warning messages on opening.

             

            Mike

            • 3. Re: Delete field in Server taking days?
              farokh

              When I started the delete, no one was using it (Monday was a holiday here). The only client connected was the one that I was using to modify the database. So it ran for 24 hours with no one else connected.

               

              I did make a backup of the database, so I'll try it again with that, but at 100+ gigabytes, it takes a non-trivial amount of time to work with it and I have to do it when the office is closed.

              • 4. Re: Delete field in Server taking days?
                DavidJondreau

                If you were doing the delete hosted, then yes, this delete will take forever. Generally, everything has to come down to the client (and back up to the server? not sure). If your each record has a file in the container field, and each file is only a small 100KB image, then that's 40 GB coming down (and maybe going up). That's a lot. And if the files are larger...the load is proportionally larger. I'm pretty sure this applies when you're manipulating schema (but I'm not 100% sure).

                 

                I don't know how you're hosting it, but another solution is to install FM Pro on the server itself (through the machine if you have physical access or remote desktop if you don't). Connect to the hosted file via remote (but "locally") and do your delete that way. You can also do your "Save as Compacted..." and other maintenance that way too. Saves you from having to download the file, etc.

                 

                Other things you may want to try is changing the container field to a global, or a calculation with no result and exiting Manage Database. That may be faster (though that's just a guess). Mike B's suggestion of a Replace[] is a good idea, you may want to script it to only do batches of 5K at a time to reduce the chance of a hang.

                • 5. Re: Delete field in Server taking days?
                  farokh

                  OK, I'll try to cover all the comments in this one reply.

                   

                  Trying to recover the database is a non-starter. I did that a while ago when FileMaker techs recommended that when I was having crashing problems with Server and Client. It took something like 3 DAYS to recover the file. I used a backup copy and did my changes over again. But, the database wasn't forzen, you just could do any updates while Server was trying to delete the field.

                   

                  What I found, after spending much of the night trying to get things working, is that if I do anything that is a one step in FM client, it will mean that Server will be stuck, potentially for hours, doing that one step. IE If I try to use Replace to get rid of the data in the container, even for just a few thousand records, Server simply bogged down trying to do that step. Any time I make a change to a field's definition, it takes Server a long time (15-30 minutes) to actually make the change.

                   

                  As for network traffic when using FM Client and Server, there doesn't appear to be much, at all. Definately not the amount of traffic you would expect to see if each record was actually going from Server to Client. That said, the machines are connected via gigabit ethernet.

                   

                  After some trial and error, I decided that a script that steps through each record and deletes the image in the container is the safest way to go. I can't afford to have the database down for days while Server tries to slog through the 400,000+ records. At least with the script, I can stop it at any time it causes a problem and restart it again later.

                   

                  I'm going let it run for about 20,000 records and then close the file tonight and see if it frees up any space. Otherwise I'll try further in relatively small chunks. I don't want to get stuck where Server is hung trying to close the file because it's attempting to compact all 400,000 records. I'm also deleting the images from the last record backward, in case that helps with the compaction any, but I doubt it.

                   

                  Thanks.

                  • 6. Re: Delete field in Server taking days?
                    taylorsharpe

                    Deletes can take a long time since FileMaker updates the index after each record is deleted.  Nothing can be done about that easily.  But I have had files with many millions of records and didn't have time for this.  So what I did was I went into the database, turned off indexing on all fields, and ran a server side script to delete everything.  Aftewards, I turned indexing back on for fields that needed it.  Also, I never run this big jobs on a client.  I always create a script and and schedule it in the admin console to run on the server silently.  If I want a progress update, I can make the deletes a loop and have it send me an email or text message every 10,000 records or something like that so I know it is working. 

                    • 7. Re: Delete field in Server taking days?
                      BenHutson

                      Hi Taylor,

                       

                      Interested that you say 'send ... a text message', what is your current solution for sending a text message from FileMaker?

                       

                      There are several uses we could make of texts, and I've investigated a few options, but there's been nothing I found that great. Be interested to hear what others are using.

                       

                      Apologies for hi-jacking the thread.

                       

                      Best Regards,

                       

                      Ben

                      • 8. Re: Delete field in Server taking days?
                        taylorsharpe

                        Hey Ben... always good hearing from you.  The easy way to do text messages here in the US is by sending a preformatted email to the cell phone company that then resends the text message to your cell phone.  You have to know the recipients service provider.  Mine is AT&T.  The format for AT&T is to have FileMaker send an email in the format of 1234567890@txt.att.net where the numbers is the cell phone's phone with area code and no dashes or periods.  Verizon's format is 1234556890@vtext.com, T-Mobile's is 123456890@tmomail.net.  I realize this doesn't help you in the UK, but I'm sure your cell phone companies have something similar. 

                        • 9. Re: Delete field in Server taking days?
                          BenHutson

                          Hi Taylor,

                           

                          Thanks for the response. Unfortunately we don't have this in the UK, and as far as I am aware we have nothing similar. Would be a great solution though, so simple.

                           

                          Perhaps I should open a new thread for this.

                           

                          Best Regards

                           

                          Ben

                          • 10. Re: Delete field in Server taking days?
                            taylorsharpe

                            The only UK cell phone company I know is O2, but I googled them and found: 

                             

                            http://www.ehow.co.uk/how_6510217_send-message-o2-cell-phone.html

                             

                            So if you have O2, you should be able to email a text message to your phone.  What phone company do you have?

                            • 11. Re: Delete field in Server taking days?
                              BenHutson

                              Hi Taylor,

                               

                              That was quick!

                               

                              Interesting to know though, for my next phone. Currently with Orange. There are services available that will do effectively the same thing, email to text, but you have to pay for credits.

                               

                              Best Regards,

                               

                              Ben

                              • 12. Re: Delete field in Server taking days?
                                rbarlow03

                                Quick google search found the following.  Not sure if costs any more than a text message from your allotment.

                                 

                                phonenumber@orange.net

                                • 13. Re: Delete field in Server taking days?
                                  BenHutson

                                  Thanks rbarlow03,

                                   

                                  Having checked this out, it looks like this won't work that straightforward. However, Orange will offer a free email account, and from what I have read elsewhere on their website (presuming all info is up-to-date), then they will send you a text containing the subject line of the email when you receive an email. You can of course set up your email with your orange phone number.

                                   

                                  So in a round-about way you can make this work. Since not everyone is on Orange then I may resort to setting up the service email based only.

                                   

                                  This is good to know though for future applications.

                                   

                                  Best Regards,

                                   

                                  Ben