Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
226
questions
0
votes
1
answer
887
views
Index Maintenance - Reorganize After BIG SQL Server Shrink
I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@...
CommunityBot
- 1
-1
votes
1
answer
62
views
Alternatives to reclaim free space after Shrink activity
For couple of our SQL server databases on version SQL2017 APP team did purge to change retention on these partitioned tables which has released quite good amount of space.
Some background-
Before ...
- 45.9k
0
votes
1
answer
514
views
How to reduce big size database files (Data File ) SQL Server without SHRINK?
I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question
I have a big database with almost 8TB of Data File Size ...
CommunityBot
- 1
0
votes
3
answers
5k
views
What is Fastest way to shrink a datafile?
I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the ...
CommunityBot
- 1
0
votes
0
answers
63
views
SQLServer Physical Log shrinking automatically with AutoShrink off
Running in SQLServer 2016 I have a weird situation where the Physical log is shrinking without any interaction and with AutoShrink=false set.
Also, on occasion the size is shrinking below the initial ...
- 83k
2
votes
1
answer
1k
views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Oracle 11g - Decreasing Big Table Size
I want to reduce the size of a table.
I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using ...
CommunityBot
- 1
0
votes
1
answer
29
views
What is the correct value (GB) to shrink a DB?
My database has an MDF file of 56 GB and an LDF file of 263 GB, recovery model is setting to Full.
So, I would like to shrink the LDF file because we are out of space. What specific size should I ...
- 35.2k
53
votes
8
answers
155k
views
How do I shrink all files quickly for all databases?
In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, ...
-1
votes
1
answer
41
views
Azure SQL Database :: SHRINKDATABASE increase the unallocated space
I'm trying to squeeze as much Azure SQL Databases as possible in order to make them fir inside a SQL Elastic Pool.
I'm using this query that allows me to compare the unallocated space before and after ...
- 1,504
-1
votes
1
answer
226
views
SQL Elastic Pools: "The semaphore timeout period has expired" while REBUILD index
I'm shrinking databases that are already on a SQL Elastic Pool.
The goal is to fit as much as possible databases in a unique Elastic Pool.
In order to do so I've stubble upon a blog that suggest to ...
- 1,504
0
votes
1
answer
169
views
DBCC Srhinkfile is stuck running for 2+ days
Forgive me, but I am very new to being a DBA as I ended up in this role out of necessity :)
I am currently working on reducing the size of several databases. I previously asked a question here about ...
- 35.2k
0
votes
1
answer
127
views
DBCC ShrinkDatabase is increasing my file size
Yesterday, I asked a question about how to reduce the size of a database. The community suggested using the command dbcc shrinkdatabase. I followed their advice and ran the command for over 24 hours. ...
- 17k
2
votes
2
answers
2k
views
Why dbcc's shrinkdatabase and shrinkfile aren't working?
Ok, I got it. Shrinking your database is wrong. You hate it. But let me explain.
I have a 1TB P1 Azure SQL Database in production with ~50 tables, where ~5 of them are JSON containers. This was the ...
- 125
3
votes
1
answer
332
views
How to reclaim space on Sql Server Azure
I have an Azure Sql Server database (in the cloud, not on a VM)(Not a managed instance) which currently has 270 gigs of space allocated to it.
However, the actual space used is 27% (81 gigs).
I would ...
- 133
3
votes
1
answer
2k
views
Fastest way to shrink LOB data in SQL Server
We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present.
Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular".
Since the ...
- 83k
0
votes
0
answers
149
views
How does shrinking of tempdb log file work?
On the following documentation page:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16
It states that:
Assume that tempdb has two files:...
- 1,159
48
votes
5
answers
71k
views
When is it OK to shrink a Database?
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
- 32k
0
votes
2
answers
695
views
What causes disk space growth from Informatica load onto SQL Server?
We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
- 2,201
10
votes
2
answers
18k
views
How to free the unused space for a table
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.
I use SQL Server 2014 Express edition with 10GB limit of ...
- 83k
299
votes
4
answers
347k
views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the transaction log ...
- 83k
-1
votes
1
answer
217
views
how to shrink database with a big file in SQL server
I have deleted some of the tables on the SQL server2019 database this table is around 1.5TB. And already shrink the database but there is no effect.
- 35.2k
0
votes
0
answers
469
views
PostgreSQL Database Data and Log size
I am looking for a way to list PostgreSQL Database Data and Log sizes.
Using the following command we can check the total database size based on the documentation https://pgpedia.info/p/...
- 170
9
votes
5
answers
29k
views
DBCC SHRINKFILE works with file_id but not with logical name
I am trying to shrink a database file but am running into errors.
Using the file_id from sys.database_files works but using the logical file name yields an error.
The logical file name is the same in ...
- 83k
0
votes
1
answer
238
views
How to remove unallocated space after partitioning
I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode.
Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do ...
- 24.7k
5
votes
3
answers
13k
views
Shrink the database only up to its initial size which is set after creating database
In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.
So now if create a new ...
- 2,201
0
votes
2
answers
237
views
Database not shrinking and cant insert data even thought file has free space
I have SQL Server Express 2019 on my local system on a large SSD with >50Gb free disk space.
I had a 14Gb database on production, which I copied, deleted a bunch of old data, and reduced the size ...
- 34
0
votes
2
answers
198
views
DBCC Shrinkfile after full backup restoration but before differential restoration
My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to ...
CommunityBot
- 1
0
votes
2
answers
202
views
Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it
We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data ...
CommunityBot
- 1
1
vote
1
answer
311
views
Reclaiming allocated free space from partitioned tables
We have a data warehouse with about 4TB worth of datafiles. A couple of these databases consist of partitioned tables.
Since we're running out of disk space (on premise environment), we are looking to ...
- 27.8k
-3
votes
1
answer
147
views
After mass deletion of data, what should be done first: Index rebuilding or shrinking filegroups?
When we delete huge amounts data from a table, what should be the best thing to follow: Shrink the file group first and rebuild indexes, or Rebuild indexes then shrink the file group?
- 25.2k
30
votes
2
answers
173k
views
Best practice to shrink Tempdb in a production environment
What is best practice to use when shrinking Temporary db in SQL Server 2008?
Is it risky to use the following?
use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
...
- 1
18
votes
3
answers
87k
views
Shrink Transaction Log While Using AlwaysOn Availability Group
We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database.
I have read here doing the ...
- 1
-2
votes
2
answers
1k
views
Why not reduce the database log file size with shrink?
I use SQL server database.
To reduce the database log file I set the recovery model of the database to simple and then Shrink database but the log file does not get the effect.
What is problem?
- 17k
-5
votes
1
answer
799
views
Does the shrink data and log file command need to be done during maintenance hours or less busy hours?
Our data and log files have increased drastically due to a recent large INSERT query and DELETE query. The database in simple recovery mode.
Does the shrink data and log file command take the database ...
- 3,031
0
votes
2
answers
54
views
Recovering space on a restored and pruned SQL Server database for testing purposes
I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few ...
- 299
1
vote
1
answer
394
views
Unable to shrink data file with LOB data sql server 2017
I have a huge table that stores pdf documents using datatype "image".
Recently we started to offload files to blob storage, that is why we want to shrink main data file.
All my tries to ...
- 17k
8
votes
1
answer
7k
views
What is DBCC SHRINKFILE actually doing?
I have a database which was 11TB in size. I recently truncated over 5TB of data from this database.
(I am fully familiar with all the reasons why you would not normally shrink a database please)
I'm ...
- 1
1
vote
2
answers
416
views
How to compress a non-shrinkable table with nvarchar(max) fields?
We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost ...
- 865
0
votes
1
answer
633
views
Shrink the data file on Azure SQL MI
I have a 600GB DB with 400GB free space in the data file on Azure SQL MI.
I am planning to shrink it and save the storage, but it's a critical DB with the user sessions.
There are two plans:
Plan A: ...
- 35.2k
0
votes
0
answers
99
views
transaction log grows despite recovery=simple. SHRINKFILE does not release space
I have this script that restores a DB, sets recovery=simple, trims some tables & shrinks files:
RESTORE DATABASE [ABC] FROM DISK (....)
ALTER DATABASE [ABC] SET RECOVERY simple
go
exec [ABC]..trim
...
- 10.9k
1
vote
3
answers
355
views
Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space
We are having an issue with our databases. Here's what happens:
All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Oracle 19c cannot shrink blob column
I deleted a bunch of old data from a schema, including BLOB data, and afterwards I tried to shrink and compact everything. I managed to shrink all tables (enable row movement, shrink space compact, ...
- 40.4k
32
votes
6
answers
165k
views
Shrinking the log file does not reduce size
I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.
When I try to shrink the log file, it's not shrinking.
I know shrinking a ...
CommunityBot
- 1
0
votes
1
answer
326
views
After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind?
MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - ...
CommunityBot
- 1
37
votes
4
answers
19k
views
I Need to Shrink My Database - I just freed a lot of space
This question is asked in various forms here but the question boils down to:
I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.
How can I ...
- 239
13
votes
6
answers
105k
views
Oracle shrinking / reclaiming free tablespace space
I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.
TB1 54 % 90880 41383 49497 46 98304 50 % ONLINE PERMANENT
The tablespace has 3 datafiles.
E:\ORACLE\...
- 998
0
votes
2
answers
104
views
What could be the reason for shrinking the transaction log file before and after the database backup?
I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
- 18.9k
10
votes
10
answers
21k
views
Shrink a database below its initial size
I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% ...
- 2,201
-1
votes
1
answer
3k
views
Reducing .mdf file size
I have a drive, :d, in my server where I have my .mdf file stored and now all of a sudden I notice that the drive :d has no space left.
When I checked the .mdf file of the database demo1, it is 1.6TB ...
- 101