Hot answers tagged

19

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

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.


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


12

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


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

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


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


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


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


8

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


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

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

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


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

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

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


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

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

Looks like you are already aware of the risks associated with shrinking so I wont go over those again. As a once off due to a bug or some form of large growth that will only happen once then doing a one time shrink is fine. Do the shrink, rebuild / reorg the indexes and that should be it. Is your database in SIMPLE or FULL recovery? If Full make sure you ...


5

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


5

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


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

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

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

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.


5

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


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

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



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