Questions tagged [archive]
The archive tag has no usage guidance.
113
questions
24
votes
3
answers
10k
views
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 ...
- 24.1k
18
votes
1
answer
17k
views
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 ...
18
votes
1
answer
12k
views
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 ...
- 977
6
votes
2
answers
3k
views
Managing a SQL Server database with over one terabyte of data [closed]
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 ...
- 1,039
6
votes
1
answer
357
views
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 ...
- 287
5
votes
1
answer
907
views
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 ...
- 299
5
votes
1
answer
9k
views
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 ...
5
votes
1
answer
803
views
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 ...
- 1,163
4
votes
1
answer
1k
views
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 ...
- 437
4
votes
2
answers
866
views
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 ...
user155025
4
votes
2
answers
5k
views
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: -...
- 143
4
votes
2
answers
2k
views
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 ...
- 45
4
votes
1
answer
204
views
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 ...
- 675
3
votes
2
answers
37k
views
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
3
votes
1
answer
2k
views
archive command is not working
One of our ancient PostgreSQL server's used disk space has increased to 90%. First I've checked the archive command:
archive_command = 'rsync -aq %p /data/wal-archive/%f'
The problem with that ...
- 63
3
votes
1
answer
2k
views
DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows)
I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.
Background
I had a large table (~400,000,000 rows) that needed to have rows archived ...
- 76
3
votes
1
answer
243
views
Archivelog redundancy
I need to enable archivelog. But sometimes database can't connect archivelog destination (NFS disk) and shutdowns. I can set two archivelog destinations and one of them is not NFS disk (local disk).
...
- 163
3
votes
3
answers
897
views
SQL Server replicating data to an archive
I have the following scenario:
Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these ...
- 131
3
votes
1
answer
3k
views
Data archival strategy that handles schema changes?
I am working with a legacy application that has about ten years of customer data. A majority of this data is not used in day-to-day operations but there is a business requirement to have the data ...
- 2,619
3
votes
1
answer
791
views
MongoDB sharding by monotonic id with zones for archiving
Situation:
I have an ever-growing collection of documents which do have monotonically increasing unique id.
All queries are direct lookup for documents with specified _id (no range queries nor ...
- 131
2
votes
2
answers
802
views
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 ...
- 675
2
votes
1
answer
3k
views
Best practice for removing old data in postgres table but retaining a copy of removed data
We currently have a table that has approx 20 columns (one of the columns being a timestamptz datatype) in our database server which has 834 million rows. This in terms of size is a big table (approx ...
- 429
2
votes
2
answers
2k
views
How to archive a whole database?
I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when ...
- 7,300
2
votes
2
answers
723
views
Support ticket system - when/how to move old tickets?
I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
- 75
2
votes
1
answer
9k
views
archive command repeatly failing for one particular file
I am seeing many pg_log files full of following errors
2019-07-08 00:04:06 CEST DETAIL: The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/...
- 145
2
votes
2
answers
475
views
Retrieve archived data [closed]
MSSQL database has previously been archived on another database. Approximately 70m rows (includes sales information.)
Now, I am asked to move this archived data to live database. Because, customer ...
- 1,079
2
votes
1
answer
1k
views
postgres: WAL files not being deleted during PITR
I have got an issue with setting up PITR on my postgres server:
WAL archiving and restoring works quite well, as well as restoring to a specific point in time. The issue however is, that if i do a ...
- 43
2
votes
1
answer
3k
views
Archive database on Postgres
I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, etc.)....
- 163
2
votes
1
answer
9k
views
Difference between alert log and archive log in oracle databases
I don't know anything about oracle databases. Actually I'm asking this question for my friend (He currently doesn't have access to internet!).
He wanted to know that what's the difference between ...
- 123
2
votes
2
answers
34k
views
Extract Archived Log Files from RMAN Backupset
I have an RMAN backup that is taken on 19th january with: BACKUP DATABASE PLUS ARCHIVELOG command. Now I need to restore Archived logs from that backupset to some other OS directory. Is there any way ...
- 75
2
votes
1
answer
35
views
accessing, updating a large MYSQL table advice and guidance
I'm after some guidance or advice really. We've built an application and its gone mental with custom since coronavirus. We are after some advice for something we have never tackled.
The problem we ...
- 123
2
votes
1
answer
1k
views
Truncate text output of COPY command for postgres
I've got a script that copies every table in a schema to a csv using the following:
psql -Atc "select tablename from pg_tables where schemaname='$schema'" --host=$host -w --user=$user $database |\
...
- 317
2
votes
1
answer
88
views
"symlink" data to new database
I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible:
Archive anything with a ...
- 23
2
votes
3
answers
2k
views
Extracting Data from multiple archive tables
I am developing a report in report builder 3.0 where I am extracting data from a table dbo.odcalls.
The problem is this table is archived on monthly basis to a separate database (HN_Backup), and I ...
- 29
2
votes
1
answer
3k
views
Archiving data in SQL Server with stored procedure [closed]
Database in SQL Server 2014 has one table and is consuming around 17-20 GB of disk space each day. Assuming that level of usage remains fairly consistent and not accounting for lower usage on ...
- 135
2
votes
0
answers
307
views
SQL Server archiving
We are trying to archive our data for the first time in our database. The database is around 250 GB. Our approach is as follows. Create a copy of those tables without any index or constraints. And ...
- 161
2
votes
0
answers
674
views
Any way to ensure that Change Data Capture clean up process does not delete the change data that you haven't processed yet?
I'm using CDC feature to track changes that are being made, since the cleanup period is set to 3 days by default, I have to run jobs to archive this change data from the CDC system tables to my tables....
- 535
1
vote
2
answers
124
views
Resetting a new archiving database
I have an archiving question.
We have a History DB used for archiving old records from our main system and the application knows to query it
However, it becomes too big.
I would like to ...
- 575
1
vote
2
answers
3k
views
SQL Server Archiving Data Functionality
Our company is looking for functionalilty/or automated way to archive data in SQL Server.
Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We ...
user129291
1
vote
3
answers
1k
views
Design for archiving a sorted list (with ability for a few INSERT)
I have a somewhat static sorted list of words which I want to save in a database table:
-- simplified!
-- Note: the rank/order of each item is NOT calculable from other values!
CREATE table words (
...
- 111
1
vote
3
answers
24k
views
DB2 ARCHIVE mode - Can all archive logs be deleted after doing the FULL OFFLINE backup?
I am interested in one thing, if I understood correctly.
DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK).
If I made offline full backup today and I know I ...
- 675
1
vote
2
answers
1k
views
How to move old data to cheap hard drive
I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few ...
- 115
1
vote
1
answer
5k
views
Delete and archive old data from several related tables
Our schema looks like this:
table tblTrip: stores start and end trip date and time
table tblTripData: keeps all the trip data
table tblTripEvent: stores events related to the particular trip.
Then ...
- 969
1
vote
2
answers
3k
views
Best Practices to archive or remove old data in SQL Server 2012
Our application has numerous tables that contain outdated data, some dating as far back as 2010. Despite having indexes defined on these tables, many of the index seeks retrieve old records, which is ...
- 25
1
vote
1
answer
91
views
data archival postgres
I have this code to delete rows with column "id". I want to limit the number of rows deleted in each loop. This code deletes the first 25k rows and the next loop deletes nothing since its ...
- 71
1
vote
1
answer
5k
views
mongodb - monthly archival of data
Coming from a RDBMS background, this is what I have done in the past.
Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous ...
- 145
1
vote
1
answer
827
views
Is it possible to archive table rows of an RDS read replica?
I would just like to know if I can purge 60 days old rows from a replica. Would the replica just copy the rows again from the master after deleting the rows? Because what I would really like to do is ...
- 13
1
vote
1
answer
2k
views
DB2 - Archive Mode - on what values to set LOGPRIMARY,LOGSECOND,LOGFILSIZ?
we must set our database from CIRCULAR to ARCHIVE mode in order to enable ONLINE backups.
Backups will be done every 1 day.
Database is now almost 30GB.
Database grows approximately 1GB per month.
...
- 675
1
vote
1
answer
3k
views
How does Postgres know which logs have been archived?
I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave ...
- 1,169
1
vote
3
answers
929
views
Database archiving strategy
I work on a payment system (a bit regulated area with requirements to store really old data for audits & customer requests).
We have SQL Server 2016 (but will move to 2019 soon).
System is 16 ...
- 161