Tag Info

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 ...


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 ...


5

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 ...


5

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 ...


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 ...


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. ...


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

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

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 ...


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

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

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

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 ...


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

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

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

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

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: ...


1

You can always clean them up manually or use a script to clean them up (maybe looking for files older than seven days or something).


1

There are two ways to determine which logs to keep. One you write yourself. You would need to issue a db2 list history backup all for <db name>. This will show you the history file and locate the last backup and the last needed archive log. Anything prior to that can be deleted. Now you can add in automation. First step set db2 update db cfg using ...


1

When you restore an offline backup, you are not required to roll forward (although the database will be in ROLLFORWARD PENDING state at the end of the backup, which you can avoid by using the WITHOUT ROLLING FORWARD option for RESTORE DATABASE). This means that you can remove any archived log files. However, keep in mind that you may want to be careful ...


1

Since the ARCHIVE Storage Engine does not support indexes of any kind, your main problem is the presence of the PRIMARY KEY. Simply drop the PRIMARY KEY. Then, convert the Storage Engine. There are two approaches to this APPROACH #1 ALTER TABLE date_obook DROP PRIMARY KEY; ALTER TABLE date_obook ENGINE=ARCHIVE; APPROACH #2 CREATE TABLE date_obook_new ...


1

Not sure why your boss doesn't want to keep a backup of the DB on hand in the case of need of future restore, but I guess he has his reasons. The nice thing about exporting this data out to text files is that you could always import them back into databases that aren't necessarily SQL Server in the future - as well as read them with text editors in a pinch. ...


1

The problem is that these deletes are now taking over 10 hours to run. if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log. Founded the ...


1

You probably need to use catalog, because a long time passed since January. rman target / catalog rman_catalog_user@rman_catalog_db RUN { SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore'; RESTORE ARCHIVELOG FROM TIME = "to_date('2014-01-19 19:20:00','YYYY-DD-MM HH24:MI:SS')" UNTIL TIME = "to_date('2014-01-19 ...


1

To troubleshoot this type of erro follow the steps bellow : 1- make sure that the location where you want to write exists. 2- make sure that the oracle(or the user who runs the database instance) user has the right to write in that location.(try to create txt file in that location as Oracle user.) Ok , more steps for you to troubleshoot: - i see you are ...


1

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


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.



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