The Old Joel on Software Forum: Part 3 (of 5)

Sql server question

i have about 1.5 GB of text files created every day. i can have these automatically entered into sql server, which would enable me to run some handy-dandy analysis software blah blah blah.

i would like for sql server to automatically delete all records in this table that are older than say, two weeks, or something like that. what's the best way to do this? thanks for your help.

sql newbie
Friday, September 26, 2003

If the table has a column (create_date) that is set to the date when the data is loaded something like

delete tablename where create_date < dateadd(dd,-14,getdate())

will delete all the records older than 14 days.

SQL Server has a scheduler that can be used to run this once a day.

john
Friday, September 26, 2003

excellent! thanks so much!

sql newbie
Friday, September 26, 2003

Anticipate the delete of 1.5 GB of data to take some time, especially if the data is stored into many rows and there are indexes on the table.

I've worked with an Oracle database table that was large and hand several indexes. It took more time to delete the rows from the database than it did to insert them in the first place.

Glade Warner
Friday, September 26, 2003

It might be worth writing an SP to drop the indexes, run the delete, and then put the indexes back.

Philo

Philo
Friday, September 26, 2003

Second philo here, the reasoning behind it is because everytime there's transactions happening to a table.. the table index(es) needs to be maintained. That means.. take whatever part that was affected.. recalculate the index.. save the index. Not to mention the logger has to write to the rollback log your action, row by painful row.

And as you delete these rows one at a time.. the index is wasting a lot of time rebuilding an index for the rest of the records that are soon to be deleted anyway.

So for an offline db, do it exactly as philo says, for online, add a step to duplicate the table without the index.. and without the rows you need.. rename as proper... or if you can.. drop a partial index on the records affected.

Li-fan Chen
Friday, September 26, 2003

I have to manage millions of deletes a day. The best way to do it is not to do it at once. It ties the log up for too long and makes it grow like crazy.

If you can make deletes more frequently than once a day, you should. Even a once-every-five-minutes delete would be ideal, if it suits your data. That way you don't wind up with a bajillion delete rows in the log and wind up waiting a half hour on the checkpoint.

Troy King
Saturday, September 27, 2003

Heh. I thought "turn off logging" was obvious - it's usually the indexing that trips people up.

Philo

Philo
Saturday, September 27, 2003

Philo, please, for the benefit of us all, post directions on how to delete specific rows from a table in SQL2K without logging it. TRUNCATE TABLE doesn't count because it deletes all rows.

Tell us any way you know how to turn off logging for the purpose of a DELETE in SQL2K, since it's so obvious and funny to you.

Troy King
Sunday, September 28, 2003

Rule #1: Think before posting.
Rule #2: If tempted to make a quick quip that really requires no thought, rule 1 becomes imperative.

Totally moronic post on my part. When doing a bulk *insert*, you can do so without logging by using BCP.

I honestly don't know what was going through my brain when I wrote that. My apologies.

Philo

Philo
Sunday, September 28, 2003

Troy,

Re: disabling logging, check out Recovery Options. I believe (though its been a while) that Simple Recovery will disable logging. Of course, you will want to make sure you do a full backup before and after the job.

Simple Recovery is similar to trunc. log on chkpt as well.

Seeya

Matthew
Sunday, September 28, 2003

Nope, Matthew, simple recovery does not turn off the log. As you said, it's the same as trunc. log on checkpt. Even with simple recovery, the log is still active. A massive delete will still make the it grow like crazy and stress the disk.

Troy King
Monday, September 29, 2003

Philo, I missed your retraction. I was just running you down to someone else because you posted something that tried to make you look good while making someone else look foolish.

You're right this time -- there is no way to turn off the log for deletes. That's why my post emphasized minimal impact to the log was the best way to improve mass delete performance.

Troy King
Monday, September 29, 2003