I have millions of old measurement records in my database, which has now grown to 20GB. I would like to move records older than two years to another database and do so on a monthly basis. It seems silly to unload the database to a new database and the delete all records younger than two years from than (newly-created) database. Is there another way to do this?

asked 12 Dec '11, 06:57

Gudjon's gravatar image

Gudjon
16112
accept rate: 0%

edited 15 Mar '13, 20:51

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266


If you created a second database, and set that as a Remote Server on your original database, you could then create an event in your original database to copy all the old rows into the second database, perhaps check that the same number have arrived(!) and then delete those from the original. That process could be completely automatic if you wanted.

Look at the "Working with Remote Servers" and "Automating Tasks Using Schedules and Events" topics in the help.

NB the term "Remote Server" can be confusing, it can be another database on the same server!

permanent link

answered 12 Dec '11, 07:03

Justin%20Willey's gravatar image

Justin Willey
6.6k107136205
accept rate: 20%

I fully agree with Justin's suggestion.

It should be noted that deleting old data won't reduce the database's file size - it will simply use the free pages for the coming inserts (which is a reasonable thing IMHO).

Therefore, in case you would like to reduce the file size and would like to rely on a regular facility to move old data to a second database, you will have to unload/reload the database once after you have done the first "move operation".

(12 Dec '11, 07:47) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×36
×5

question asked: 12 Dec '11, 06:57

question was seen: 750 times

last updated: 15 Mar '13, 20:51