You didn't mention the logfile size limits. Deleting a lot of records can easily fill up the log file and SQL server will roll everything back. However, you can write a stored procedure ( or do it in query analyzer) to truncate the logs, then run your delete.
This is part of a script I wrote to shrink the log files to 1MB each day: DBCC SHRINKFILE(DB_LOGFILE_NAME, 1) BACKUP LOG YOUR_FILE_NAME WITH TRUNCATE_ONLY DBCC SHRINKFILE(DB_LOGFILE_NAME, 1) Note: If you use a stored procedure, you can't use "TOP". You can use "set rowcount @number" to control the number of returned records. Of course, you don't have to run the query on your host's computer. Setup a Cfserver where you are and connect to SQL server remotely. Just use the IP address of the server with your login credentials. Then run the queries on your dev server at home/work. No timeout restrictions there :) I none of that works and you're looking for another host, I have a small hosting company that can probably help. Kind regards, Tom Nunamaker www.paladincomputer.net 888-286-2056 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cameron Childress Sent: Thursday, September 30, 2004 9:18 PM To: [EMAIL PROTECTED] Subject: RE: nightmare db issue on hosted server... 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
