28

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


22

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


21

This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case. Try instead: SELECT relname AS objectname , relkind AS objecttype , reltuples AS ...


19

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


17

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though. CAUTION (As noted in the comments by @erny): Note that high CPU load due to I/O operations may be expected. You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of ...


17

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume). SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024; ------ 61,035 If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.) Other factors ...


15

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


15

I am struggling to find the reason of the arithmetic overflow. why is it happening? Most likely the metadata is returning some unexpected values that your code cannot handle. For example: -- Example values returned from sysfiles and FILEPROPERTY DECLARE @size integer = 1, @spaceused integer = 10000; -- The essence of the code in the question ...


14

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


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

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


12

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( FORMAT(B.ISize/POWER(1024,pw),3),',','...


12

Here is the answer to my own question. Run the below query to get information about the log file's reuse wait: SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'DBName' I got the following output: log_reuse_wait_desc ------------------- REPLICATION There were some replication-related objects remaining in the database, even after removing the ...


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.


11

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, CAST(SUBSTRING(@...


10

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


10

SQL Server stores data on 8kb pages. You can't have a table that's smaller than than two pages. So when you add one row to the table, you have a minimum space consumption of 2 * 8kb = 16kb. But those pages aren't full, which means adding 15 more rows doesn't increase table size by (15 * initial size), it just means that the data page holding the initial row ...


9

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.


9

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


9

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'


9

Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6gb Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully. There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, "yes I have ...


9

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so: ALTER INDEX ALL ON dbo.Articles REORGANIZE WITH (LOB_COMPACTION = ON); See ALTER INDEX (Transact-SQL) If you have the clustered index name (not just the clustered column(s)), replace the ALL ...


8

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", CONCAT(LPAD(FORMAT(STSize/POWER(1024,...


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


8

My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed : Object.bsonsize(db.collectionName.find({"_id" : ObjectId("5508497c51a990da07b07106")})) And I obtained 810 bytes. Why do I have this huge difference ? The db.collection.find() method returns a cursor, so what you have calculated is the ...


8

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.


8

It's been answered on Stack Overflow: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i....


8

Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding: ALTER INDEX ALL ON dbo.myTableName REBUILD; Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up ...


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

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


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