101

Q2: way to measure page size PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.) This is going to show that different methods to measure the "size of a row" lead to ...


38

Here is the query Management Studio uses to populate those numbers: SELECT (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize], SUM(a.total_pages) AS [SpaceUsed], (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in (1, 3)) AS [LogSize] FROM sys.partitions p join ...


35

An approximation of the size of a row, including the TOAST'ed contents, is easy to get by querying the length of the TEXT representation of the entire row: SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value; This is a close approximation to the number of bytes that will be retrieved client-side when executing: SELECT * FROM ...


30

This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange. To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP ...


29

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old ...


23

Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries (you would need to change the math for TBs): -- Individual File Sizes and space available for current database -- (Query 36) (File Sizes and Space) SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total ...


22

Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data. The following is an adaptation of the code I ...


21

Although you did not state it, I assume from your references to documents that you have followed that you have done a VACUUM FULL on the database and/or affected tables. You also didn't specify what postgresql version you are using - I will assume it is > 9.0 (VACUUM FULL behaved differently before this). VACUUM FULL will rewrite affected tables into new ...


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


20

You can cycle the error log by calling sp_cycle_errorlog and then that will close the current error log and cycle the log extensions. Basically, it'll create a new error log file that SQL Server will be hitting. Then the archived error log(s) can be treated accordingly (delete/move with caution). This will not technically "truncate" the log, it'll just ...


19

You can extend disks while the database is online, but I will recommend you plan for a brief 15 - 30 minute outage during which you can take the services offline. I've extended disks hundreds of times while databases were online and running without issues. However one time, and only one time at this point in my career, the operation caused corruption in ...


16

Disk Sectors A disk has a fixed sector size, normally 512 bytes or 4096 bytes on some modern disks; these disks will also have a mode where they emulate 512 byte sectors. The disk will have tracks with varying numbers of sectors; tracks closer to the outside of the disk have more sectors as they have more room for a given bit density. This allows more ...


16

Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a ...


15

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following: Example: To truncate an ...


14

There are a few things that could be happening. In general, I doubt that length is the proximal problem. I suspect instead you have a length-related problem. You say the text fields can get up to a few k. A row cannot go over 8k in main storage, and it is likely that your larger text fields have been TOASTed, or moved out of main storage into an extended ...


14

From the column documentation of sys.database_files: size: Current size of the file, in 8-KB pages. So a more meaningful query might be something like this: SELECT df.name AS LogicalFileName, df.physical_name AS PhysicalPath, CONVERT(int, (((df.size * 8192) / 1024.0) / 1024.0)) AS SizeInMB FROM sys.database_files df; (You can expand on ...


14

I will be deleting approx 400 million records from this table. Hopefully, you are doing it in chunks - to avoid bloating transaction log. notice that i am not see any drop in my hard drive space You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the OS. ...


13

A reorganise-and-shrink is never recommended really. If you can take the apps the database is serving offline, you can speed up the process and reduce index fragmentation by removing all indexes and primary/foreign key constraints before the shrink (this will mean there is less data to be moved around as only the data pages will be shuffled not the now non-...


13

It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST. USE tempdb; DROP TABLE IF EXISTS dbo.RobertBurns; CREATE TABLE dbo.RobertBurns ( Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Scotch VARCHAR(50), HaggisAddress VARBINARY(MAX) ); DECLARE @...


13

No, but it may make your transaction log smaller - so your SQL Server will use less space. To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery ...


12

pg_default and pg_global locations are "hardcoded". pg_default lives in: select setting||'/base' from pg_settings where name='data_directory'; and pg_global lives in: select setting||'/global' from pg_settings where name='data_directory'; src/backend/commands/tablespace.c says so: * There are two tablespaces created at initdb time: pg_global (for ...


12

First things first: How much data is there in the table? Number of rows and size of the table? Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more ...


11

After discussions in the comments on the original question, it appears in this case the lost space is caused by the choice of clustered key, which has led to massive fragmentation. Always worth checking the state of fragmentation via sys.dm_db_index_physical_stats in these situations. Edit: Following update in comments The average page density (prior to ...


11

Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL. NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly effects performance. Effective storage requirement depends on whether a null bitmap for each row already exists and has room for 3 more bits. Tables with up to 8 columns can ...


11

Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file. ;WITH f AS ( SELECT name, size = size/128.0 ...


10

If you are going to continue logging data in this database, the last thing on earth you want to do is shrink the database file (and then perform index maintenance that will require it to grow again). Never mind that these shrink and grow operations will interfere with performance, and that the end result is not going to leave you much better off than when ...


10

If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, ...


10

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes. Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file ...


10

When you restore a database you have all the information on it packed, with no empty space between rows (or in indices), unless some specific settings are in place (basically: FILLFACTOR for tables and FILLFACTOR for indices). On the other hand, when your database has been in use for some time, and you've had your share of inserts, updates and deletes, free ...


10

First, I would confirm what the server admin is seeing, by running some free-space diagnostics on your SQL Server: /* Get SQL Server Drive Usage Stats */ IF OBJECT_ID('master.sys.dm_os_volume_stats') IS NOT NULL SELECT vs.volume_mount_point AS Drive, vs.file_system_type AS [Type] ,vs.logical_volume_name AS LogicalName ,MAX(CAST( 1.0*vs....


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