Questions tagged [shrink]
Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space.
236 questions
4 votes
1 answer
695 views
What is Microsoft SQL Server doing when I manually reduce the file size in Database Properties > Files?
In Microsoft SQL Server I can go into Database Properties > Files and change the Size of a file to a lower number. If there's space available, it will actually reduce the file's size, and never ...
- 59
1 vote
1 answer
91 views
SQL Server Shrinkfile Stops at 76% Due to LOB and System Data—Is Partial Shrink Possible?
Problem: I am working on a SQL Server 2019 database where I deleted a large amount of user data (~500 GB). After deletion, I attempted to shrink the data file using: DBCC SHRINKFILE (N'web_Data', ...
0 votes
1 answer
49 views
Shrinking log files on AG to manage VLF's
some of our databases have excessive VLF's in their log files and I plan to shrink and then grow them to an appropriate size. I'm going to use the Tiger Team script https://github.com/microsoft/...
- 53
4 votes
1 answer
452 views
Best practise for reducing database file size during migration to new server
Introduction I have been reading many articles about deleting from a databases, and the perils of using the shrink command. However, I have an opportunity to move a database to a new server with a big ...
- 77
0 votes
2 answers
171 views
Reclaiming space after dropping varbinary columns
I have a table of almost one million rows with two columns of datatype varbinary. These two columns store binary data that causes the database to grow to 1 TB. As this database is also restored in ...
- 1
1 vote
1 answer
110 views
If I replace images in a table, will existing BLOB data be cleaned up and the database shrink?
I recently updated a table in my database by saving small thumbnail images as a byte[] to a varbinary(max) column. This significantly improved the speed of loading thumbnails (previously I was loading ...
- 13
4 votes
1 answer
573 views
SQL Server reduce unused space on a mostly heap table database
I’m working with a 2.3TB primary data file and currently have about 1TB of unused space. Recently, I performed row-level compression on the largest table in the database, which reduced the table’s ...
- 43
0 votes
2 answers
557 views
How to shrink log file for Azure SQL Database?
My Azure Sql Database transaction log file is about 1 TB in size, but only 1 GB is in use: SELECT file_id, name, type_desc, CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS ...
- 582
1 vote
1 answer
58 views
SQL Server 2008R2 - data file grows back after shrinking and available to shrink again
After shrinking of datafile there is around 180Gb of free space on hard drive (prior to operation shrink file dialog also shows that it is possible to free up around 180Gb of space). After 1-2 days ...
- 113
-5 votes
1 answer
172 views
WAIT_AT_LOW_PRIORITY doesn work for shrink data file [closed]
When I try to make shrink data file like this: DBCC SHRINKFILE (N'DataFileName' , 1500) WITH WAIT_AT_LOW_PRIORITY I get the error: 'WAIT_AT_LOW_PRIORITY' is not a recognized option. What can be the ...
- 1
0 votes
0 answers
34 views
Reduce primary DATAFILE size for an SQL Server Database 2016
In an AG always on configuration (primary) I have a database with 10TB datafile, 50% of the datafile is a free space. Then, I want to reduce this DATAFILE from 10TB to 8TB. How to proceed for that ...
user2106896
0 votes
1 answer
192 views
SQL Server, Maintenance Plan, Shrinking DATA File
After 5 years of work on our Sql Server database, I plan to carry out a maintenance on, and we plan to eliminate more than 170 GB of useless data by truncating some tables, and after that a Shrink ...
1 vote
2 answers
1k views
Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?
If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full ...
- 89
-1 votes
1 answer
170 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 ...
- 804
0 votes
1 answer
55 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 ...
