Skip to main content

Questions tagged [size]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
831 views

Mongodb index keeps growing in size eventhough the collection has a TTL index

I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes. One of the indexes is a TTL ...
1 vote
1 answer
1k views

MongoDB conditional query for array $size

I have a user collection, and each user has different number of id_cards, all id_cards are stored in an array, I can get all users that have 7 id_card by using array $size operator, how do I get the ...
14 votes
1 answer
14k views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
0 votes
1 answer
2k views

Oracle 11g - Decreasing Big Table Size

I want to reduce the size of a table. I thought if I could do this with export / import, but when I check it with the ESTIMATE_ONLY option, it doesn't seem to work. I have no chance of using ...
1 vote
3 answers
129 views

Azure SQL index rebuild after huge size reduction

We have a table in an Azure SQL database that used to have an nvarchar(max) column storing pdf files. (The wonders of external developers.) The table grew to 156 GB. It has 476000 rows. After changing ...
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 ...
0 votes
0 answers
39 views

/var/lib/mysql grows indefinitely in size

Having the need to upgrade from Ubuntu Bionic to Ubuntu Focal, I made an upgrade from server 5.7 - deduced afterward - to 8.0 by simply inheriting the original /var/lib/mysql directory, that is I left ...
-1 votes
1 answer
33 views

I'd like to know how to monitor tablespaces

I'd like to know how to monitor tablespaces. I don't have much knowledge of Postgres. When I create a tablespace, how can I measure the tablespace size? How can I monitor the size of the tablespace so ...
1 vote
1 answer
65 views

The size of my table is not growing

