Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [disk-space]

For questions about the storage space used by databases or database objects.

376 questions
138
votes
5 answers

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor. I need all of the fields (or nearly…
Joe
  • 1,655
  • 2
  • 11
  • 14
50
votes
5 answers

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a…
bumble_bee_tuna
  • 977
  • 2
  • 11
  • 20
37
votes
1 answer

VACUUM returning disk space to operating system

VACUUM usually does not return disk space to operating system, except in some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will…
Vadim Samokhin
  • 635
  • 1
  • 8
  • 13
36
votes
1 answer

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I should have used truncate, but I didn't know…
arcull
32
votes
6 answers

Query to report disk space allocation and used space

We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage). I'd like to write a query (report) for a single database indicating the "Currently allocated space" and…
JustBeingHelpful
  • 2,116
  • 18
  • 45
  • 61
25
votes
2 answers

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and re-import the whole database (which has several…
rubo77
  • 816
  • 2
  • 13
  • 24
20
votes
1 answer

Postgres: check disk space taken by materialized view?

I know how to check the size of indexes and tables in Postgres (I'm using version 9.4): SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class WHERE…
Richard
  • 343
  • 1
  • 5
  • 11
18
votes
3 answers

Why might a table's data space take up 4x the size of the raw data?

I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in the VARCHAR field is about 21.5, so the raw data…
Jon of All Trades
  • 5,987
  • 7
  • 48
  • 63
18
votes
1 answer

Safe way to truncate SQL Server Error Log

We are running out of space. What is the safe way to clear the error log?
aron
  • 595
  • 2
  • 6
  • 8
16
votes
2 answers

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for some more space. This datadir is a file system…
atxdba
  • 5,293
  • 5
  • 41
  • 62
16
votes
1 answer

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The disk space was released after ~12 hours, without…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
16
votes
2 answers

How to free up disk space? which logs/directories to clean?

I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size /u01/app/11.2.0/grid/cv/log /u01/app/11.2.0/grid/log/diag/tnslsnr/r1n1/listener_scan2/alert (Contains xml…
kupa
  • 1,737
  • 14
  • 39
  • 55
15
votes
2 answers

What effect will reducing the size of a varchar column have on the database file?

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes down to more reasonable levels. The 'how' to do…
nateirvin
  • 756
  • 1
  • 6
  • 22
15
votes
2 answers

Freeing disk space after dropped database

I'm working on a dev system, and I have been restoring to a database, say "foo", that I'm using for dev purposes. As I'm working through the kinks, I've just been running DROP DATABASE foo. However, I quickly realized I ate up all the space on my…
Jmoney38
  • 1,175
  • 5
  • 13
  • 22
15
votes
1 answer

How to go about extending volumes on SQL Server boxes?

Here is a question for the sys ops out there (or those who have gone through this experience before). My company wants to allocate additional disk space on existing SQL Server boxes in production. We are wondering whether extending a volume…
Martin Surasky
  • 773
  • 1
  • 7
  • 16
1
2 3
…
25 26