Hot answers tagged

26 votes
Accepted

What is the quickest way to purge data?

Since you are deleting 90% of the rows, I'd recommend copying the rows you need to keep into a new table with the same structure, then use ALTER TABLE ... SWITCH to replace the existing table with the ...
Hannah Vernon's user avatar
  • 70k
11 votes
Accepted

What's the best way to archive all but current year and partition the table at the same time

Why are you not getting minimal logging? I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I ...
Geoff Patterson's user avatar
8 votes
Accepted

Managing a SQL Server database with over one terabyte of data

Get more space. If you have a business requirement to retain that much data, they have to come up with the money. Turn on page compression for the biggest tables (and indexes) (test test test!), Log ...
Trubs's user avatar
  • 742
5 votes

Managing a SQL Server database with over one terabyte of data

I'm adding to Trubs' answer above, which you should upvote as (s)he's on the right track, but I'm adding more options here as well: Add more space (as stated above) If you have a high amount of ...
John Eisbrener's user avatar
5 votes

Best practice for removing old data in postgres table but retaining a copy of removed data

Any solution that DELETEs a lot of rows from a database table is painful. You will always end up doing something along the lines of WITH d AS (DELETE ... RETURNING ...) INSERT INTO ... SELECT * FROM d,...
Laurenz Albe's user avatar
  • 50.5k
4 votes
Accepted

Should Archive Tables have their own Surrogate Identity Key

Fragmentation is not as bad as you think it is. Page fullness (internal fragmentation) is much more important than the physical order of the pages (external fragementation). Having rows inserted out ...
spaghettidba's user avatar
  • 11.3k
4 votes
Accepted

archive command repeatly failing for one particular file

If the file is not in pg_xlog, but there is a .ready file in archive_status, then somebody must have removed that file, which is why archival is failing. Temporarily set archive_command = '/bin/true' ...
Laurenz Albe's user avatar
  • 50.5k
3 votes
Accepted

SQL Server Archiving Data Functionality

There is no built in functionality. The stored procedure route will work, but you might also want to look into an ETL package (like SSIS) to do this as it may be easier to maintain. You could also ...
LowlyDBA - John M's user avatar
3 votes

archive command is not working

First, take a pg_basebackup, just in case. Then set your archive command to /bin/true. That will allow Postgres to delete everything to its own satisfaction that has already been deleted. To restore ...
Gaius's user avatar
  • 11.2k
3 votes
Accepted

DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows)

I found out what's going on here: Because of how I was choosing test_id values in steps 1 and 2, temp_table B would sometimes be empty. (see question description for step/table context) Then, with ...
dutchscout's user avatar
3 votes
Accepted

Archivelog redundancy

You can use ALTERNATE attribute in second archive log destination. ALTERNATE Specifies an alternate archiving destination to be used when the original destination fails. Example: ...
atokpas's user avatar
  • 8,624
3 votes
Accepted

Resetting a new archiving database

The built-in way to reduce backup size for VLDBs with read-only data is to parition your large tables, placing older partitions on read-only filegroups, and then take infrequent backups of the read-...
David Browne - Microsoft's user avatar
3 votes

Resetting a new archiving database

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name ? Yes, you can create a view /...
Randi Vertongen's user avatar
3 votes
Accepted

How to move old data to cheap hard drive

Here is a better architecture: Create a new tablespace on the slow drive. Set the storage parameters seq_page_cost and random_page_cost higher on that new tablespace so that the PostgreSQL optimizer ...
Laurenz Albe's user avatar
  • 50.5k
3 votes

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

In OLTP you would rarely, if ever, switch-in partitions for exactly the reasons you state. Instead consider using Columnstore with compression delay. See Get started with Columnstore for real-time ...
David Browne - Microsoft's user avatar
2 votes

Redo/Noredo in Oracle

Can nologging in certain tables boost the performance? Not for a normal transactional load. nologging only affects some very specific cases of DML (essentially direct-path inserts) and some DDL (see ...
Mat's user avatar
  • 10k
2 votes

Use a SP to Archive/Revision related records in several related tables?

Creating such a stored procedure is probably going to be complex and error prone. That's why there are temporal databases or temporal features in a database. If you really wanted to do this with a ...
SQLmojoe's user avatar
  • 1,433
2 votes
Accepted

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 ...
Md Haidar Ali Khan's user avatar
2 votes

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

This is a very tricky question because of the internals of the ARCHIVE storage engine. People have asked this same question in the MySQL Forums Jan 04, 2014 : Huge and growing archive with ARN ...
RolandoMySQLDBA's user avatar
2 votes
Accepted

mongodb - monthly archival of data

As at MongoDB 4.0, there isn't built-in support for partitioned collections but you can implement a similar pattern: Your application would always write new entries to collection M0 (current) You ...
Stennie's user avatar
  • 10.2k
2 votes

SQL Server Archiving Data Functionality

This is my list on how I would approach : If the data needs to be reside in the same database : If using enterprise edition Use table partitioning (since you are using SQL Server 2012). For your ...
Kin Shah's user avatar
  • 62.2k
2 votes

Retrieve archived data

If there's no duplicate data to worry about, adding another 50 GB of data to a 250 GB database is not going to matter much, especially since the business requirements need the archived data in the ...
Randolph West's user avatar
2 votes

data archival postgres

First of all this function doesn't work, you specified RETURNS integer but then returns nothing after loop (anyway I don't think it's necessary, I would just make returns void) Second thing, it ...
sh4rkyy's user avatar
  • 129
2 votes
Accepted

Best Practices to archive or remove old data in SQL Server 2012

Indexes do not seek data, the query does. If your query check old data, then it is written incorrectly. A simple example: select year, sum(amount) from sales where shop=123 group by year select year, ...
White Owl's user avatar
  • 797
1 vote
Accepted

Retrieve archived data

If I were to implement the migration, I would ensure to have a good planned indexing. First of all, move the historical data into history tables, having the same structure as the current working ...
AMG's user avatar
  • 1,352
1 vote

SQL Server replicating data to an archive

Apart from developing ETL process using SSIS or Store Procedure etc, if you want to use Transaction Replication there is option to ignore DELETEs in replication. Provided the archival on primary ...
Unkush's user avatar
  • 316
1 vote

SQL Server replicating data to an archive

if i understand correctly log shipping and transactional replication is not an option because they basically update on the other side whatever you do on the primary server. you will get an updated ...
sql_girl's user avatar
  • 345
1 vote

Mysql Slave - Stop applying changes from a particular user

I think you'll be able to achieve this goal but there isn't really anything native that will give you this functionality out of the box. You won't be able to use replication filters because they're ...
eroomydna's user avatar
  • 1,031
1 vote

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

Here's a thought on archiving, with transactional integrity. Have another table; let's call it archive. It needs few, if any indexes. Move 1000 rows from your table to archive at a time, ...
Rick James's user avatar
  • 77.7k

Only top scored, non community-wiki answers of a minimum length are eligible