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

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 -

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

You will also be able to select the following

Click 'OK' to start the shrink process

back to top

MSSQL storage

Lascon latest major updates