Database is full; what should be the strategy?

Question

What if my database is full and I don't have any spare hard disk or memory space. What should my strategy to free up the space?

Answer 1

Try and truncate the transaction logs (they get pretty big) - download something like WinDirStat and run it to see if you can find out what the really big files are.

Or buy another hard drive.

Answer 2

"My database is full" can mean very different things in Oracle

1 Every byte of my hard drive is filled with "useful" data.

Compression / de-duplicating data / normalisation may help, but its cheaper to buy more disk.

2 I have space in the file for tablespace X but need it for tables in tablespace Y

If the space is at the end of the file, you can shrink the tablespace. If not best to move the tables from Y to X. Its pretty difficult to reclaim space from the middle of a tablespace file

3 I have deleted a lot of records from TABLE A but it hasn't reclaimed the space in the tablespace

ALTER TABLE ... MOVE; will tidy it up, but you'll need to rebuild indexes afterwards.

There are probably other scenarios, so I'll community wiki this answer/

Answer 3

Transaction logs will keep growing indefinitely until they consume all available space. It's likely this is one of the causes of you running out of space.

You can truncate the transaction logs to free up space, but be aware that this will remove your ability to restore-to-point-in-time or roll back changes to the DB.

To truncate the logs, run

BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY

then shrink the log file to free drive space. Note I've assumed MS SQL.

You will need an ongoing strategy to prevent this from happening again. Depending on your application and level of laissez-faire, you may want to:

• Backup the transaction logs to an external server or drive (and shrink the log file). This is the MS recommended solution I suspect and preserves nice things like restore.
• Turn on simple logging. This means you lose restore functionality but also means your DB log won't grow out of control like it has this time.

Answer 4

A quick check on fragmented indexes can do wonders. If you have tables whereby rows go in, come out, get re-inserted etc etc, then you can end up with very fragmented indexes. Even if you have 10GB of indexes that are only 10% fragmented, you can free up 1GB (roughly) with careful application of re-indexing.

Answer 5

Ther is no permanent solution to this....for an instance you can think of virtual memory but its not the ultimate solution you might be awaiting of...so better you can add memory to your database bcoz you cant afford of losing any sort of data..

Answer 6

Table compression.