If you find yourself in a hole, stop digging! Change the code that's adding all the records. Why can't you run a Cf query template to accomplish your deletes?
Joe Kelly ----- Original Message ----- From: "Ron Mast" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 30, 2004 2:37 PM Subject: RE: nightmare db issue on hosted server... > Have you tried maxrowing your query? Break it down to manageable time? > I don't know if this would work, but it certainly would be something I > would try. > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Palyne PJ Gaenir > Sent: Thursday, September 30, 2004 2:11 PM > To: [EMAIL PROTECTED] > Subject: nightmare db issue on hosted server... > > Gods I hope some dba-type person can help here. > > I have a table that is vastly too big. This is SQL-Server 7 and CF > 5 hosted remotely. > > I have been trying for over a month to drop/delete old records, > which I usually do 3x a year. I let it slide a little longer than > usual to help a researcher. But then when I began to worry about > size and speed and start on doing it while just excluding his > records, guess what -- it won't work. > > The server host has limited the SQL query time to 60 (milliseconds?). > > Every day I go to SQL Enterprise Manager (since scripts won't work > for timeout it's easier to be right there in EM) and delete records. > > Sometimes up to a few hundred a time during good moments, often > down to ONE AT A TIME. > > I need to delete OVER A MILLION RECORDS! They are adding at the > rate of thousands a day so obviously I am not keeping up. > > The ID field is indexed on the table so I have to work by that. I > can't do anything by the date field due to timeout. > > I got the maximum ID number for my cut-off date; I need to delete > everything "less than" that (that brings the table up to only > holding records since May 1). The lowest ID value still in the > table is about 1,807,713 and the number I need to delete up to > right now is 2,938,241. > > The server host won't extend the timeout. But often even deleting > ONE record takes "61" or "72" instead of 60 so it won't let me do > it!! This is manually running the query through SQL-Server EM > locally. > > I wait, hoping for a less busy time on the server so I can delete, > you guessed it, ONE RECORD via EM that won't timeout. Meanwhile > reports on the site that do clearing of empty records are starting > to timeout. > > I'm panicking. The whole site is totally slowed down from this > bogged down table. I've asked my rep for help but he is not > qualified as a CF/DBA the way the previous rep was and every day > for the last month, TONS more records get added which just makes > it worse. > > If I can't delete through script and I can't delete through SQL > EM, what the hell am I supposed to do?? > > I would really appreciate any advice. It is only a matter of time > before the whole damn site crashes because of this table -- it's > the answers table of an edu website -- if I can't clean it out. > > Palyne > > > --- > [This E-mail has been scanned for viruses.] > > ---------------------------------------------------------- > To post, send email to [EMAIL PROTECTED] > To unsubscribe: > http://www.dfwcfug.org/form_MemberUnsubscribe.cfm > To subscribe: > http://www.dfwcfug.org/form_MemberRegistration.cfm > > > > > ---------------------------------------------------------- > To post, send email to [EMAIL PROTECTED] > To unsubscribe: > http://www.dfwcfug.org/form_MemberUnsubscribe.cfm > To subscribe: > http://www.dfwcfug.org/form_MemberRegistration.cfm > > ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
