2022 Developer Survey is open! Take survey.

Questions tagged [archive]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
3 answers
46 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 ...
user avatar
  • 123
0 votes
1 answer
45 views

Best way for archiving old mysql records

we have a Ruby on Rails app with a MySQL DB (on AWS RDS). Some of our tables have a large number of records ~100 million and this number is rapidly increasing every month. Most of the records in these ...
user avatar
0 votes
1 answer
37 views

Oracle - Big Table Archiving

Version: Oracle 12cR2 EE The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2 Table size: 95GB Table row count: 20M Table partitioned? No Indexes on table: there ...
user avatar
0 votes
1 answer
43 views

Best practice for data archiving

I am trying to figure out the best practice for data archiving. I have 1 pair of Master/Slave MariaDB servers where data is replicated from Master to slave. Since this is a very old app I am not able ...
user avatar
  • 205
0 votes
1 answer
310 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 ...
user avatar
  • 143
0 votes
1 answer
33 views

When should I Switch In from Stage Table to a SQL OLTP Partitioned Table

I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table. The time to ...
user avatar
0 votes
1 answer
41 views

SQL Server Partitioning by Local And Remote database

We have a table related to documents(about 17m records on one table), and another one for properties of that documents. And team decided to archive old entries. I thought about partitioning with ...
user avatar
  • 103
0 votes
0 answers
16 views

Is it possible to one time upload an on-prem SQL Server database to Snowflake for archival purposes

I am trying to sort this out before my company jumps from Azure. In Azure, I can BCP a decommissioned SQL Server database to files and upload those to a folder in the data lake. Can this also be ...
user avatar
0 votes
1 answer
303 views

Determining data retention period

I have an application that has generating data for the past 15 years. Currently, no retention period is configured so the data size grows day by day. I would like to add a retention period to the data ...
user avatar
0 votes
1 answer
94 views

MYSQL 5.7 Partition a table based on partition key of another related table

Please understand a little background before getting to know the problem. TABLE transaction ( t_id: AUTO INCREMENT BIGINT t_execute_on_date: DATE, timestamp: DATETIME, ... other columns ) The ...
user avatar
0 votes
1 answer
90 views

I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table

I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number....
user avatar
0 votes
1 answer
50 views

How to properly replicate and archive at once

I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a ...
user avatar
  • 163
0 votes
2 answers
47 views

Analyze MySQL what needs to be archived

I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and ...
user avatar
0 votes
1 answer
95 views

Options for archiving old MySQL 5.7data

I have an web application that has a 1TB MySQL 5.7 production database. We are running into performance issues due to table data size. We are planning to archive old data from different tables. ...
user avatar
0 votes
1 answer
178 views

Table partitioning on OLTP

On SQL Server 2017 Std, I have an SSIS package that runs once a day on a 24×7 mission critical operational database to archive the largest transactional tables. The data is moved to tables on a ...
user avatar
2 votes
1 answer
33 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 ...
user avatar
0 votes
1 answer
71 views

Yearly database archive design

I need to implement a DB of tracking numbers. The tracking numbers table could potentially contain billions of records during its lifetime so an archive database should be implemented to improve ...
user avatar
  • 159
0 votes
2 answers
390 views

Archive old data in a large table in DB2

I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE. The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,...
user avatar
0 votes
0 answers
168 views

Archiving Huge table with Foreign Key dependencies

We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship We have decided to archive the huge table but not the child tables(since they are not that big ) ...
user avatar
0 votes
1 answer
1k views

MongoDB - Can I create TTL index with rolling method

Im getting stuck with archiving a huge amount of data in MongoDB 3.6 I want to delete 506 million records in a collection. I tried to remove using bulk.remove(), but that is also slow. 50 records are ...
user avatar
  • 1,688
6 votes
2 answers
2k 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 ...
user avatar
  • 1,007
0 votes
0 answers
407 views

Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints. We decided on the archival since the ...
user avatar
0 votes
1 answer
119 views