I couldn't see any growth in table size down to a single byte when I added 1 million rows in the table during the last days. I'm using this query: select table_name, pg_relation_size(quote_ident(...
2 votes
1 answer
47 views

How do you make sure a data page can only have 3 rows

For a table with a setting of (fillfactor = 75) I am trying to make sure that each data page can only have 3 rows. select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012. First 24 is page header ...
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 ...
1 vote
1 answer
213 views

MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table

Recently I switched from MySQL 5.7 to MariaDB 10.8 and it's been generally great as a drop-in replacement, but I've run into a problem: the process for a cron job that daily runs /usr/bin/nice /usr/...
0 votes
2 answers
9k views

Calculating the total row size of a table - SQL Server

I have a table 'Connections' with the following schema in SQL Server: [ID] [int] IDENTITY(1,1) NOT NULL, [User] [nvarchar](20) NOT NULL, [UserID] [int] NULL, [True] [bit] NOT NULL, [Comment] [...
4 votes
2 answers
251 views

Alert on Table size

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
34 votes
3 answers
8k views

Why is the default page size for databases so small?

In PostgreSQL and SQL Server, the default page size is 8 KB, in MySQL is 16 KB and in IBM DB2 and Oracle it is only 4 KB. Why are these page sizes so small? Is there a historical or memory usage ...
0 votes
2 answers
262 views

Row size limits in Mysql w.r.t to TEXT and BLOB columns

There is a well-known limitation in Mysql for row size which 65,535 bytes. While going through the docs, I have found that BLOB and TEXT columns only contribute 9 to 12 bytes toward the row ...
2 votes
1 answer
725 views

Table size is not changed after new column is added

I am trying to check how the table size is going to be increased after adding new column with default values. I use the following default values: NULL, ''(empty string) and some text(some random text)...
5 votes
1 answer
720 views

Rebuild of DB fails, yet size of the DB has doubled

I attempted to rebuild all the indexes of a DB using the query, USE [DB_Name]; GO DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE TableCursor CURSOR FOR SELECT '['+...
1 vote
1 answer
2k views

How to delete MongoDB log files

We are using MongoDB 4.0.4 version for our project. The database configuration is done as below: Created a custom database in MongoDB and the service is running in authentication mode. Using Windows ...
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 ...
0 votes
0 answers
54 views

How to customize jsonb field in postgresql to accept 40GB

Background I am data scientist. I had tried minimize my instance to smaller version, but unfortunately the recalculated result from smaller instance is wrong. After I check with the original value. I ...
1 vote
2 answers
2k views

Size of data in DB is much larger than size of data dumped to disk

I have a table that contains 99.9% of the data in the database. When I query the size of the table and database, I get: mydb=# SELECT pg_size_pretty( pg_database_size('mydb') ); pg_size_pretty ------...
2 votes
1 answer
2k views

Using pg_prewarm to load X number of latest rows into cache

We have a large query that is extra slow when customers "run it the first time, early in the morning..." So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount ...
0 votes
0 answers
52 views

Shrinking log files in SQL Server 2014

I ran sp_Blitz in our Production server over the weekend and it output a few results regarding the log file growth. Example: The database [MYDB] has a 104 GB transaction log file, larger than the ...
137 votes
5 answers
163k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
3 votes
2 answers
3k views

Table size without indexes in sql server

Using the following query I can get the size of the indexes for a table: SELECT i.name AS IndexName, SUM(page_count * 8) AS IndexSizeKB FROM sys.dm_db_index_physical_stats(db_id(),...
0 votes
1 answer
2k views

What is the available SQL Server Express database size? [duplicate]

SQL Server Express allows the maximum of 10GB/10240MB for a database size. I have a database which currently is ~8000 MB in size. The unallocated space is ~7000 MB. The question is: how many megabytes ...
2 votes
1 answer
124 views

Database Size Troubleshooting [closed]

I have a SQL Server database and the .MDF file is almost 9GB in size. However, the data and indexes only seem to be taking up a little over 1GB. I've shrunk the database (with and without ...
1 vote
2 answers
2k views

Calculate size of the filtered rows of a table in sql server

My requirement is to know the size occupied(count of records and size in mb) by a table during every 24 hours, I found query to get the whole size of a table but not sure on where to start with or how ...
1 vote
0 answers
206 views

Postgresql index size: penalty for composite type?

My question refers to Postgresql 12.2. I have two almost identical tables, structure- and contentwise, the main difference being that the primary key of the second table is a composite type, while the ...
0 votes
2 answers
131 views

Size of a MyISAM table doubles after OPTIMIZE

I have this table: CREATE TABLE `foo` ( `CalculatedResultsId` int NOT NULL, `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `SectionData` json NOT NULL ) ENGINE=...
1 vote
2 answers
60 views

MySQL index size doesn't include all values

I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index: mysql> SELECT DISTINCT(COUNT(id)) FROM my_table; +-------------+ | (COUNT(id)) | +------...
1 vote
2 answers
6k views

SQL Server : database free space vs data file free space

I don't understand well why the available space in database is 0% while in file is 98% available. Can you explain or advise? (SQL Server 2019, SSMS the newest, one datafile)
2 votes
0 answers
265 views

How to find the column value with the biggest storage size in the DB?

Is there any way to find out which column value has the largest storage out of all tables in PostgreSQL DB? I'm looking for currently used storage, information_schema.column only shows the length of ...
1 vote
1 answer
2k views

MongoDB: Is it possible to search for two fields whose array sizes don't match?

I have a collection that looks something like the following (I have simplified it a lot). I need to query any record that doesn't have the same number of customersAccepted entries as customersInvited. ...
5 votes
2 answers
2k views

Table size grows indefinitely after deleting and then inserting the same data

I create a table and populate it with data: create table tab as select 'value' || level val from dual connect by level <= 10000; If I then delete all rows and insert the same data: delete from ...
-1 votes
3 answers
12k views

Oracle data file size

My client’s tablespaces are autoextend off and i add monthly or bimonthly 3 or 6 gb space for tablespaces. Can you advise me if there is a recommended datafile size for Oracle 11gR2 on Windows 64 Bits?...
4 votes
2 answers
16k views

Appropriate tempdb size?

DBCC CHECKDB is failing on a particular database because of Could not allocate space for object 'dbo.SORT temporary run storage: 143479689576448' in database 'tempdb' because the 'PRIMARY' ...
1 vote
2 answers
357 views

does pg_dumpall run vacuum?

Can someone confirm if pg_dumpall does a vacuum? I cannot explain the size differences between a local and dumped database but thinking duplicate/rewritten entries are ignored 350mb inside psql with \...
5 votes
2 answers
9k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
10 votes
1 answer
10k views

Estimating the size (of the records) and the overhead in Postgres

Consider the following table in Postgres 9.4: CREATE TABLE t ( a1 bigserial, a2 bigint NOT NULL, a3 bigint NOT NULL, a4 integer, a5 timestamp with time zone NOT NULL, a6 timestamp with ...
0 votes
2 answers
136 views

Considerable size difference between mysql versions

We usually employ different MySQL versions for different scenarios. And I realized that similar databases, whith the same amount of tables and data, just because of different mysql versions differs in ...
2 votes
1 answer
922 views

Calculating total "on row" bytes for each row ... the easy way

We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like ...
3 votes
1 answer
19k views

Find actual data size from a query?

Is it possible to find the size of data that will be returned by a particular query? For example, I can use the following to find the number of rows per date: select mydatecolumn, count(*) from ...
6 votes
4 answers
18k views

In Postgres, set log_rotation_size but my logs are still not rotating

I’m using Postgres 9.5 on Ubuntu 14.04. I wanted to rotate my logs whenever they reach a certain size. So I edited /etc/postgresql/9.5/main/postgresql.conf and set this log_rotation_size = 50MB ...
2 votes
2 answers
5k views

how to reduce the size of the datafiles of a oracle DB?

we use a oracle db (11g release2) and the undo tablespase exists of two datafiles and requires 48 GB. But we just need this size for the boot and the import. Currently we just use 10 % from the ...
1 vote
1 answer
87 views

How to know exactly when my Database / Log files grew?

Is there a way to see when (history / log records) of .mdf / .ldf files grew? Ultimately, is it possible to setup alerting and know immediately when my database / log files grow?
1 vote
1 answer
5k views

Reasonable values for thread_cache_size in relation with Connections [closed]

I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients ...
2 votes
2 answers
2k views

MySQL: optimize column size

I need a tool/command which will check a table then say something like that: columnA use tinyint instead of int columnB use enum instead of varchar I've seen something like this while ago so I do ...
-1 votes
2 answers
2k views

How can I estimate the size of a new table?

We have tables with a lot of data (50M new rows each month, just in one table). Now I want to know what would be the impact of adding a new column to those tables, or creating a new table. Something ...