Questions tagged [disk-space]
For questions about the storage space used by databases or database objects.
360
questions
1
vote
1
answer
85
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 ...
0
votes
0
answers
33
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
53
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 ...
Shackle Shot
3
votes
1
answer
114
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 ...
- 133
0
votes
1
answer
57
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 ...
- 35
0
votes
2
answers
32
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 ...
- 1
0
votes
2
answers
987
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....
- 101
0
votes
0
answers
49
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"
...
- 193
0
votes
2
answers
467
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 ...
2
votes
1
answer
692
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 ...
Piet Pro
0
votes
1
answer
150
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
...
- 13
0
votes
1
answer
116
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 ...
- 175
2
votes
2
answers
1k
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
1
vote
1
answer
114
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
--------...
- 13
0
votes
0
answers
135
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 ...
- 213
0
votes
1
answer
155
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, ...
- 1,159
0
votes
1
answer
36
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?
- 795
1
vote
1
answer
88
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, ...
- 21
0
votes
0
answers
34
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
0
votes
1
answer
109
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
2
votes
2
answers
262
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 ...
- 181
0
votes
0
answers
240
views
SQL Server FILESTREAM Object Garbage Collection in a Always On Availability Group
We have a production SQL Server 2014 running on FILESTREAM with Always On Availability Group.
It has been about 4 years since the servers go live. Recently, we encounter a hard disk space issue on the ...
- 11
0
votes
2
answers
268
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 ...
- 289
0
votes
1
answer
289
views
Is there a way to VACUUM an sqlite3 database in parts to avoid OperationalError: database or disk is full/reduce size of sqlite3 databse
I have a rather large sqlite3 database that I've built/populated through python over a few months. I have done many inserts/deletes etc while putting it together and it now uses the majority of my ...
- 101
0
votes
1
answer
231
views
VACUUM FULL using another disk
Current database takes almost all of SSD free space ~802GB. 800 GB of memory is taken by single table and it was never vacuumed since 2019. Recently i deleted old rows from the table and now i need to ...
2
votes
1
answer
136
views
SQL Server data file creates 50% unallocated bloat when I create clustered index
My company has relatively large datasets with clustered indexes currently being built on a char(40) field. In order to maintain disk space and streamlining backups, I currently have a "one table ...
- 23
0
votes
1
answer
753
views
Drive space issue
Hello I have installed a Zabbix Server 3 months ago with 32GB space disk, I have 200 hosts supervised on this server.
And I got this message from the "system information" part :
Error in ...
0
votes
1
answer
648
views
How to permanetly turn of transaction logging for specific table in Db2?
In our company we are merging two systems together. Data are required to be moved from Microsoft SQL Server 2016 source database into Db2 v11.5 target database. In production environment this is going ...
- 327
0
votes
1
answer
514
views
Table with smaller data types seems to take more space on disk?
I have these two identical tables:
Table "public.region"
Column | Type | Collation | Nullable | Default
-------------+----------+-----------+----------+---------
...
- 235
2
votes
1
answer
2k
views
PostgreSQL crash due to lack of device space
We run PostgreSQL 11 under a CentOS 7 server.
Recently, we had several warnings in the log that caused the PostgreSQL server to crash:
2022-05-09 23:26:29 EDT pg_restore db123 132.132.132.32 ...
- 1,775
5
votes
3
answers
4k
views
SQL Server / SSMS taking up lots of disk space
I installed SQL Server + SSMS for my univ class and ever since then my disk space is going missing (every so often, approx. 3 gb of space goes missing). I strongly suspect SQL Server and/or SSMS to be ...
4
votes
2
answers
5k
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.
...
6
votes
1
answer
2k
views
Does UPDATE write a new row version for unchanged, TOASTed values?
I am working with a PostgreSQL table with a large TEXT field, which is theoretically updated on a regular basis. I have thought about storing the data directly in the filesystem, but with TOAST, the ...
- 183
0
votes
1
answer
327
views
SSMS -- inhibit results or remove temporary files when query is done
I am running several instances of the same query to establish baselines for potential performance optimization. That is, I want to compare timing for two versions of the same query.
I am using SSMS ...
- 147
1
vote
1
answer
1k
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, ...
- 473
2
votes
1
answer
441
views
Postgres WAL full and database stopped/exited with error - No space left on device
I am new to postgresql.
We had a full disk issue on PostgreSQL DB where data and WAL was set.
WAL was taking almost 65 GB. This is a lower environment and we don't need to restore to a point. Compared ...
- 21
0
votes
0
answers
185
views
Excessive TempDB Usage When Using Pivot + Temp Tables
I have a stored procedure that is acting quite strangely, it basically works in this way:
--Populate #tmpActualsVertical with data(in this example 84,652,788 rows)
SELECT ...
- 205
2
votes
2
answers
547
views
Does Postgres protect the hard drive from repeated writes to the same memory cells?
If I have the same row in the same column in the same table to which I write 4 bytes (an integer) 100,000 times a day, will that wear out the SSD? For an SSD 400 kb per day is nothing, yet writing it ...
- 801
0
votes
0
answers
168
views
SQL Server 2014 - Disk Space Issue
Currently we have a drive on your server that stores all user DB's data files along with one tempdb data file. (D: drive) It has 1.4 TB total space with only 98Gigs free space.
The biggest DB size is ...
- 83
0
votes
2
answers
139
views
Free disk space on SQL without impacting application
We have 1 TB disk which is almost full(85%). We're going to free up disk space without any impact on application availability with below approach. Please suggest if this approach looks good or there ...
- 135
0
votes
1
answer
516
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 ...
- 13
2
votes
2
answers
972
views
Reduce the size of a primary key Index
I'm using Azure SQL DB and noticed that one of my largest databases appears to have a primary key index with a total size of 6Gb. It's a clustered index on a uniqueidentifier. However, the total rows ...
- 123
9
votes
2
answers
1k
views
Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?
PostgreSQL allows time/timestamp to specify a precision:
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds ...
2
votes
1
answer
263
views
Does a fragmented database cause the transaction log to grow faster?
Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the ...
- 21
0
votes
1
answer
782
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 ...
- 101
0
votes
1
answer
507
views
SQL Server data drive (.mdf files) running out of space
We have a D drive that has 999 GB of total space. Currently the production database data files are being stored there. It is running out of space.
A few weeks ago DB files were shrunk to temporarily ...
- 83
0
votes
3
answers
436
views
RMAN configuration suggestions to help save our storage space?
To preface...
Our Senior Oracle DBA has just recently passed away, and I've been taking over. My level of experience is just a junior level. So there is a lot I still don't understand. So I appreciate ...
- 11
0
votes
1
answer
140
views
Estimate Oracle tablespace required before loading data
I have files ranging from 450 to 750 million rows, which I need to load into our Oracle environment. The DBA asked me to give an estimate on how much tablespace I would need.
How to estimate the ...
- 101
0
votes
2
answers
58
views
Which Oracle directory takes up the most space and why?
Does any directory file in Oracle occupy the most space? Why does it takes up the most? e.g. system
- 1
0
votes
0
answers
233
views
Store Elasticsearch shards in separate partitions on data node
I have an Elasticsearch cluster configured with one head node and three data nodes with the number of replicas set to 2. The data nodes are each split into three partitions called /data1 /data2 and /...
- 261