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

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

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


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

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

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

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


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

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

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


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

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

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

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 logging_clause). So essentially only for bulk data-loading scenarios. If that's not what your database is doing, it won't help at all. If it is what your ...


1

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support. Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log ...


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

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



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