Questions tagged [database-size]
The database-size tag has no usage guidance.
153 questions
0
votes
1
answer
61
views
When should sp_spaceused be used to get the size of a database, instead of querying sys.database_files?
I frequently find the output of sp_spaceused misleading. What appears to be the size of all data in a database is actually the combined size of both the data file and the log file. I have recently ...
- 180
0
votes
2
answers
3k
views
How to reduce the size of a SQL Server-Database?
Context
We have a Backup DB, where we normally store BackUps of tables from production DB before doing any updates/deletes. If anything goes wrong, we can restore the data from that table created in ...
CommunityBot
- 1
3
votes
1
answer
176
views
Does rebuilding a clustered index offline require extra space for each non-clustered index?
I have an uncompressed clustered primary key. It consumes 63.6 GB in the main part of the clustered index with 17.9 GB LOB. The table's only non-clustered index is 57.3 GB also with 17.9 GB LOB. I ...
9
votes
1
answer
2k
views
AWS RDS postrgres massive disk use, small tables
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
CommunityBot
- 1
47
votes
5
answers
61k
views
I need to run VACUUM FULL with no available disk space
I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
- 239
0
votes
1
answer
22
views
What is a viable low-cost DR option for a large cluster?
We have a Cassandra cluster running on GKE with a 32-CPU node pool and SSD disks. The current cluster size is nearly 1 PB, with each node utilizing an average of 5 TB on 10 TB allocated SSD disks. The ...
- 4,155
0
votes
2
answers
152
views
Database size is far larger than sum of tables, even after VACUUM FULL?
Here is my query and its output in psql, run as an admin on that database.
There is a massive discrepancy between the 'total size of the database' at 50GB, and the sum of the tables, ~7MB.
This is ...
- 182k
2
votes
1
answer
359
views
Why there is huge gap between allocated and used size in a database
I want to find total used and remaining space for each database in Azure SQL MI.
For that, from an example, when I right click on a database and select properties I see following example output, where ...
- 90.2k
1
vote
1
answer
181
views
how to find out the free space inside a database file when the database is not online?
Normally when the database is online
I use the following query and get how much space is free inside each file:
SELECT CONVERT (decimal(12,2) ,
(CONVERT(...
0
votes
1
answer
118
views
Can a postgres autovacuum increase the TOAST size of the table?
A table toast size goes from 7.5Go to 9.2Go in one hour. The normal database activity seems not responsible for that: no more queries than usual. To be the cause, it would need a larger amount of ...
- 56.4k
2
votes
1
answer
97
views
Why is my composite index on 3 int columns smaller than the PK index on 1 int column?
I have a table called data_table. Below are schema, indexes, and their sizes.
Table "public.data_table"
Column | Type | ...
- 182k
0
votes
1
answer
111
views
Create DB: Initial Size vs Max Size
In SQL Server, when creating a database we can specify the (initial) SIZE and the MAX_SIZE. I don't understand the difference.
When I create the database, it's empty. So it's size is zero.
Therefore, ...
- 9,244
2
votes
2
answers
1k
views
Insert 2 output result sets from sp_spaceused into Temp Table (SQL Server non-2016)
sp_spaceused, when executed without any parameters, produces 2 result sets
database_name, database_size, unallocated_size
reserved, data, index_space, unused
Usually when you execute some SP, you ...
- 328
1
vote
1
answer
840
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 ...
- 182k
0
votes
1
answer
89
views
How to add information about two same named databases on different hosts in PgBouncer config file?
I want to add details for databases in a config file of pgbouncer.
I have two same database names but their hosts are different, as there is no information about server endpoints in the config file, ...
- 9,030
0
votes
1
answer
2k
views
Oracle XE 18c database size limitation
According to official Oracle documentation available at https://www.oracle.com/database/technologies/appdev/xe/faq.html it has limitations:
What are the resource limits for Oracle Database XE Oracle ...
CommunityBot
- 1
3
votes
1
answer
15k
views
How to know DB size using backup file without restoring it
Is there anyway so that we can estimated/know size of database using backup file without restoring it?
- 3,119
0
votes
1
answer
387
views
How difficult and how much time should it take to export a DB to a flat file?
How difficult of a process is it to export a database to a flat-file? Should it take seconds, minutes, hours, or days to complete an export?
I have no knowledge of the database structure, beyond the ...
- 39.5k
10
votes
2
answers
20k
views
How to free the unused space for a table
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.
I use SQL Server 2014 Express edition with 10GB limit of ...
- 90.2k
0
votes
0
answers
162
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
- 131
2
votes
1
answer
7k
views
Script to monitor database growth on daily basis
I am fairly new to databases, I have got a specific requirement to script a Job/Stored Procedure to monitor the database growth. We are currently using MS SQL Server 2016 Standard Edition with Always-...
- 5,964
0
votes
0
answers
931
views
PostgreSQL Database Data and Log size
I am looking for a way to list PostgreSQL Database Data and Log sizes.
Using the following command we can check the total database size based on the documentation https://pgpedia.info/p/...
- 170
0
votes
1
answer
479
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 ...
- 182k
1
vote
2
answers
2k
views
Postgres 10.3 : table grows in size after delete-insert
I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it.
I have a table (around 3 million rows and 200 columns). In order to populate fresh ...
- 90.2k
0
votes
1
answer
264
views
Issue with table size after upgrading to MySQL 8
I have two MySQL servers. One is running on 5.7.24 and other one is running on 8.0.29 version.
In 5.7.24 I have table with 71M (millions) records and its size is about 6GB. The same table on 8.0.29 ...
- 79.4k
0
votes
2
answers
5k
views
Postgres: How to find where database size growth is coming from
We have a PostgreSQL database that has grown significantly in size recently, from about 340GB to 571GB over the past couple of months, and we are not tracking any significant change in user behavior ...
- 101
0
votes
1
answer
347
views
PostgreSQL DB size estimation
We are doing POC for DB selection. Based on our use case we got three recommendation
Postgres
Cassandra
Azure SQL
We are not going with Cassandra since we are not matching minimum requirements and ...
- 90.2k
3
votes
1
answer
6k
views
How to estimate database size needed?
I work on a transaction banking service project, and we need to have a central database to keeping client's info, account info, transaction details, etc.
So, how can I estimate the size of database ...
- 90.2k
4
votes
1
answer
6k
views
Database size twice as large after mysql dump and re-import
After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be?
This is occurring when importing the sql dump right ...
CommunityBot
- 1
19
votes
1
answer
15k
views
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::...
- 182k
3
votes
1
answer
2k
views
SQL Server :: How many 8K pages are taken by this record?
I would like to see how many 8K pages are taken by a specific record.
If you run this query on AdventureWorks2019:
SELECT ProductID
,Name
,DATALENGTH(Name) AS SizeInBytes
,LEN(Name) AS ...
- 29.8k
1
vote
1
answer
1k
views
Why is the per-row size returned by pg_table_size greater than pg_column_size(row)?
I have a table with only integer and smallint columns, adding up to 20 bytes. pg_column_size reports 44. This is expected due to the 24-byte per-row overhead, as explained in Difference between ...
- 56.4k
12
votes
1
answer
25k
views
How to get actually allocated tablespace sizes?
A Postgres tablespace is cluster-wide.
Therefore, how do I calculate how much space is allocated by each database in Postgres tablespaces?
- 182k
0
votes
0
answers
332
views
varbinary(max) - table size
I have an application database (not of my design) with a table for storing files in a varbinary(max) column. These databases show to take up 2X the actually required space (i.e. shrink shows give/take ...
- 2,201
1
vote
1
answer
134
views
How to index when partitioning for start and stop time columns in PostgreSql 12
I have a log table that takes inserts at a rate of 5k-10k rows per second and number of records grow upwards of 20 Million every 24 hrs. Here is the table structure:
CREATE TABLE IF NOT EXISTS public....
- 11
27
votes
5
answers
41k
views
Are junction tables a good practice?
Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.
In order to retrieve the data ...
- 70.5k
10
votes
3
answers
2k
views
DB backups have suddenly gotten smaller
About 8 GB has been trimmed off of the size of our main database. This is reflected in backups and when we look at free vs available space.
Everything seems to be working and we can't find any sign ...
- 90.2k
1
vote
1
answer
707
views
Database is 300 GB, but I only see small tables?
I wonder why my database is so large. I type:
SELECT pg_size_pretty(pg_database_size('defaultdb'));
and it says defaultdb is 259 GB. That is huge.
So I list my tables in psql with:
\dt
For each ...
- 182k
3
votes
1
answer
402
views
Estimate database growth caused by Accelerated Database Recovery
Accelerated Database Recovery (ADR) is on by default in Azure and an option in SQL Server 2019. It has some really interesting potential.
I have experimented by turning ADR on in a static test ...
- 90.2k
3
votes
2
answers
1k
views
Free some space on a full Express Edition instance
Using Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Express Edition (64-bit)
I have a database that reaches the maximum space (10Gb) every month.
In order to free up some space I do a bacpac, ...
- 113
33
votes
6
answers
172k
views
Shrinking the log file does not reduce size
I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.
When I try to shrink the log file, it's not shrinking.
I know shrinking a ...
CommunityBot
- 1
3
votes
1
answer
981
views
Unexpected result from PostgreSQL information schema
I have written a simple query that should show all table sizes of any given schema in human-readable format:
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
...
CommunityBot
- 1
31
votes
2
answers
22k
views
How can I determine the size of a dump file prior to dumping?
How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
- 318
0
votes
2
answers
562
views
Analyse differential backup to see why it is so big
We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest.
1 or 2 days after a full backup was taken, the diff backup is bigger ...
- 563
2
votes
0
answers
102
views
How do I measure the overhead of a Postgres extended statistics object?
Some solutions to poor query performance suggest creating extended statistics objects to help the query planner better understand the shape of the data. Because these statistics objects are not ...
- 163
1
vote
3
answers
6k
views
How do we determine data_length + index_length as database size in MySQL
I am calculating my InnoDB database's size with the following syntax:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
...
- 139
0
votes
1
answer
466
views
How to predict database size?
One of requirments for database which I am building is to predict database size to prepare hardware for production environment. Aapplication has 2 main tables wchich are partitioned. Tables are in 8 ...
- 39.5k
6
votes
2
answers
2k
views
Identifying Unused Indexes on SQL Azure
I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information.
See: https://...
- 17.9k
2
votes
2
answers
1k
views
Why is my SQL Server database file so large?
I have a relatively simple database that is over 300MB on disk. When I look at the table data it adds up to just over 5MB. Please see the screenshot below:
This screenshot can also be seen here.
If ...
- 90.2k
3
votes
1
answer
2k
views
Why the sudden increase of my database .mdf file size?
Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below:
...
- 28.5k