19
votes
Accepted
How to go about extending volumes on SQL Server boxes?
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 ...
- 9,487
14
votes
Accepted
Disk usage of integer column vs boolean column in Postgres
You can find out the storage size with
SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');
typname | typlen
---------+--------
bool | 1
int4 | 4
(2 rows)
...
- 56.4k
13
votes
Accepted
Best way to shrink a DB after nulling data from varbinary(max)?
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....
13
votes
Accepted
Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?
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'...
- 43.2k
13
votes
Accepted
Size Requirement of SQL Database
This is an open-ended interview question that doesn't have a precise answer. The interviewer might expect you to ask additional questions for clarity.
Actual space requirements will depend on what is ...
- 28.5k
12
votes
Setting location of temp files for Postgresql backend (9.3.10)
There is a big problem with a_horse_with_no_name's solution. Not all instances of PostgreSQL even have a $PGDATA/base/pgsql_tmp because that entire path is actually created as needed in those ...
- 121
12
votes
Size Requirement of SQL Database
These kind of questions are stupid interview questions in my opinion, and definitely need more context. Perhaps they just want to see your thinking and what follow up questions you'd ask for clarity.
...
- 39.5k
11
votes
Accepted
what is the best practice for tempdb autogrowth?
For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect.
Let's use some simple numbers. Say you have a 100 GB drive that you've ...
- 182k
11
votes
Accepted
SQL Server / SSMS taking up lots of disk space
You might want to use WinDirStat or TreeSize Free to see which files are actually taking up space. It may not even be what you think.
SQL Server doesn't take backups on its own, so it's unlikely those ...
10
votes
Accepted
Database size reduced after backup on PostgreSQL 8.3 and restore in PostgreSQL 9.4
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 ...
- 13.6k
10
votes
How to free up disk space to the OS, ideally without using SHRINKFILE?
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') ...
- 1,554
10
votes
Accepted
Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?
If you need rounded values, storing them rounded is going to be faster and less error-prone than dynamically rounding each time you access it.
And having less precision means more ties so more ...
- 41.3k
9
votes
Accepted
Why does dropping decimal columns not free up any space?
For a rowstore table you should not expect a drop column operation to free up any space. Data is stored on pages in row format. I'm oversimplifying a bit, but for most rowstore tables you'll end up ...
- 32.6k
9
votes
Accepted
If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed?
>> NOTE FROM O.P.: While I have accepted this answer, please be aware that it is not conclusive since extensive testing has not been done. It is possible that certain types of updates, or series ...
- 530
9
votes
Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?
Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your ...
- 182k
8
votes
In Postgres, set log_rotation_size but my logs are still not rotating
In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included.
From https://www.debian.org/doc/debian-policy/ch-...
- 32.3k
8
votes
Measure the size of a PostgreSQL table row
If an average of the current rows size is wanted, you could use pg_column_size :
SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;
Using it per column :
SELECT SUM(pg_column_size(...
- 181
8
votes
Accepted
Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?
There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available, unused, VLF entries.
If the log runs out ...
- 70.5k
8
votes
Accepted
Does UPDATE write a new row version for unchanged, TOASTed values?
That's the one major shortcut an UPDATE takes in Postgres' MVCC model (as compared to DELETE + INSERT): Fields that are stored out-of-line (TOASTed) and not changed in the UPDATE are kept as is. ...
- 182k
7
votes
Accepted
Is it wise to drop fixed-length columns to free up space?
Dropping them will reclaim space if you rebuild indexes or run DBCC CLEANTABLE
New rows will not have these columns
Fixed length columns always have space reserved whether NULL or not (note: variable ...
- 70.1k
7
votes
Accepted
On Amazon RDS, what consumes storage space beyond the database files?
AWS support came back with a comprehensive answer. In this case, it was transaction log backups, being written to the SQL disk before offloading to S3. There are a few more things which can consume ...
- 5,957
7
votes
Why would I not run "index reorganization" regularly?
Reorganizing an Index should be done when you have elevated amounts of white space within your index (i.e. avg_page_space_used_in_percent column in the sys.dm_db_index_physical_stats DMV). Many ...
- 9,487
7
votes
Restore DB Backup with limited amount of disk space
SQL Server need to create the database with the size it had when the backup was produced. No way around that.
You can possibly have the backup file on a different machine and restore using an UNC ...
- 17.9k
6
votes
Accepted
Using pg_prewarm to load X number of latest rows into cache
Addressing Question #3
the live_row_size I get from above query is not the same
as the result mentioned in this answer (by Erwin). Am I doing
something wrong?
You have:
SELECT pg_size_pretty(...
- 182k
6
votes
What happens when a file size limit is reached?
No, the two are unrelated.
TempDB is used for explicit temp tables, but also for sorting and database snapshots and lots of other internal things. Was there some other very large transaction going ...
- 10k
6
votes
Accepted
Sum of table sizes don't match with mdf size
There are "internal" tables, queues, etc that don't show up in certain reports / queries as they are system objects. If you run the query below, which counts the number of actual pages defined in the ...
- 69.5k
6
votes
Accepted
1000 MB vs. 1024 MB
Powers of 10 vs Powers of 2
The computer industry has for decades made a mess of these sizes, sometimes meaning base 10 and sometimes meaning base 2. This is done inconsistently between contexts and ...
- 11k
6
votes
Accepted
How does SQL Server reuse reserved pages after deleting a table
Quite simply a DROP or TRUNCATE statement marks any allocated pages that were associated with the table (regardless if it was a heap or a clustered table) as no longer being allocated and the space is ...
- 9,487
Only top scored, non community-wiki answers of a minimum length are eligible