Why Data file is not shrinking in SQL Server? | DBCC ShrinkFile doesn’t shrink file

This article help to resolve,

Data file is not shrinking in SQL Server | DBCC ShrinkFile doesn’t shrink file

You can follow the steps and check which best solution work for your case.

What is issue? or Why Data file is not shrinking in SQL Server or DBCC ShrinkFile doesn’t shrink file

Database size was grown to 8 TB. We ran data purge script to delete old data from database. Post that 7 TB space got free up and it was showing in database file as well. Around 97 percent free space was available post old data purging.

But when we tried to shrunk file using DBCC shrinkfile query , database was not releasing space.
Checked standard reports in SSMS for top disk usage by top tables and Databases space used. Both reports was showing space is available.

What solution tried to resolve DBCC ShrinkFile doesn’t shrink file? or Data file is not shrinking in SQL Server

Step 1 : Tried to change initial size of database by reducing size to 50 MB and post that run DBCC SHRINKFILE but database was not shrinking

USE [master]
GO
ALTER DATABASE [testdb] MODIFY FILE ( NAME = N'1testdb', SIZE = 102400KB )
GO

Step 2: Above step not worked so we tried to rebuild indexes on databases so that all index pages rearranged and

allowed DBCC SHRINKFILE to shrink database file. Run index maintenance to rebuild all indexes and update statistics.This option work most of the time but this time doesn’t work.

DBCC CHECKDB (TestDB)

Post DBCC CHECKDB completion it return below result which shoes there was some allocation errors in database causing database not to shrunk.

Msg 8905, Level 16, State 1, Line 3Extent (1:1051239384) in database ID 19 is marked allocated in the GAM, but no SGAM or IAM has allocated
it.Msg 8905, Level 16, State 1, Line 3Extent (1:1051244872) in database ID 19 is marked allocated in the GAM,
but no SGAM or IAM has allocated it.
CHECKDB found 2 allocation errors and 0 consistency errors not associated
with any single object.CHECKDB found 2 allocation errors and 0 consistency errors in database 'Test'
.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test).

How to resolve DBCC ShrinkFile doesn’t shrink file?

As there was allocation errors observed, we need to repair those to make database relese space. We were facing space issue and there is chance to get database in suspect mode and space increse is not an option as there is free space in database.

So post taking approval from customer we ran

DBCC CHECKDB ('TestDB', REPAIR_ALLOW_DATA_LOSS)
GO

When we ran this it completed successfully. This took time as our database was 8 TB so we executed in non business hours.

This query completed ad shown below message. This fixed allocation error.

Estimated rollback completion: 100%.Msg 8905, Level 16, State 1, Line 5Extent (1:1051239384) 
in database ID 19 is marked allocated in the GAM, but no SGAM or IAM has allocated it.       
 The error has been repaired.Msg 8905, Level 16, State 1, Line 5Extent (1:1051244872) 
 in database ID 19 is marked allocated in the GAM, but no SGAM or IAM has allocated it.       
 The error has been repaired.CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
 CHECKDB fixed 2 allocation errors and 0 consistency errors not associated with any single object.
 CHECKDB found 2 allocation errors and 0 consistency errors in database 'Test'.
 CHECKDB fixed 2 allocation errors and 0 consistency errors in database 'Test'.

Post this we re ran DBCC SHRINKFILE command and it got executed successfully. Shrink operation will take time to complete as per your database size.

--Change database Name and Size
USE [testdb]
GO
DBCC SHRINKFILE (N'testdb' , 8)
GO