Questions tagged [disk-space]
For questions about the storage space used by databases or database objects.
371
questions
0
votes
1
answer
310
views
How to add a volume to current in use MySQL database
related to the problem I faced here No space left on device, I want to adopt a long term solution about the point no space left on device. One of the action includes to add a volume to the machine in ...
CommunityBot
- 1
0
votes
2
answers
334
views
Disk space and performance issues while updating a PostgreSQL table with over 120 million rows
I have a single table with an approximate size of 85gb and roughly around 120 million of rows (in a PostgreSQL (12.8) database).
I need to update all 120 something million rows with an update query to ...
- 1,653
2
votes
5
answers
132
views
Investigate why SQL Log file increased in size
We had one or more of our log files increase in size over night, causing our SQL logs disk to almost fill up. I've been asked to look at what could have caused this.
Can anyone suggest where to start ...
- 5,791
2
votes
1
answer
1k
views
Mongodb replica set primary and secondary node conf fail when one slave is down
In my env, I had created MongoDB replica set conf for high availability so one server fails should be handled by other also in my conf I have 3 DB server and 1 arbiter too but,
my slave's server ...
CommunityBot
- 1
1
vote
0
answers
18
views
PG: How to release disk space after truncating string columns [duplicate]
select version()
PostgreSQL 15.3 (Ubuntu 20.04) on x86_64-pc-linux-gnu 64-bit
We have an application that stores big jsons in result TEXTFIELD.
Data inside this column looks like this:
{"data&...
0
votes
1
answer
31
views
Why do updates to table increase the relation size?
In my Postgres database have a large table execution_transcripts with several columns. Two of those columns, task and result are JSONB columns holding values that are often in the kilobytes range and ...
- 9,230
0
votes
2
answers
51
views
Is FILEPROPERTY only used for database_files dmv?
I'm trying to mix some queries I created in the past, and some that I stole over the network to give me mount points size and free space together with database size and free space of the files.
Only ...
- 88.3k
0
votes
1
answer
463
views
optimizing or estimating postgresql table size on disk
This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there.
I have a table of web ...
CommunityBot
- 1
0
votes
1
answer
909
views
Limit size of WAL files for PostgreSQL database
I'm using AWS RDS PostgreSQL 13 database in a master-slave configuration using logical replication with pglogical extension.
Recently, one of the application migrations has updated the schema of the ...
CommunityBot
- 1
0
votes
2
answers
361
views
Mysql - tablename.ibd file is huge in size
I'm using MySQL 5.6 and innodb file per table has been enabled.
I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB.
I have done a huge ...
CommunityBot
- 1
1
vote
1
answer
127
views
How much extra disk space should a postgresql server have?
Suppose you have a PostgreSQL database of total size X hosted on a local server (i.e. expanding disk space in an emergency is difficult). How much space should the server actually be allocated to be ...
- 54.9k
0
votes
2
answers
1k
views
ORA-01654: unable to extend index by 8192 with enough free space
I'm working in Oracle 19c DB. Block size is 8k.
I'm trying to insert new data in my table. I have enough free space (according to dba_free_space more than 2 GB).
But I've got ORA-01654: unable to ...
CommunityBot
- 1
0
votes
0
answers
261
views
Upon PC restart, MySQL service on local computer "started and then stopped"
I'm working in MySQL on a local (Windows) computer. I have a somewhat recurring issue I have with managing the server. I cannot replicate this error because I do not know what causes it.
Sometimes, I'...
- 1
0
votes
0
answers
46
views
How to Determine the Low High Water Mark (LHWM) in Oracle DB?
How could one determine the position of the Low High Water Mark (LHWM) for a non-partitioned heap table segment in Oracle?
I am concerned, that if we only do direct path inserts and never update, ...
- 123
0
votes
1
answer
4k
views
Drop tables but space not claimed in postgres 12
I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option.
So basically I am having two data directories i.e. One is old data directory (v9.5) ...
CommunityBot
- 1
-1
votes
1
answer
751
views
How to reduce big size database files (Data File ) SQL Server without SHRINK?
I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question
I have a big database with almost 8TB of Data File Size ...
CommunityBot
- 1
0
votes
1
answer
3k
views
How can I find total used and remaining space in Azure SQL MI
I queried sp_databases and the sum of space occupied by all db's was 380Gb. The total allocated space for the managed instance is 800Gb.
But, enquiring with the infra team found that we have only ...
- 147
0
votes
0
answers
60
views
I increased my NDF files from 3 files to 7 files. How to re-distribute the data across MDF and NDF files?
I have a big database that uses 1 MDF files and 4 NDF files. The MDF file is placed in a 2TB disk and NDF files are placed in 500GB disks separately. I set the MDF file to be unlimited, while the NDF ...
- 1,750
4
votes
2
answers
20k
views
In Postgres, how do I adjust the "pgsql_tmp" setting?
I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full.
myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail ...
- 119
1
vote
1
answer
932
views
MS SQL show percent extents changed per table
I've got differential backups set up for MS SQL Server 2022 and would like to know how much each table in the database is contributing towards the size of the differential backup.
The page below shows ...
- 88.3k
0
votes
1
answer
99
views
sql server backup restore multiple require disk space
I took a full backup file of a live system, approximately 210 GB. It's in the downloads folder with the .bak extension. I'm trying to install it locally with the restore database in SQL Server on my ...
- 38.7k
3
votes
2
answers
3k
views
Is there a way to get a history of transaction log used space in SQL Server?
I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually ...
- 16.4k
14
votes
1
answer
7k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 180k
1
vote
1
answer
685
views
How to find whole cluster size in PostgreSQL?
I know how to find relation and database size using pg_total_relation_size(), and pg_database_size(). But I want to find whole cluster size. Is there a way other than calculating disk space of data ...
- 180k
0
votes
0
answers
72
views
Sql server multi-instance drives and controllers
I was wondering what is the best practice for drives and controllers when you are installing multiple Sql instances engines.
Are the similar pros and cons as in having different drives and controllers ...
- 101
0
votes
1
answer
103
views
Why does adding a new UniqueIdentifier column to my existing SQL Server table increases data space of the table by 100%?
I have an existing table and once I add an ObjectId UniqueIdentifier column with default NewSequentialId() value, the data space suddenly doubles. I'm using the script below:
ALTER TABLE Employee
ADD ...
- 26.6k
3
votes
1
answer
336
views
Row greater than max size (8060) but I can't see why
I have a table that has 483 columns.
A date not null, a char(6) not null, and then 481 x float sparse null.
By my maths, even if every sparse float is non-null, the row should still only be:
date: 3 ...
- 88.3k
0
votes
1
answer
227
views
Does a long string with "unique" constaint take a lot of space?
I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:
global_settings:
id primary key,
key ...
- 180k
0
votes
2
answers
44
views
Suprising gained disk space after backup & restore PostgreSQL databases
We backed up, deleted and restored several databases. Surprisingly, the databases occupy 20% less storage space afterwards. Have we lost data or what could have happened here? Autovacuum runs ...
- 40.7k
0
votes
2
answers
4k
views
msg: Cannot execute SQL 'COPY "my_table" FROM '/path/big_file.csv': ERROR: Could not extend file "base/A/B": only C bytes of D written in block E
I am running an ansible playbook. The machine running ansible is localhost.
The playbook breaks during a task which loads data from a big csv (1.2 GB) into a postgres database table ( module community....
- 54.9k
3
votes
3
answers
13k
views
PostgreSQL: Foreign key on string column
I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it.
So for example:
create table "Item"
(
"...
- 2,869
1
vote
1
answer
2k
views
Show size of tables and other database objects in Postgres
I need to clear disk space, so I want to see which tables and other objects are using the most disk space in my Postgres databases.
Ideally, I'd like to see: database name, schema name, object type, ...
CommunityBot
- 1
0
votes
0
answers
149
views
octet_length and pg_column_size are too small for integer
My postgres ID field is an integer. Just a regular integer as far as I can tell, not a bigint or smallint.
\d my_table
Table "public.my_table"
...
- 203
5
votes
2
answers
8k
views
How to free up hard drive space after deleting millions upon millions of records in mariadb
We have an application where we are receiving information seconds apart, we record this information in a table that we call events, which currently weighs 240 GB, this is the largest we have so far.
...
CommunityBot
- 1
6
votes
1
answer
11k
views
Character varying index overhead & length limit
I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.
I have read about B-Trees, but I can't determine how much disk ...
- 180k
49
votes
5
answers
74k
views
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 ...
- 32.4k
2
votes
1
answer
1k
views
Difference between total table size and size of all pages in postgresql
Working with Postgres I noticed the following:
When I run this query I get the total table size:
SELECT pg_size_pretty( pg_table_size('tenk1') );
I get 384kb.
And when I run:
SELECT relpages FROM ...
- 180k
0
votes
2
answers
715
views
What causes disk space growth from Informatica load onto SQL Server?
We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
- 2,201
2
votes
2
answers
2k
views
How defragment compressed partitioned table in oracle?
I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database.
Delete by key and direct insert was used to populate a data for the table. So the table is ...
- 33
0
votes
1
answer
225
views
Same data stored in more rows with more repetition results in smaller SQLITE file?
I'm comparing two very barebones SQLite databases with the same data stored differently.
Database A has tables like this:
string (TEXT UNIQUE)
count_1 (INTEGER)
count_2 (INTEGER)
the
23
10
it
56
44
...
- 1,653
0
votes
1
answer
222
views
best practice size ratio of $PGDATA vs archive/backups for postgres [closed]
we use separate logical volumes for postgresql
the data directory
a directory for daily (compressed) pg_basebackups, which are being sent to an offsite datagrave once created plus archives (also ...
- 54.9k
1
vote
1
answer
217
views
PostgreSQL: smallint vs "char"
CREATE TABLE test_size1 AS SELECT (random() * 1e3)::int id, (random() * 10)::smallint col1, (random() * 10)::smallint col2 FROM generate_series(1, 1e6);
attname | typname | typalign | typlen
--------...
- 54.9k
0
votes
0
answers
217
views
Trying to increase disk with GPT for SQL Server data, do we need to configure anything to SQL Server Application
I am not a DBA, but SQL Developer.
Our company has a Azure VM that has a very active SQL Server 2008.
Currently, a disk drive (H:\ drive) that holds SQL data is getting full (up to 2 TB, possibly due ...
- 249
0
votes
1
answer
564
views
Managing disk space on a SQL Server database server with limited storage
I am currently working on a SQL Server database server that has multiple drives and databases. The server is running low on disk space due to some of the databases growing unexpectedly. Unfortunately, ...
- 360
1
vote
1
answer
188
views
/var/lib/mysql/mysql.ibd big after disabling performance_schema
What I think is the reason for this issue
Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.)
So, ...
- 78.9k
0
votes
1
answer
47
views
Drive with SQL binaries out of space
What happens if the drive (not C-drive) where the SQL binaries are located runs out of space?
Can this take down a production server?
- 41
0
votes
2
answers
405
views
SQL database is taking up 1.5 TB but only using about 1 TB
SQL Server is almost 98% full. Its one database is taking up 1.5 TB but only using about 1 TB. Can we get any of that space back, if so, how? We are using Azure SQL Managed Instance.
File Growth ...
- 2,412
0
votes
0
answers
36
views
Optimization of google query with multiple LEFT JOIN and OR operators!
I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
- 1,750
2
votes
2
answers
465
views
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
- 263
0
votes
1
answer
198
views
EMStagePatches_$ORACLE_SID is using up space
We have this directory:
$ORACLE_HOME/EMStagePatches_$ORACLE_SID
We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do with ...
CommunityBot
- 1