Archiving data monthly.
-
i have a table which archived the logs from our content filtering device and it has grown quite large over the past several months.
I was wondering if there was a way to run some kind of script (in SQL server) on a monthly basis which would take all the records from the past month and remove themfrom the DB table and save themoff to a file. is that possible? if so how?
and if i can do it then how woudl i go about searching through those archived files if i needed to?
-
You'd load them back into sqlserver....
Seriously why do you want to take those records out of the database?
How many records r u talking about?
-
well the problem is that from october 04 to may 05 we have accumulated about 3.2 million records and when it will usually timeout when i try to query it (from within enterprise manager). and if i try to view all records in enterprise manager it get to almost 2 million records and then tells me it does nto have enough memory to display the entire recordset. this is on a system with 4GB of RAM.
so thats why i thought about archiving the data off into files. unless you can offer a better solution.
-
You can try to return only the data you want to see by using a WHERE clause, or if you really want to archive the data you can create a historical table which is on a seperate data file. Then move the archival data to that table and delete it from the main table.
-
yeah i know that i coudl just select what i want using WHERE but depending on what i am looking for, sometiem the query will time out.
I suppose i coudl move the records to a archive table that contains everythign up this month because i woudl suspect that i woudl less frequently have to query the archive table.
-
If you truly don't want the data in SQL Server anymore but may need to refer to it in the future, you could script or Bulk Copy it out to a text or Excel file, and then go back remove the records you want from the table. If you need to keep it available in SQL Server, a better option may be to migrate the data in question off to a second archive table then delete it from the originating table.
Be aware though that since deletes are logged transactions, for a large table you'll need enough disk space to accomodate the growth of your log file and make sure that it's set to Autogrow. Hope this helps.
My hovercraft is full of eels.
-
Why not partition it monthly?
I.e create one table per month, create a check constraint on the table that restricts the data and gives a HUGE performance gain, create a VIEW that UNION ALLs the individual tables. When you reach the time for removing a month, rewrite the VIEW, drop the partition and Smile
Partitioned views in SQL Server 2000 is a great untapped resource that is quite unknown and gives radical performance gains in most cases (if you do it correctly). More over, SQL Server's vertical partitioning is far beyond anything you could match in Oracle.
Happyhunting, Hanslindgren!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply