31
votes
I need to run VACUUM FULL with no available disk space
Method with little to no downtime (minimal user-impact)
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 ...
- 731
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 ...
- 180k
15
votes
Accepted
what is causing arithmetic overflow in the query below?
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 ...
- 83k
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 ...
- 180k
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
...
- 83k
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 ...
- 27.8k
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 ...
- 29k
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 ...
- 180k
11
votes
Accepted
How can I verify that my SQL Server edition is Express?
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
...
- 62.1k
10
votes
Accepted
Querying True Physical Database File Sizes
Here you go. You can use this to get file sizes by file and filegroup. It includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by ...
- 737
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,353
9
votes
Shrinking sql databases - bad idea?
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 ...
- 18.9k
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,069
8
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
Vacuum
...
- 181
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,260
7
votes
Accepted
Shrinking sql databases - bad idea?
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 ...
- 446
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,319
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:
...
- 172k
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
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 ...
- 38.9k
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.1k
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
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
4
votes
Accepted
Does INFORMATION_SCHEMA always contain up-to-date metadata?
Yes, the data is up-to-date. But don't read too much into the numbers.
InnoDB, especially, preallocates space for future rows. So, it is quite possible to look at the exact size of a table, insert ...
- 77k
4
votes
Accepted
mongodb compact does nothing - no entry in mongodb.log and nothing in db.currentOp()
Since you are using the MMAP storage engine there are several factors to be aware of:
The compact command only defragments data files & indexes in MMAP; it does not release unused space to the ...
- 10.2k
4
votes
what is causing arithmetic overflow in the query below?
Maybe you have forgotten that a file can have 100% free space?
In that case you need DECIMAL(5,2), not DECIMAL(4,2).
- 2,361
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.6k
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 ...
- 23.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
...
- 40.4k
Only top scored, non community-wiki answers of a minimum length are eligible