17
votes
Accepted
SQL Server table with 4,000,000 rows is 40GB
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 ...
- 182k
13
votes
Table has 14 GB in unused space - How to shrink table size
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 ...
- 182k
13
votes
Accepted
How to free the unused space for a table
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
...
- 90.2k
12
votes
Accepted
Why the sudden increase of my database .mdf file size?
Messages will be retained in Service Broker queues until they are consumed with RECEIVE or the associated conversations ended with END CONVERSATION. Messages will also be retained in the queue after ...
- 28.5k
12
votes
Accepted
SQL Server :: How many 8K pages are taken by this record?
I think you have something backwards here. A page is 8 KB (8,192 bytes). The rows in that table have a maximum size of the sum of the data type sizes of all of the columns:
I'm not actually going to ...
- 29.8k
11
votes
Accepted
SQL Server DB table data size query result not increasing if data count increasing
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 ...
- 182k
9
votes
Accepted
Pages to MB, multiple and divide or just divide?
It's simple math. Those common equations are the same (not seem to give the same answer).
Simpler notation is the only benefit of the second one.
The first one just exlpains from where is the number ...
- 1,363
9
votes
I need to run VACUUM FULL with no available disk space
Quick and dirty:
Stop Postgres
Move the main database directory to another disk where there's enough room for vacuuming
In the original location of main, add a symlink to the new location
Start ...
- 191
9
votes
DB backups have suddenly gotten smaller
The free space on the current database was around 19 GB, while on the
restored database it was around 11 GB. A difference of 8 GB, like the
backups. Our backups are not compressed.
You might find the ...
- 5,926
8
votes
DB backups have suddenly gotten smaller
Some clues that may change things:
Dropped some non-clustered indexes (NCIs).
Added clustered columnstore indexes on some tables.
A drop in row numbers reduces the size of indexes, especially if you ...
- 4,685
6
votes
Accepted
Why is my database 12 times bigger than inserted data?
text is the best type for plain text. Text values alone occupy about the same internally (depends on encoding details, too of course: UTF-8?). A small overhead of 1 byte per field in your case. See:
...
- 182k
6
votes
Releasing free space in mdf file to OS
What exactly you are trying to achieve by shrinking the database?
You should plan for the database growth and have reserved space for this growth.
So why don't you leave it as it is to have ...
- 2,306
5
votes
Reduce size of SQL Server database
You have three options, none of which will involve me telling you to shrink your database:
Archive old data by deleting it
Archive old data by moving it to another database and use views to allow ...
5
votes
Show data and disk use breakdown by table
Just for fun, here's a query that will generate the same data as the report in nateirvin's answer
create table #disk_usage
(
name varchar(128)
,rows varchar(20)
,reserved varchar(20)
,...
- 223
5
votes
Accepted
what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server
-- casting using DECIMAL(38,2) throws exception, Size needs to be casted as bigint
select [SizeInMB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files
-- This fix is ...
- 207
5
votes
Releasing free space in mdf file to OS
Please read my post on how to shrink a database, which I'll summarize here:
Look at your data file size
Look at the size of all files in your filegroup. You want all files within the group to be ...
- 16.3k
5
votes
Finding integer database columns that may have their data type changed to reduce size
The following stored procedure will help identify those columns. It starts by creating a temporary table that stores the minimum and maximum values that each integer type can hold and then runs a ...
- 193
5
votes
Accepted
Does rebuilding a clustered index offline require extra space for each non-clustered index?
nope
Rebuilding the clustered index will not rebuild nonclustered indexes unless you're changing the clustered index key columns.
You would also need to rebuild nonclustered indexes if you were adding ...
4
votes
Table has 14 GB in unused space - How to shrink table size
You should check the fill factor on your tables and indexes, to make sure that you aren't explicitly reserving a lot of space on your pages. Run the following command:
SELECT t.name as TableName
...
- 11.7k
4
votes
Accepted
Table and Primary Key sizes in Oracle
But why do Oracle stores this so inefficiently? (a separated structure
for the primary key)
It is inefficient for this use case.
Do Oracle has something similar to MS SQL's clustered index, that
...
- 41.1k
4
votes
Accepted
Can I Compress a huge table on SQL?
Since you're using SQL Server 2012 Standard, you're not able to take advantage of DATA COMPRESSION. It's an Enterprise feature up until SQL Server 2016 SP1. When you're able to upgrade, you should ...
4
votes
Accepted
Script to monitor database growth on daily basis
There is a stored procedure called DatabaseSpaceUsage:
https://github.com/aleksey-vitsko/Database-Administrator-Tools
When you execute it exec DatabaseSpaceUsage without any parameters, it will show ...
- 5,964
4
votes
Accepted
Free some space on a full Express Edition instance
Your current process shouldn't really change application performance (especially with such tiny amount of data) but perhaps your database gets severe index fragmentation and creating the new database ...
- 39.5k
4
votes
Why is my composite index on 3 int columns smaller than the PK index on 1 int column?
25 MB for the index "data_table_pkey" btree (id) is its minimum possible size. 1184330 rows, 8+8+4 = 20 bytes per index tuple, default fillfactor for B-tree index = 0.9 (plus negligible ...
- 182k
3
votes
Accepted
SQL Server Database size unchanged after dropping File column
When you drop a table, it will free the space in the database, but not back to the O.S. because your database has this space allocated. You need to shrink your database. but it's not a good Idea, ...
- 7,484
3
votes
Database Space Used Decreased after Index Rebuild (Ola Script)
This is quite common behavior. You are reclaiming unused space by reordering pages in the leaf level.
Just don't shrink your datafile unless it's critical - you'll probably need that space in the ...
- 51
3
votes
Accepted
Database Space Used Decreased after Index Rebuild (Ola Script)
You would not lose data with index maintenance / rebuilds. As your data is fragmented, the data would be spread over multiple leaf pages in the clustered / nonclustered indexes as data is deleted / ...
- 156
3
votes
Why is my SQL Server database file so large?
Simple recovery doesn't make it easier to shrink a log file. It will just empty the log for you (when checkpoint occurs), same thing as you do when you perform a log backup. The thing with a log file, ...
- 17.9k
3
votes
Accepted
Finding integer database columns that may have their data type changed to reduce size
You might be able to save yourself a lot of work if you can upgrade to SQL Server 2016 SP1 or later. Data compression is available even in Express edition starting with that service pack. You could ...
- 32.6k
Only top scored, non community-wiki answers of a minimum length are eligible