SQL Server compressed database files

When the MDF or NDF file is large, the traditional DBCC SHRINKFILE command is difficult to take effect, and the current feasible solution:

1. Create a new filegroup by rebuilding the remaining tables and specifying a clustered index to the new filegroup way to empty the source filegroup for storage space release
2. Refer to using the following script write loop to wait for the file shrink to complete:

Big Data file Shrinking

DECLARE @BeginSize BIGINT--Original size MB

DECLARE @EndSize BIGINT--Final size MB

DECLARE @ShrinkSize BIGINT--size MB needed to shrink

DECLARE @UnitSize INT--size MB per contraction

DECLARE @Loop INT--Number of cycles

DECLARE @FileName VARCHAR (256)--Shrinking file logical name

SET @UnitSize = 512

SET @BeginSize = 239493

SET @EndSize = 164413

SET @ShrinkSize = @BeginSize-@EndSize

SET @FileName = N ' logical name of data file that needs to be shrunk '

SET @Loop = 1

While @ShrinkSize > @Loop * @UnitSize

BEGIN

SET @EndSize = @BeginSize-@Loop * @UnitSize

PRINT ' DBCC shrinkfile (' + cast (@FileName as varchar) + ', ' + cast (@EndSize as varchar) + ') beging DATETIME: ' + cast (GET DATE () as VARCHAR)

DBCC shrinkfile (@FileName, @EndSize)

PRINT ' DBCC shrinkfile (' + cast (@FileName as varchar) + ', ' + cast (@EndSize as varchar) + ') END DATETIME: ' +cast (GETDATE () as VARCHAR)

SET @Loop = @Loop + 1

END

SQL Server compressed database files