Questions tagged [database-size]
The database-size tag has no usage guidance.
153 questions
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 ...
- 573
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 ...
- 923
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?
- 435
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 ...
- 393
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::...
- 333
18
votes
2
answers
25k
views
Why doesn't DELETE + REORG free diskspace (DB2)?
In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?
The ...
15
votes
4
answers
36k
views
Show data and disk use breakdown by table
I have a SQL Server 2008 R2 database being used by several deployed programs.
Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and ...
- 253
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?
stevenatadv
12
votes
1
answer
9k
views
MySQL Workbench Database Sizes
I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using.
Does anyone know of an easy way to figure this out?
If nothing else, the default location mysql/...
- 323
12
votes
3
answers
9k
views
PostgreSQL Initial Database Size
There are 2 parts to my question.
Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?
I'...
- 783
12
votes
2
answers
23k
views
Proper storage size estimation technique for PostgreSQL
We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we ...
- 229
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 ...
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 ...
- 1,505
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 ...
- 621
8
votes
2
answers
39k
views
Table has 14 GB in unused space - How to shrink table size
I use the following script to gather data from my database wich is SQL Server on Azure.
-- Script to run against database to gather metrics
CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved ...
- 555
7
votes
2
answers
8k
views
Manually increase PostgreSQL's table size limit of 32 TB?
I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database ...
user15267
7
votes
3
answers
21k
views
How do I get the size of a Vertica database?
I have a Vertica database. How can I tell how big the database is?
I need a rough answer for the purposes of reporting basic statistics about our environment to upper management.
- 14.8k
7
votes
2
answers
6k
views
Find the largest databases on my server
I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?
- 161
7
votes
1
answer
14k
views
Pages to MB, multiple and divide or just divide?
When you are using things like sys.database_files it gives size of the file, in 8-KB pages, while many other things you are comparing to it to are in MB.
There are a number of ways to convert the 8-...
- 6,298
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://...
- 201
6
votes
1
answer
1k
views
Using an RDBMS for querying tens of Terabytes of time-series data?
I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote:
(...) opinions regarding best of
breed data structures to be used for indexing time-series (aka
...
- 2,420
5
votes
3
answers
16k
views
what is causing arithmetic overflow in the query below?
when I run the following query on MY_Database
select * from sys.sysfiles
I get the following results:
But when I run a dynamic query that gets the percent of free space I get:
Msg 8115, Level ...
- 16.6k
5
votes
1
answer
11k
views
Querying True Physical Database File Sizes
On querying database file sizes...
1) Most if not all of these answers do not always match the physical file size shown in file explorer:
https://stackoverflow.com/questions/5945360
The accepted ...
- 2,130
5
votes
1
answer
8k
views
what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server
When this command is run, there is SQL server error
'Arithmetic overflow error converting expression to data type int'.
SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg ...
- 207
5
votes
1
answer
5k
views
Oracle Database 12c: Unable to resize system tablespace
Problem:
My java application is returning ORA-01691: unable to extend lob segment errors for every insert
Cause:
After running:
SELECT *
FROM dba_tablespace_usage_metrics
ORDER BY used_percent ...
- 199
5
votes
2
answers
5k
views
My database dramatically increased in size. Why?
I found that my database, specifically the USERS tablespace, dramatically increased in size.
How can I find what DML operations are performing on the objects located in this tablespace?
- 1,717
5
votes
2
answers
3k
views
PostgreSQL relation size does not sum up
I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:
=> SELECT pg_size_pretty(pg_total_relation_size('...
- 11.9k
5
votes
1
answer
1k
views
What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?
We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, ...
- 987
5
votes
1
answer
5k
views
What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?
I am learning dynamic tree-structure organizations and how to design databases.
Consider a DBMS with the following characteristics :
file pages with size 2048 bytes
pointers of 12 bytes
page header ...
5
votes
2
answers
3k
views
Postgres reported table / db size varies wildly from dump size
I have a very strange misreporting of the size of one specific database.
According to \l+ in psql the DB size is 292 MB.
Doing a sum over the sizes reported by the following statement also reports ...
- 153
4
votes
2
answers
22k
views
Estimate a Database Size
I have to do some project and need to estimate the DB size.
I'm going to use Oracle in the project because we will have a lot of transactions and data.
But in the "presentation" I need to specify, in ...
- 155
4
votes
2
answers
3k
views
SQL Server table with 4,000,000 rows is 40GB
I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just ...
- 175
4
votes
3
answers
6k
views
Releasing free space in mdf file to OS [duplicate]
We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.
Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at ...
- 1,399
4
votes
1
answer
1k
views
SQL Server DB table data size query result not increasing if data count increasing
I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing
TableName ...
4
votes
3
answers
5k
views
SQL Server 2008 R2 Express size
I have what I believe is SQL Server 2008 R2 Express installed on a production server. When I connect to it with SQL Server Management Studio it shows up as SQL Server 10.50.1600.
If it is in fact SQL ...
Norm Woodward
4
votes
1
answer
2k
views
Why does an sqlite3 .dump and restore result in a smaller database file size?
I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was ...
- 345
4
votes
1
answer
3k
views
How large is SQLites minimum data storage overhead and what generates them?
Motivation:
In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
- 143
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 ...
- 191
3
votes
2
answers
2k
views
Shrinking sql databases - bad idea? [duplicate]
I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information).
My server ...
- 217
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 ...
- 1,629
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:
...
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?
- 727
3
votes
1
answer
368
views
How can I verify that my SQL Server edition is Express? [closed]
I have a SQL Server (version 11.0.5058). I tried to restore a database from a bak file and I got an error saying:
CREATE DATABASE or ALTER DATABASE failed because the resulting
cumulative ...
- 135
3
votes
2
answers
218
views
Finding integer database columns that may have their data type changed to reduce size
I have a database schema that I've inherited where all the integer columns have been defined as int but because of business rules many could be replaced by tinyiny and other data types that would ...
- 193
3
votes
1
answer
2k
views
Why is my database 12 times bigger than inserted data?
I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.
The point is: from 50 mb plain data (~700,000 lines), ...
- 45
3
votes
2
answers
3k
views
Huge disk space usage increase when altering a column
I hope this question is easy for someone to answer :)
I have a table that looks like this (unimportant columns hidden)
CREATE TABLE [dbo].[Log](
[LogID] [int] IDENTITY(1,1) NOT NULL,
--...
- 241
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 ...
- 825
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, ...
- 33
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
...
- 219
3
votes
1
answer
459
views
How many blocks is my database
How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)
- 75