Delete old rows from all tables in a database

Question

Post reply

Delete old rows from all tables in a database

JamesGibb

Old Hand

Points: 347
More actions
November 12, 2010 at 7:20 am

#226622

We use SQL to log lots of values from our process plant (a power station) which can be pulled back for showing graphical trends. The data is only useful when its fairly recent, and as the tables grow retrieving the data become slow so at the end of each day we have a job running to do a series of..
delete from table1 where logtime < dateadd(day, 14, getdate())
..one for each table. Due to restrictions in the trending client (which is not adaptable by us) we sometimes have to create a new table into which to log additional values. And then of course we have to add a new row to the deleting job, and since we have 8 systems doing this, we have to add it to all 8. If there a way to do something like...
(for each table in database) delete from table where...
...so that we can not have the bother of updating the job when a new table is added (oir if one is removed) ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

are you sure you want to delete data older than x days? does all tables have same column name for date?

one way to store table names and column names(date) in a new table which you call master table.

read table names and the column name(to be used in where clause) one by one from this table and delete rows.

for other question, you do have an undocumented SP sp_msforeachtable which can iterate through each table.

Pradeep Singh

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

I think you'd be better off with a template for new tables that also includes a stored proc template to delete older data (#days as a parameter) and includes a reminder to alter your job.

To me the habit of having a reminder is a good thing. I might also include a reminder in a checklist for the people that do the releases.

There is sp_msforeachtable, but I would be concerned that the "always delete data" might not apply to every table every time and would cause issues.

Perry Whittle SSC Guru Points: 233846 More actions · Answer 3

have you profiled the database to ascertain why data retrieval is slow?

how many rows do the tables hold when performance is slow?

what indexes have been implemented to aid data retrieval?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

{S-H-I-V-E-R} Yowch. Deleting data. I'll parition it... I'll save it in an archive or log table. I'll export it and back it up. I do just about anything with data but I'll never delete it unless I know where I can get it back and it's one of MY places that I know I can get to.

I know you say that older data is basically useless but I still wouldn't delete it. Achive in a safe place and then delete? Sure. But just delete it with no chance of getting it back? Someday it may not be so useless and (hint: been there, done that) neither you nor anyone you know can predict it either way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JamesGibb Old Hand Points: 347 More actions · Answer 5

No, it REALLY IS useless, after more than a few hours to some peoples mind, but definitely after two weeks. It's only needed for drawing graphical trends of temperatures, pressures and so forth that the plant operators use to make decisions. A completely separate system - a 'lossy' system called PI from OSI - is used for long term archiving of the data that we do need for strategic decision making. So don't worry about the fact that the data is deleted, just about how to do it.

Steve Jones: The template and reminder sounds like a good option, though if we delete a table then how would we be reminded to alter the job then?

The sp_MSforeachtable is what I was hoping for. Two tables in the database hold data that describes what's being logged to the other tables and will be excluded using the @whereand, all the others will be processed using @command1.

Thanks all.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

Two things.

1. Add notes/comments in your template to make sure you do xxx.

2. In your job, run this off of a table. So join with tables, use a cursor, whatever to do the deletes. Have a "check" job that looks at this list of tables for the deletes, compares to information_schema.tables, sends a note if you're missing any.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

JamesGibb (11/15/2010)
No, it REALLY IS useless, after more than a few hours to some peoples mind, but definitely after two weeks. It's only needed for drawing graphical trends of temperatures, pressures and so forth that the plant operators use to make decisions. A completely separate system - a 'lossy' system called PI from OSI - is used for long term archiving of the data that we do need for strategic decision making. So don't worry about the fact that the data is deleted, just about how to do it.
Steve Jones: The template and reminder sounds like a good option, though if we delete a table then how would we be reminded to alter the job then?
The sp_MSforeachtable is what I was hoping for. Two tables in the database hold data that describes what's being logged to the other tables and will be excluded using the @whereand, all the others will be processed using @command1.
Thanks all.

If that's truly the case, I'd probably take the time to throw a trigger on each table that would delete data older than the desired timeframe everytime new data was added. I'd probably control that timeframe from a central table for each table. That way you could change the timeframe if necessary without having to rebuild the triggers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)