Questions tagged [archive]
117 questions
24
votes
3 answers
What's the best way to archive all but current year and partition the table at the same time
Task
Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database.
The database is in simple recovery mode
The tables are 50 mil rows to several billion and in some cases take up…
Kenneth Fisher
- 24,307
- 13
- 63
- 116
18
votes
1 answer
Database archive solutions
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for database archiving in PostgreSQL.
Few solutions I…
Gnanam
- 987
- 2
- 9
- 16
18
votes
1 answer
What is the quickest way to purge data?
Scenario:
We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and updating the data into Tbl2.
Now, we have to purge…
Dharmedra Keshari
- 253
- 1
- 3
- 6
6
votes
1 answer
Log shipping with a monthly archival process
We will be implementing log shipping in our production environment in the next weeks.
We perform a monthly "cleanup" of the database, whereby we set the database to the SIMPLE recovery model, copy records to an historic database, then delete from…
Luis de Haro
- 287
- 1
- 3
- 9
6
votes
2 answers
Managing a SQL Server database with over one terabyte of data
Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options.
My proposal is at the end of each year we take a backup and truncate all the tables which will keep the database…
SQL_NoExpert
- 1,107
- 1
- 21
- 37
5
votes
1 answer
What is the use of Archivelogs?
Hi I am new to Oracle Database . Using Oracle 11g R2
I want to know the purpose of archive logs created at archive log destination, how they are helpful in database backup.
Suppose I am having backup of Production database till "15-sept-14" and I…
tanuj shrivastava
- 175
- 2
- 3
- 7
5
votes
1 answer
Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?
I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many records and the time to perform these queries has…
Evan Appleby
- 1,203
- 1
- 10
- 16
5
votes
1 answer
Moving Transaction Data To Another Database For Reporting Purpose
We got a requirement from our developer group, to do the followings:
Frequently (every 30mins) move the transaction data out of live database to another database
Secondary database will be utilize for Ad-Hoc query and Reporting
They do not want to…
NismoGTR05
- 309
- 2
- 12
4
votes
1 answer
DB2 - what happens when SQL archive log file reches its maximum value (S9999999.LOG)
we have DB2 in archive mode.
archive log files are created and deleted from the disk.
What will happen when archive log file reaches its maximum value for the name (S9999999.LOG) will it start to count from the beginning (S0000001.LOG)?
Dejan
- 695
- 7
- 20
- 34
4
votes
2 answers
Change table from InnoDB to ARCHIVE in MySQL
I try change my table from InnoDB to ARCHIVE with SQL command:
ALTER TABLE data_obook ENGINE = ARCHIVE;
But it always show me only error:
#1005 - Can't create table 'coincity.#sql-1350_8a' (errno: -1)
My structure:
SET…
Maximi
- 143
- 1
- 3
4
votes
2 answers
What is the proper way to store a full table of data outside of a database? It needs to be able to be restored as well
I have a large SQL Server 2005 database that hasn't had the best maintenance over the years (I just took it over recently). There are around 100 tables that can be completely removed from the database, as well as some that just need data after a…
Jamie T
- 45
- 1
- 5
4
votes
1 answer
Deleting Rows is Increasing Index Size
We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table using an SSIS data flow. The table has a bigint…
aaroncatlin
- 457
- 5
- 15
4
votes
2 answers
Should Archive Tables have their own Surrogate Identity Key
Should archive tables have their own surrogate identity id/key? For a customer sales table example:
We have original table, and then an additional archive table with surrogate archive id.
Rules for primary clustered indexes are:
Ever…
user155025
3
votes
2 answers
DB2 - Why new multiple C000000x subfolders are created in Archive Log folder?
After switching to Archive mode (from Circular mode to keeping archive logs on the disk) we had C0000000 folder where archive logs were copied.
Now I noticed that folders C0000001, C0000002, C0000003 and C0000004 are created where C0000001 and…
Dejan
- 695
- 7
- 20
- 34
3
votes
2 answers
What is the Difference between Archiving and Purging in Oracle database?
I would like to know the main difference between Archiving and Purging in any database we use.
Ramesh