Skip to main content

Questions tagged [size]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...
Joe's user avatar
  • 1,645
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 ...
Mate Michelisz's user avatar
18 votes
2 answers
24k 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 ...
Alexander Tobias Bockstaller's user avatar
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 ...
Erwin Brandstetter's user avatar
12 votes
2 answers
2k views

Find uncompressed size of all tables in a database

In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'...
Tom V's user avatar
  • 15.7k
10 votes
6 answers
32k views

Estimate projected database growth

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 ...
Diego Flores's user avatar
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 ...
arthur's user avatar
  • 888
10 votes
1 answer
5k views

Estimating impact of indexes SQLite database size

I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to ...
G__'s user avatar
  • 203
8 votes
0 answers
150 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently? [duplicate]

This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing. If stored, I think it is well ...
Léo Léopold Hertz 준영's user avatar
8 votes
2 answers
3k views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
raacer's user avatar
  • 213
7 votes
2 answers
32k views

Choosing the right storage block size for sql server

There are many articles on what storage blocks size should be used for sql server e.g. Disk Partition Alignment Best Practices for SQL Server. The right blocks size should improve the performance of a ...
r0tt's user avatar
  • 1,028
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 ...
Dave's user avatar
  • 713
6 votes
2 answers
2k views

SQL Server Table Size Discrepancy

Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table. If I run the ...
Mitch Wheat's user avatar
  • 1,375
5 votes
3 answers
1k views

Recalculate row size in SQL Server 2008?

It is possible to recalculate row size in SQL Server 2008? Take this example: declare @counter int declare @statement nvarchar(max) set @counter=0 drop table kua2 create table kua2(id int) while @...
Martin Cifko Štefček's user avatar
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 '['+...
theindianvenom's user avatar
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 ...
Adamantium's user avatar
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 ...
juanman80's user avatar
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('...
Adam Matan's user avatar
  • 11.7k
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' ...
jack's user avatar
  • 389
4 votes
2 answers
2k views

Heap size too large for data present

A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over ...
SQLFox's user avatar
  • 1,564
4 votes
3 answers
4k views

SQL Database size doesn't match with the total table sizes in the database

I have a database that is size about 104GB. When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces. Why the DB size is too large?
pavelcc's user avatar
  • 41
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 ...
igal's user avatar
  • 345
4 votes
1 answer
1k views

Table Data Space Used Confusion

I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (...
Brad's user avatar
  • 943
4 votes
2 answers
365 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
Rahul J's user avatar
  • 41
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 ...
Sakshi Sharma's user avatar
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 ...
Francesco Mantovani's user avatar
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(),...
arthur's user avatar
  • 888
3 votes
2 answers
1k views

Access 2000 database rapid growth

We have an Access 2000 format database running in Office Access 2003. We have a problem with the size when I merge the database; the size is about 250 MB. Two days later, the size has ballooned to ...
Joël Craenhals's user avatar
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 ...
BlueChippy's user avatar
3 votes
1 answer
5k views

How to reduce size of PostgreSQL table after dropping columns?

I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the ...
Jon S.'s user avatar
  • 31
2 votes
1 answer
11k views

What's the difference between Object.bsonsize and avgObjSize returned by db.collection.stats?

In my collection, my documents seem to be relatively similar. I need to know how much storage I'll need in my production server to store my documents, that's why I queried : db.collectionName.stats() ...
KirbyGirl's user avatar
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)...
gotqn's user avatar
  • 4,026
2 votes
2 answers
6k views

Print database_size from sp_spaceused

Using SQL Server 2008, I have found that the database size can be obtained using exec sp_spaceused This procedure returns two tables: database_name | database_size | unallocated space reserved | ...
user avatar
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 ...
UnLogicGuys's user avatar
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 ...
zam6ak's user avatar
  • 471
2 votes
2 answers
3k views

Microsoft SQL database table size

I have come across a sizing issue with my database tables becoming too large. Some digging has shown that it is how MS SQL stores the information. I created two test tables and filled them with data: ...
Darren's user avatar
  • 23
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 ...
Putnik's user avatar
  • 285
2 votes
1 answer
9k views

How do I limit the size of my Postgres log?

I’m using Postgres 9.5 on Ubuntu 14.04. I notice I’m generating a lot of Postgres logs in /var/log/postgresql/postgresql-9.5-main.log . I would like to limit the size of this file, and doing some ...
Dave's user avatar
  • 713
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 ...
user avatar
2 votes
2 answers
284 views

SQL Server Database size unchanged after dropping File column

I have a database with a table that has about 10 000 records. One of the columns in this table is a VARBINERY(MAX) that is used to store images. A backup of this database is about 1.2GB. It's size as ...
Carel's user avatar
  • 125
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 ...
jian's user avatar
  • 447
2 votes
1 answer
735 views

'packet too large' error with packet smaller than max_allowed_packet

Some time ago we started storing serialized data in our MySQL database and immediately ran into the well known 'packet too large' error. We increased max_allowed_packet to 64M and all was well. This ...
Confusion's user avatar
  • 141
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 ...
user avatar
2 votes
1 answer
4k views

Need help in increasing maximum table size

Two of the tables vb_post, vb_profilevisitor getting corrupted again and again. The hosting company suggested the following: Looks like the problem is that the .MYD table was converted to a .TMD ...
coolbreeze86's user avatar
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 ...
Rj_N's user avatar
  • 418
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 ...
Aleksey Vitsko's user avatar
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 ...
dubloons's user avatar
  • 121
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 \...
dquake's user avatar
  • 11
1 vote
3 answers
798 views

Big size of database logs SQL Server 2008

I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB ...
BennoDual's user avatar
  • 153
1 vote
2 answers
785 views

Sizing MySQL Database Servers for Drupal

What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc? We're going to go with either the community edition or the enterprise edition, and ...
KM.'s user avatar
  • 1,357