Shrinking MSSQL Databases

How MSSQL reuses Fre Space

Free space will appear in databases as rows are deleted. The SQL Server will reuse space that is made available by deleted rows. It records the status of extents with two maps, the Global Allocation Map (GAM) and the Shared Global Allocation Map (SGAM). A GAM will indicate if an extent is completely free, while an SGAM will identify partially free extents. Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page.

The Database Engine uses these maps to find free space for inserts, but it always tries to distribute data evenly in a database.

Shrinking SQL Server 2016 Databases

You can free up unused space in a database by using a 'shrink' command. The shrinking process simply moves pages of data from the end of the file to unoccupied space closer to the front of the file. When a page is freed up at the end of the file it can be deallocated and the space returned to the file system. However, before you start to think about shrinking a database, remember that databases need free space to operate. If you take all the free space out of a database, it will quickly put some back in again. The shrinking process will also fragment the physical storage that holds the database, and it also fragments database indexes, which in turn degrades performance.

So why would you want to shrink a database? A shrink operation could be appropriate after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

There are some restrictions on database shrinking

  • You cannot shrink a database or log while they are being backed up - and you cannot backup a database while you are shrinking it.
  • You cannot shrink a database to be smaller than the size of the model database.
  • If you are shrinking an entire database, you cannot make it smaller than its original size.
  • If you shrink a multi-file database by individual files using the SHRINKFILE command, then you can make it smaller than its original size.
  • If you are using xVelocity memory optimized columnstore indexes then the shrink command will fail as soon as it finds the first such index. The database might have reduced in size a bit as the work up until that point will have succeeded. You should disable all columnstore indexes before running the shrink command and then rebuild the columnstore indexes when the shrink process completes.

Setting up automatic database shrinking

We have already mentioned that databases need free space to operate and if you remove all the free space, some will be addded again. Also, A shrink operation will generally increases the fragmentation state of indexes in the database. For these reasons, you should not set the AUTO_SHRINK database option to ON unless you have a really need to because of the way the database is processed, for instance a database that gets frequent large deletes might benefit from a regular shrink.

If you really need to shrink a database regularily, this is how to do it. You can set up a database to shrink automatically by using the ALTER DATABASE AUTO-SHRINK command, or by using the SQL Server Management Studio (SSMS). Navigate through the SSMS until you reach the databases, then you right click your mouse on the database you want to shrink. Select 'TASKS' then 'Shrink' then Database'. You will see a summary of the database size, and the amount of space that can be recovered. If you click on the 'Schedule' radio button, then you get the option to schedule the shrink process. The screens are quite simple to follow, you can select how often, and at what time the shrinks will run.

Shrinking a Database using T-SQL (DBCC)

You can shrink a database or a file using T-SQL like this -

  • Open the Management Studio and click Connect Object Explorer from the File menu.
  • This will open the Connect to Server dialog box. Select 'Database Engine' in the Server type.
  • Type the name of the instance of the Database Engine in the Server name box then click 'Connect'.
    The default instance name is the computer name; if the instance has been given a name, then the format is 'computer_name\instance_name'
  • Click 'New Query' from the Standard bar
  • Enter a commad like this

    Transact-SQL Copy DBCC SHRINKDATABASE (LasconDB, 20); GO

    This will decrease the size of the data and log files in the LasconDB database but leave 20 percent free space in the database.
  • Now consider rebuilding the database indexes to avoid fragmentation.

Shrinking with the SQL Server Management Studio

Open 'Object Explorer', connect to the required instance of the SQL Server Database Engine and expand the instance.
Now expand 'Databases' and right-click the database that you want to shrink.
Select Tasks, then Shrink, and then click Database.

You will see the follwing information

  • Database - Displays the name of the selected database.
  • Current allocated space - Displays the total used and unused space for the selected database.
  • Available free space - Displays the sum of free space in the log and data files of the selected database.

You will also be able to select the following

  • Reorganize files before releasing unused space - Selecting this option is equivalent to executing the DBCC SHRINKDATABASE shown above and specifying a target percent option.
  • SHRINKDATABASE with TRUNCATEONLY option. - By default, this option is not selected when the dialog is opened. If this option is selected, the user must specify a target percent option.
  • Maximum free space in files after shrinking - Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99.

Click 'OK' to start the shrink process