Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 14 '10 at 13:17

This question came from our site for professional and enthusiast programmers.

7  
This: "Delete stuff". –  Blorgbeard Sep 14 '10 at 8:09
1  
Look out for huge log-files. –  elusive Sep 14 '10 at 8:10
2  
What database are you actually using? MySQL, SQL-Server or Oracle? Or is this simply a "what if" question? –  Mark Baker Sep 14 '10 at 8:15
    
2 votes to SU vs 1 vote to SF, why? –  BoltClock Sep 14 '10 at 8:19

2 Answers 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/

share|improve this answer

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.
share|improve this answer
    
+1 for knowing how to properly deal with runaway transaction logs –  Stephen Jennings Sep 15 '10 at 2:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.