Hot answers tagged

18

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 ...


13

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.


13

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 ...


12

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 ...


11

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.


10

You have to check the SERVERPROPERTY ('Edition') AS ServerEdition to find if your installed instance is an Express edition or standard or enterprise. You can use below T-SQL : SELECT SERVERPROPERTY ('MachineName') AS PhysicalMachineName, SERVERPROPERTY ('ServerName') AS SQLServerName, SERVERPROPERTY ('Edition') AS ServerEdition, ...


9

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 ...


8

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 ...


8

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.


8

I have some queries you can run against the INFORMATION_SCHEMA Run this to get the Total MySQL Data and Index Usage By Storage Engine SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( ...


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 ...


7

That is not a large table, SQL-Server should be able to handle that easily assuming you are running on server-grade hardware. If your hardware is severely underpowered then nothing we tell you can solve the problem entirely. You may have performance issues relating to using GUIDs. Your PK is clustered and is a GUID, this means it is continually moving rows ...


7

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 ...


7

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'


6

Here is the answer to my own question. Run the below query to get information about the log files. select log_reuse_wait_desc from sys.databases where name = 'DBName' It will give output if there relevant rows. I got the following output: log_reuse_wait_desc ------------------- REPLICATION There were some replication-related objects remaining in the ...


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

The "Database Size" on the properties page is showing the size of the data file. This will be the size of your data, with some empty space for new data. You can configure the rate at which this empty space is allocated in the database properties. It says space available 0.03Mb because you've shrunk the data file down - it will grow by itself to allow for ...


5

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 ...


5

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 ...


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

If you put everything in one table, you will have a bigger, redundant table. If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.


4

Junction tables are a very standard practice in relational database design. It's covered in database 101. If you have a many-to-many relationship between two entities, the standard way to represent them is with three tables. Two of the tables are entity tables, with a primary key. A junction table lies between them (logically) and contains two foreign ...


4

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeeeeeeettty sure though it will work on 9.0 though. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of exclusive locks. But only on the table you are vacuuming. So all that will happen is that client queries ...


4

Yes, the data is up-to-date. But don't read too much into the numbers. InnoDB, especially, preallocates space for future rows. So, it is quite possible to look at the exact size of a table, insert dozens of rows (maybe even thousands), then look at the size again -- and see exactly the same Data_length and Index_length. When you first create an InnoDB ...


4

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect. You can add a new datafile to 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

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

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

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

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 ...



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