DBCC SHRINKFILE - Doesn't shrinking

Question

Hello,
I am running DBCC SHRINKFILE but seems that it's not working.
I have ran same command few months back and it was working fine.
I have Sql Server 2014 and one of the database is almost 1.7 TB size and we are almost Disk space FULL, I know that it's not a good option but we are at the limit to increase the disk space and currently we don't have any other option to free up some space.
Our DB size is 1750 GB and Space Available is 700 MB when I look DB Properties.
I am running following command:
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

set @DBFileName = 'SqlDBData'  
  
set @TargetFreeMB = 10000  
  
set @ShrinkIncrementMB = 5000  
  
-- Show Size, Space Used, Unused Space, and Name of all database files  
select  
        [FileSizeMB]    =  
                convert(numeric(10,2),round(a.size/128.,2)),  
        [UsedSpaceMB]   =  
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,  
        [UnusedSpaceMB] =  
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,  
        [DBFileName]    = a.name  
from  
        sysfiles a  
  
declare @sql varchar(8000)  
declare @SizeMB int  
declare @UsedMB int  
  
-- Get current file size in MB  
select @SizeMB = size/128. from sysfiles where name = @DBFileName  
  
-- Get current space used in MB  
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.  
  
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName  
  
-- Loop until file at desired size  
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB  
        begin  
  
        set @sql =  
        'dbcc shrinkfile ( '+@DBFileName+', '+  
        convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '  
  
        print 'Start ' + @sql  
        print 'at '+convert(varchar(30),getdate(),121)  
  
        exec ( @sql )  
  
        print 'Done ' + @sql  
        print 'at '+convert(varchar(30),getdate(),121)  
  
        -- Get current file size in MB  
        select @SizeMB = size/128. from sysfiles where name = @DBFileName  
          
        -- Get current space used in MB  
        select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.  
  
        select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName  
  
        end  
  
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName  
  
  
-- Show Size, Space Used, Unused Space, and Name of all database files  
select  
        [FileSizeMB]    =  
                convert(numeric(10,2),round(a.size/128.,2)),  
        [UsedSpaceMB]   =  
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,  
        [UnusedSpaceMB] =  
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,  
        [DBFileName]    = a.name  
from  
        sysfiles a  

Is it I am reaching limit or "Space Available: is only 700 MB then it's not showing?

Previously few months back when I ran then it was showing me the progress and see as below sample result kind message:

(2 rows affected)  
  
(1 row affected)  
Start dbcc shrinkfile ( SqlDBData, 1768564 )   
at 2021-07-19 15:52:32.250  
  

Answer 1

Hi @pdsqsql ,

Welcome to Microsoft Q&A!
Please see this document about DBCC SHRINKFILE: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15
Disk space is full, and shrinking is no solution to the disk exhaustion situation. You need to increase the disk space.
If you do not want to do log backup, you can change your recovery model into SIMPLE. This is not recommended if your database is a production database.

Best regards,
Seeya

---

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 2

Thanks Seeya.
My Database is already in "SIMPLE" Recovery mode. So no Log backup and this is a Datafile I am shrinking not a log file as log file space is not an issue, most of all the space taken by Data file.
I am fully aware about "shrinking is no solution" but right now adding Disk space is not an option so we need to do the one time SHRINK operation.
Did you see my description why it's not shrinking?
Your insight in the issue really appreciated.

Thank you for your help!

Answer 3

Thanks SeeyaXi.
But any specific reason it's not showing any impact?
How it will reclaim the Disk space as we are almost at full?

Do I have to restart Sql Server Service to see any impact as my disk space showing same size?

Answer 4

So you have a database that is 1750 GB full of which 700 MB is not allocated if I understand correctly. Then you can shrink the database at maximum 700 MB to 1749 GB (rounded). That is not particular meaningful.

You will have to add more disk. And if that is not an option, you will need to create some free space in the database. Here are a couple of options do this:

1. Purge data you can live without.
2. Drop indexes that you really need. (It is not uncommon to find databases with redundant indexes).
3. Compress tables or indexes.
4. If you are on Enterprise Edition, replace the clustered index with a clustered columnstore, this can be a big compression gain.

Although, with only 700 MB free, the latter two options may not really be available to you. If you have a 200 GB table you want to compress down to 150 GB, you will need 150 GB of disk space available, since SQL Server builds a new index structure. It does not compress in place.

Once you have freed up space inside the database, you could start shrinking. Although the only reason you would do that is that you desperately need the disk space for something else. Because I suspect that you will continue to pour data into that database.

Answer 5

Thanks Erland.
Unfortunately this is sql server 2014 SP1 Standard edition so Can't use that features.
I might see any UnUsed indexex If I can drop it.
Compression I might look that option but not sure.
Disk space currently they can't increase.
Purging we try but nothin currently more we can do it.