Tag Info

Hot answers tagged

12

Ideally, you would create the database, load a bit of sample data, measure the size, and extrapolate. That is, by far, the more accurate method of estimating the size of a database in 5 years. If you do want to compute the database size, you would generally start by figuring out how many rows will fit in a single block. For simplicity, we'll assume that ...


10

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.) The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...


8

Just use a different setting for the block size: --with-blocksize=BLOCKSIZE The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 32 (kilobytes). Using 32 kilobytes, your table has a maximum size of 128TB.


8

First, are you using "database" in the Oracle sense of the term? Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term? If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP ...


6

Your rows fit in page, even with 100 spaces in Comments. Updating the Comments to ltrim(rtrim)) will make absolutely no difference in space used, since the rows will be left in place in their respective pages (ie. absolutely no page gets freed, you just get more space available on each page). To reclaim the lost space you should rebuild the table: ALTER ...


6

That is not a large table, SQL-Server should be able to handle that easily. You may have performance issues relating to using GUIDs. Your PK is clustered and is a GUID, this means it is continually moving rows around on insert to keep the clustered index correct. If you are not using newsequentialid for the default value of your GUIDs, then you have a ...


6

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.


5

Please run this query: SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A; This will give you a ballpark figure. The column index_length is ...


5

The table MY_TBL contains large binary data in a BLOB column. The documentation of the REORG command says that DB2 avoids reorganizing such objects because it is time consuming and does not improve clustering. However, DB2 can be forced to reorganize LOB data if the LONGLOBDATA option is specified. The unused space can be reused by DB2, so inserting new data ...


5

This is what you're looking for, if I've read your question correctly: SQL> select segment_type, sum(bytes)/1024/1024 as size_in_mb, sum(blocks) as size_in_blocks 2 from dba_extents 3 group by segment_type; SEGMENT_TYPE SIZE_IN_MB SIZE_IN_BLOCKS ------------------ ---------- -------------- LOBINDEX 43.4375 5560 INDEX ...


5

There are several ways to get the size of a database, each suited to a slightly different use case. It's important to note that Vertica uses raw and compressed data in different ways, and that you should be conscious of which size you require. For example, licensing is based on the raw data size. Raw Size The raw size is useful for capacity planning or ...


4

For point 1), you need to read the Storage Page Layout chapter of the documentation and in particular consider the HeapTupleHeaderData Layout table for the metadata at the row level. The 4-bytes per-row OID is obsolete for user tables. PostgreSQL no longer have them by default since 8.1. This is now controlled by the default_with_oids config parameter or ...


4

The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database. But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for ...


4

Frank's answer is entirely correct, but there's more to it. Don't do this. Partition your table instead. PostgreSQL's table partitioning isn't wonderful, but it's going to be better than a 32TB+ table.


4

There is such a formula. You can find it in an appendix of the Oracle 7 manual. There is also a note on Oracle Metalink that covers the same topic: Extent and Block Space Calculation and Usage in Oracle Databases (ID 10640.1).


4

The query below will show you an overview of the schemas, tablespaces and objects in your database and their respective sizes. - Change the group by to decrease the level of detail (you might want to see if the size is being caused by tables, indexes or other objects first) - Add a where clause to only list the schema/tablespace/object_type you want to ...


4

If your problem is file fragmentation then no, there isn't. In Postgres each table gets it's own file, or set of files if it uses TOAST, in the file system. This differs from, say, Oracle (or apparently MS-SQL) where you create pre-sized tablespace files to drop your tables into-- although even there you could have file system fragmentation issues if the ...


4

SQL Server will create a new column, copy the data over, and drop the old column. The table will increase in space by some factor larger than just the size of the new or old column and/or the average or max length of the data. The reason is that temporarily the copy of the data for all rows on a page can't possibly fit on the page, so many new pages will ...


4

I don't need my transaction log. Yes you do. can i delete my log to increase space? No you can't. In this case, is there an "easy way" to copy the data from the old tables into new tables? Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your ...


4

Yes, there is, though you got to use RESTORE command anyway. Instead of RESTORE DATABASE, use RESTORE FILELISTONLY to get a detailed view of files in the backup. The Size column tells the file size in bytes. RESTORE FILELISTONLY FROM DISK = N'v:\MyBackup.bak'


3

If you want to query in the INFORMATION_SCHEMA database I have the following: Total Storage By Database in MB SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "DataSize", CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize", ...


3

This should get you the size of the data and the indexes of your databases (only looks at MyISAM or InnoDB): SELECT table_schema 'database', concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size FROM information_schema.TABLES WHERE ENGINE=('MyISAM' || 'InnoDB' ) GROUP BY table_schema ORDER BY size ASC; Note that this is ...


3

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis. 1 Backup all of the databases on your postgresql server ...


3

Because the resulting combined table would have unused rows for records from each respective table, the likelihood is that you would not gain space, but lose it. More importantly than that, I would highly discourage making an architecture decision of that nature for reasons of disk-space. Disks are cheap--a well designed and functional application is not. ...


3

Does table have any variable-width columns? If the answer is yes, you are probably missed toast tables (pg_toast_xxx). You need to find their names (not sure how to do it, need to google it) and calculate their size as well. To reduce space (for example after deleting bunch of rows) execlute VACUUM FULL and reindex table after it (to prevent index ...


3

Steps for shrinking the log are going to be Backup transaction log through either SSMS or T-SQL and then perform a shrink commands for SSMS are under the tasks if you right click the database name BACKUP LOG <Databasename> TO DISK N'<path\database_log.ldf'; GO DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS You will ...


3

Here is answer for my own question. Run the below query to get information about log files. select log_reuse_wait_desc from sys.databases where name = 'DBName' It will give output if there is any. I got following output. og_reuse_wait_desc REPLICATION There was some replication related objects remaining the database even after removing the ...


3

It is certainly possible to delete data from a random access file. Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data. At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among ...


2

I was waiting for more experienced people to answer, but one thing I will point out, since you're using innodb engine. You'll want sizeable amount of RAM to increase the innodb_buffer_pool_size so that it can hold your dataspace, if possible. So if your database takes up 6GB, it's feasible to set the buffer_pool_size to catch it in RAM (assuming you can ...


2

The export does not contain the indexes. It only contains the definition of the indexes but not the data that is stored in there. Additionally the size of SQL export will also not reflect the size of your data (just think of the SQL keywords that are needed for the SQL export). When you move data from one representation to another (MySQL "on-disk" format ...



Only top voted, non community-wiki answers of a minimum length are eligible