The query analyzer is a great idea. You might also back up the database and truncate the transaction log.
Verify the field is properly indexed.
Once the fire is put out - I recommend finding a new company to host the site because the one you are using obviously doesn't meet your needs. You might feel guilty for not running your normal maintenance, but truth is if you wanted to run a more intensive query such as delete * where ID NOT IN () it should work without even a hiccup.
At 9/30/2004 02:18 PM, you wrote:
I'd first find out about that timeout. I really doubt it's restricted to 60ms, probably 60 seconds. If you're query is taking more than 60 seconds to delete one record you have a serious problem.
You mentioned using the EM, have you tried running scripts from Query Analyzer? If you have EM access, you should have access via Query Analyzer. You can run SQL scripts right from the console with it and it's definitely faster than waiting for the EM to load up all the records in a view.
I'd suggest cranking up Query Analyzer and running something like this:
DELETE * FROM Tablename WHERE ID < YourMaxID
Have you tried something like this before?
-Cameron
----------------- Cameron Childress Sumo Consulting Inc. http://www.sumoc.com --- land: 858.509.3098 cell: 678.637.5072 aim: cameroncf email: [EMAIL PROTECTED]
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Behalf Of Palyne PJ Gaenir > Sent: Thursday, September 30, 2004 12: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
