shrinking a large data file

Question

Hi All,

One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB.

Below screenshot shows free space left inside the data file.

SELECT

--DB_NAME() AS DbName,

name AS FileName,

physical_name,

CAST((size/128.0)/1024./1024. AS NUMERIC(18,2)) AS CurrentSizeTB,

CAST((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/1024. AS NUMERIC(18,2)) AS FreeSpaceGB

FROM sys.database_files;

Go

Tried to shrink the file with "TRUNCATEONLY" option, didnt work.

USE [dbname]

GO

DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY)

GO

-no space is released to OS.

--then, tried below. its running for more than 2 hours

USE [dbname]

GO

DBCC SHRINKFILE (N'db_name_dat', 512050)

GO

Am I missing anything, what is the best way to shrink or release free space to OS? Previously, we used to have a log table which has taken up all the data and we truncated the table hoping shrink file will release free space to OS but its not. Note: The logtable has LOB columns defined on it.

Please let me know if you have any other ideas to release some space to OS. Do let me know in case if you need any information from me. This exercise we are doing it in our QA env. During the weekend, we have to do it on prod. In Prod, one variable is, the database is part of AG.

Thanks,

Sam

Answer 1

You truncated the table, but have you also take measures to keep down the size of the log table in the future, like adding a cleanup job? Else there is not much point in shrinking the file.

When there are LOB columns involved, DBCC SHRINKFILE can be hugely inefficient. Although if you ran TRUNCATE TABLE, that issue may be gone.

Active transactions should not matter that much, since SHRINK works with small transactions. Still it is better to do the shrinking in a maintenance window to avoid taking resources from the production workload.

Answer 2

Hi,

Recovery Model for the database should be 'simple'.

Right click on database name->Options ..on the right hand side you can see recovery mode.

Secondly you may execute below commands before shrink:

	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	DBCC FREESYSTEMCACHE ('ALL');
	DBCC FREESESSIONCACHE;

Regards,

Answer 3

Hi @Sam

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system. 

You can decide on reclaiming the unused space in your databases depending on the type of growth you have observed.

If you feel your database size grows again then the free space is required, if you feel this much free space is not required you can go for shrink operation.

please plan shrinking activity during a maintenance window as it might cause blocking during business hours.

Also note, it is not recommended to shrink data files frequently as it can cause fragmentation and affect performance. 

Most databases require some available free space for regular day-to-day operations. If the shrink process reclaims unused allocated space that will again be shortly reused as part of the customers regular workload then there is no need to shrink the database. 

Article for your reference:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16#Recommendations 

During shrink operation, check there should be no active transaction running on the DB.

run basic sp_who2 active on every run to check if there are any active transactions.

Try to decrease the space of allocation for the DB.

Repeat this process to perform incremental shrinks.

A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. Therefore after data files are shrunk, the indexes can become fragmented. If performance issues occur, consider rebuilding the indexes once more.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#SSMSProcedureReorg

Here is similar thread where the database is part of AG- https://learn.microsoft.com/en-us/answers/questions/1138441/shrink-and-reclaim-space-for-very-huge-database?orderby=helpful

Thank you!

Answer 4

Hi @Samantha r

When planning to shrink data files, consider the following:

(1) First of all, we must understand the current usage of data files.

It is unlikely that the size of the shrinkage will exceed the size of the free space of the current file. If you want to reduce the size of the database, you must first make sure that there is indeed unused space in the data file. If the space is in use, first identify the objects (tables or indexes) that are taking up a lot of space. Then by archiving historical data, the space is freed up first.

(2) The primary file cannot be emptied. Only auxiliary data files can be completely emptied.

(3) If you want to empty a filegroup entirely, delete the objects (tables or indexes) assigned to the filegroup or move them to another filegroup. DBCC SHRINKFILE won't do this for you.

After clearing the data and objects in the data file and confirming that the data file (group) has enough free space, the administrator can run the DBCC SHRINKFILE command to shrink or empty the specified file. If you want to shrink the file, fill in the required target_size, if you want to empty the file, select EMPTYFILE. When SQL Server does DBCC SHRINKFILE, it scans data files and locks the pages being read, so the performance of the database will be affected. But this is not an exclusive act. When doing shrinkfile, other users can still read and write to the database. Therefore, there is no need to arrange special server downtime to do it, and it can generally be carried out during the database maintenance period. The DBCC SHRINKFILE operation can be stopped at any point in the process and any completed work is retained. If the operation is not completed within the stipulated time, it can also be safely stopped.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thank you all for the valuable inputs.