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

Thank You Erland!

Answer 2

Erland,
Logs means all activity audit which storing into some of the tables so in same disk nothing logs what we think.

Answer 3

Erland,
I think Application has some kind of activities which stores logs and need to keep some data for certain period also but currently we are having hurdle to increase the disk space or moving another disk so only option to shrink the DB/File as not sure how much clean up ongoing we can do it.
Before running Shrink operation, We had lots of arguments as I tried to avoid to run it and that's why whole QA started here.
When data growth happens again then we may gonna have more space available to shrink it.

Answer 4

Thanks once again Eralnd.
I understood and fortunately application people started to purge the unwanted data so so we are right now fine but for future I wanted to set up the job so I can run periodically as a job schedule to avoid anything disk space issue as currently purging and shrink helped and of 'course your previous suggestions and help.
2.6 GB size is current space but it will change so trying to keep it standard size in my shrink parameters so I don't have to much change in once set up the job.

Answer 5

Thanks Erland.
I am completely Agreed with your point and not in a favor to do the Shrink but unfortunately I have to.
So you are suggesting to use 10 MB in or for for safe I can go with 1 GB?
I am just trying to be safe as job not much run longer.