Questions tagged [disk-space]
For questions about the storage space used by databases or database objects.
371
questions
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 ...
- 21
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 ...
- 103
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 ...
- 7,436
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 ...
- 111
0
votes
0
answers
45
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
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
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 ...
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 ...
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 ...
- 141
1
vote
1
answer
683
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
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 ...
Shackle Shot
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 ...
- 133
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 ...
- 45
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 ...
- 1
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....
- 101
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
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 ...
- 110
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 ...
Piet Pro
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
...
- 13
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 ...
- 195
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
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
--------...
- 13
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, ...
- 1,199
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?
- 819
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, ...
- 21
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
0
votes
2
answers
333
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
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
0
answers
356
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
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 ...
- 301
0
votes
1
answer
579
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
423
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
209
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
1k
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
922
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 ...
- 357
0
votes
1
answer
634
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,815
5
votes
3
answers
7k
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 ...
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.
...
6
votes
1
answer
3k
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
489
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 ...
- 157
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, ...
- 483
2
votes
1
answer
729
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
252
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 ...
- 215
2
votes
2
answers
863
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 ...
- 811
0
votes
0
answers
189
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
152
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