Questions tagged [archive]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...
Kenneth Fisher's user avatar
18 votes
1 answer
18k 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 ...
Dharmedra Keshari's user avatar
18 votes
1 answer
13k 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 ...
Gnanam's user avatar
  • 987
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 ...
SQL_NoExpert's user avatar
  • 1,049
6 votes
1 answer
358 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 ...
Luis de Haro's user avatar
5 votes
1 answer
918 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 ...
NismoGTR05's user avatar
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 ...
tanuj shrivastava's user avatar
5 votes
1 answer
852 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 ...
Evan Appleby's user avatar
  • 1,181
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 ...
aaroncatlin's user avatar
4 votes
2 answers
886 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
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: -...
Maximi's user avatar
  • 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 ...
Jamie T's user avatar
  • 45
4 votes
1 answer
210 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 ...
Dejan's user avatar
  • 685
3 votes
2 answers
38k 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.
user avatar
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 ...
Ozan Temel's user avatar
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 ...
dutchscout's user avatar
3 votes
1 answer
252 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). ...
onur's user avatar
  • 163
3 votes
3 answers
935 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 ...
Nils's user avatar
  • 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 ...
8kb's user avatar
  • 2,619
3 votes
1 answer
801 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 ...
Antonio Tomac's user avatar
2 votes
2 answers
815 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 ...
Dejan's user avatar
  • 685
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 ...
rdbmsNoob's user avatar
  • 449
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 ...
usr's user avatar
  • 7,330
2 votes
2 answers
724 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 ...
user1831003's user avatar
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/...
Neeraj Bansal's user avatar
2 votes
2 answers
531 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 ...
Yunus UYANIK's user avatar
  • 1,089
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 ...
Chris's user avatar
  • 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.)....
Michal's user avatar
  • 163
2 votes
1 answer
10k 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 ...
today's user avatar
  • 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 ...
Memon's user avatar
  • 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 ...
Daniel Robinson's user avatar
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 |\ ...
robertpostill's user avatar
2 votes
1 answer
90 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 ...
AIT's user avatar
  • 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 ...
Jatin Sharma's user avatar
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 ...
somu's user avatar
  • 135
2 votes
0 answers
308 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 ...
f444ran's user avatar
  • 161
2 votes
0 answers
689 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....
Ritesh Bhakre's user avatar
1 vote
2 answers
126 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 ...
Roni Vered's user avatar
1 vote
2 answers
4k 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
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 ( ...
ComFreek's user avatar
  • 111
1 vote
3 answers
25k 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 ...
Dejan's user avatar
  • 685
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 ...
MichaelRazum's user avatar
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 ...
newbie14's user avatar
  • 969
1 vote
2 answers
4k 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 ...
JoeCharles's user avatar
1 vote
1 answer
97 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 ...
Falcon's user avatar
  • 91
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 ...
cogitoergosum's user avatar
1 vote
1 answer
835 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 ...
Ragu Diaz's user avatar
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. ...
Dejan's user avatar
  • 685
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 ...
orokusaki's user avatar
  • 1,179
1 vote
3 answers
1k 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 ...
Grigory's user avatar
  • 161