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
