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