Hot answers tagged

9

Alright so I believe you doing and INSERT is increasing the size then after you do your OLEDB command which is sending hundreds of thousands of DELETE commands to SQL Server is what's causing your problem. So here is a short Demo/Tutorial on how to do this DELETE operation more effectively. Even if this doesn't solve your index problem it will make your ...


7

My suggestion about archiving: Create archive_tablespace (if you want you can separate hardware on archive) Create tables. For example we want to archive table posts. create table posts_all ( LIKE public.posts) ; create table posts_archive () inherits ( public.posts_all) ; alter table public.posts inherits ( public.posts_all ) ; After that ...


6

Since you have this requirement, They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database AlwaysON gets eliminated. I would suggest you to setup Transactional replication. Frequently (every 30mins) move the transaction data out of live database to another database You can schedule the ...


5

This is completely normal. Each of these directories are for each unique log chain. If you are familiar with software version control, each log chain is like a branch. A new log chain is created each time you restore a database and roll forward to a point in time other than end of logs. Here is why: You have a database, SAMPLE. It has reached the log ...


4

This doesn't have anything to do with using a CTE specifically, as your question title implies. It is probably a combination of at least one or more of the following: an expensive scan (or seek + a million lookups) to identify the rows to delete (I assume [Receive Date] is not the clustering key) many underlying non-clustered indexes that also must be ...


4

You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/ I've used option 2 to get better results than with the SQL built-in backup compression. ...


4

What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And ...


3

Basically your database has stalled because it has ran out of space to store archive logs, and needs to switch one. To remedy, delete the generated logs (assuming you don't need them for backup and recovery purposes), then execute (as SYSDBA): alter system archive log all; alter system switch logfile; If you don't need to run your database in archivelog ...


3

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises. If you want the data to be restored later, then best is to Script out schema of the tables that you wish to drop and save it as a sql script. BCP out the data (without using -n switch as -n is for native format which is ...


3

According to the link that @mustaccio put in the comments: The database manager resets to S0000000.LOG if: A database configuration file is changed to enable rollforward recovery A database configuration file is changed to disable rollforward recovery S9999999.LOG has been used.


2

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout. But, checkpoint_segments has no effect during recovery. Restartpoints during recovery are driven exclusively by checkpoint_timeout. Since recovery can ...


2

Seems like a good reason to visit the Oracle Documentation site. The 2-day dba documents are very good. If your database is running in ARCHIVELOG mode, it copies all transactions to the archivelog destination. The transactions are always written to the redolog files but when they are full, they are only saved when running in archivelog mode. This enables ...


2

A read replica is basically a read_only slave in normal MySQL speak. RDS does allow you to change the read_only parameter, so you can purge the records you want and RDS won't recopy the data from the master. However be careful if you go this route. If you run DML statements on the master for rows that don't exist on the slave, you likely will break the ...


2

Selecting and deleting a large number of rows will lead to blocking, which is probably why you cannot use the system for a couple of hours. The easiest way in SQL Server Express to control blocking is to control how many rows you are deleting at one time. For example, you might read Aaron Bertrand's notes here: ...


2

LOGPRIMARY, LOGSECOND and LOGFILSIZ combine to define the maximum size of the transaction log on your server using the following formula: Total Size = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096 bytes In your case, this results in (13 + 15) * 4096 * 4096 = 469762048 bytes. The maximum transaction log size limits 2 things: 1) the absolute size of a ...


2

I recently answered similar question here: database-archive-solutions You should read about postgresql table partitioning, if you have good way to split data in parts.


2

I would use an auto-number col as the primary key. Add another col for the rank. When adding a new item to the table set If the rank is last set the rank to be MAX(RANK) +1. If the rank is in the middle increment the rank of all other items , by the following. UPDATE Words SET Rank = Rank +1 where Rank >= CurrentRankReplacedId I agree, this is ...


2

By all means use an unsigned int surrogate key as your primary, clustered index. However, instead of using sequential values, build some padding into the sequence. This means that you'll have to assign the id manually instead of using auto_increment. If you use unsigned int in MySQL, the max value is 4,294,967,295. If you expect to have at most 100,000 ...


2

You need to define your requirements more specifically before even thinking about a solution: Why is archiving necessary? It sounds like the system already handles old data, so what is the business need to separate out this data? Performance? Is archive data a read-only snapshot, or are historical data changes possible? If changes are possible, which types ...


2

This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since Engine does not support the creation of indexes Engine supports INSERTs and SELECTs Surprisingly, there can be a key internally present. How? According to the MySQL Documentation The ARCHIVE engine ...


1

Try pt-archiver from Percona Toolkit, it permits to transfert data on the fly between two MySQL instances. You can for instance use the --where option to filter result set by date. It's particulary well designed for your needs, because you can transfer data and delete it from source in the same command... Official page is here : pt-archiver If you want ...


1

I changed my account, so it's already Max. Here, a example : pt-archiver --source h=<server_source>,D=<database_source>,t=<your_table> --dest h=<server_target> --where "date_field < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --limit 1000 --txn-size 1000 --statistics Add the option --dry-run if you want to test. Add the option ...


1

I experienced this confusing error "Duplicate key" when I tried to insert ID's that were lower than the last ID in the database. Just doing a simple test: first insert record with ID 1, then with ID 5, both complete successful. But if you then try to insert with ID = 3, it gives the "Duplicate Key" error. I'm not sure whether there are ways to get around ...


1

When you enable archive logging in a database, you're telling DB2 that you want to retain a copy of all transaction logs that have been created. LOGPRIMARY and LOGSECOND only control how many active log files can be in use in the active log path at any given time. Please read the documentation on database logging for more details.


1

Here is the solution. RMAN > run { ALLOCATE CHANNEL d1 DEVICE TYPE disk; restore archivelog all; }


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...


1

We finally deleted the files manually and OEM is fine. We ran this query to get the delete scripts: SELECT 'alter diskgroup ' || gname || ' drop file ''+' || gname || Sys_connect_by_path(aname, '/') || ''';' a FROM (SELECT b.name gname, a.parent_index pindex, a.name ...



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