Search code examples
sql-serverdbcc

What is SQL Server's DBCC SHRINKFILE T-SQL command equivalent for UI option "‘Reorganize pages before releasing unused space"

Am trying to find if there's any equivalent option DBCC SHRINKFILE T-SQL command for the option "Reorganize pages before releasing unused space" that is seen when performing through SSMS.

Text

The usual TRUNCATEONLY/TargetSize options does reduce the file size but does it implicitly reorganise the pages also? Because no explanation is provided in the BOL - https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

--In 'thoughts'...

Solution

  • This option is confusing in my opinion.

    In reality, neither option benefits sql server as it only serves to activate the editing of percentage according to the scripting results.

    If you script both with the option checked and unchecked you see:

    Checked: DBCC SHRINKFILE (N'DatabaseName' , 160)

    UnChecked: DBCC SHRINKFILE (N'DatabaseName' , 160, TRUNCATEONLY)

    The truncate only command option is not supposed to fragment your indexes, where, without it, specifying a percentage, will.