Auto-archive MariaDB10 database table

I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following: Create new table everyday just before ...
user avatar
  • 1
0 votes
2 answers
555 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 ...
user avatar
2 votes
1 answer
5k 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/...
user avatar
0 votes
1 answer
43 views

Which mechanism is best for previous data archived in oracle?

Our company has a big data server in which large data is stored, like images, video, audio, etc. Our company wants a process running with the new year, such that in 2020 the previous year's 2019 data ...
user avatar
1 vote
2 answers
87 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 ...
user avatar
0 votes
0 answers
104 views

Archiving SQL Server data on disk to be pottentially restored

Right now, we are backing up data per table into text/CSV files per month. This works fine for fairly small tables. For bigtables, the text files become so big that the restore process for a month ...
user avatar
  • 1,051
1 vote
1 answer
4k 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 ...
user avatar
1 vote
0 answers
93 views

How to migrate InnoDB tables with 10s of partitions from the data directory onto another drive on the same server

I am trying to move some of the older partitions from larger tables located at the main data directory to another drive/mount point on the same server. I want all the partitions/tables to be ...
user avatar
  • 11
3 votes
3 answers
684 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 ...
user avatar
  • 131
0 votes
1 answer
33 views

Mysql Slave - Stop applying changes from a particular user

I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday. But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB. So,...
user avatar
  • 1,688
2 votes
1 answer
691 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 ...
user avatar
4 votes
2 answers
651 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 ...
user avatar
2 votes
2 answers
242 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 ...
user avatar
  • 1,035
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 ...
user avatar
1 vote
0 answers
64 views

How to configure partial replication of Oracle DB

Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE. The goal is to keep in PRODUCTION the minimal amount of data required for online operations, and use ARCHIVE to store all the ...
user avatar
  • 131
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 ...
user avatar
18 votes
1 answer
13k 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 ...
user avatar
1 vote
1 answer
2k views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
user avatar
-1 votes
1 answer
2k views

Postgresql - archive_command Windows, not working

I'm attempting to set up archiving on a windows server machine but when I check the logs nothing is happening. The postgresql.conf file this is my input: #wal_level = replica #archive_mode = on #...
user avatar
0 votes
1 answer
1k views

mysql - How to repair & backup archive table, also stop growing .ARN extension file?

I have an MySQL table build with archive storage engine. The table has over 100k records. I have got disk full and it turned out to be a large .ARN file from an archive table. When I cleared some ...
user avatar
  • 109
1 vote
2 answers
2k views

SQL Partition Function without actual values [duplicate]

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below. create table dbo.CustomerAccount ( ...
user avatar
-1 votes
1 answer
280 views

Archiving strategy

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live I want to implement this following archiving strategy: create an archive instance (...
user avatar
  • 141
2 votes
1 answer
1k 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 ...
user avatar
0 votes
1 answer
2k views

Copy a mongo db collection without locking whole mongod instance

I am trying to run mongodb copy collection on same server. Requirement is we delete database as per our archival policy but we need to keep one collection from this data as a backup in separate db on ...
user avatar
  • 511
0 votes
1 answer
1k views

Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table

I'm doing tests to change a current table from my InnoDB database to Archive, but I had a problem trying to restore a dump after the change. After several tests, the problem is related to the value ...
user avatar
0 votes
1 answer
3k views

MySQL - Delete older Records from a huge table

I have some huge tables which have around 10years data, and each table has approx 10000000+ rows. Basically, I want to remove older than 2 years data and make a copy of deleted data in a separate ...
user avatar
  • 1,688
2 votes
1 answer
2k 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 ...
user avatar
  • 135
0 votes
1 answer
1k views

Recovery using Archive redo log files

As I'm new in using RMAN and archivelog, I've a few doubts regarding to this. My database is Oracle 11g(windows platform). It was previously in noarchivelog mode and recently I've changed it to ...
user avatar
  • 152