Synapse Analytics Shrink Database

This article is contributed. See the original author and article here.

In the event that you have a large Synapse Analytics Database and you have been cleaning out large objects which were no longer required consider running a shrink on your database  to reduce your database size on disk. Since July 2020 we now officially support the execution of DBCC ShrinkDatabase within Synapse Analytics. 

 

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/release-notes-10-0-10106-0#july-2020

 

The functionality works the same was as SQL Server and we have the same guidelines when using it, for additional information review our official documentation. 

 

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-ver15

 

Or just execute the statement from your User database. 

 

DBCC ShrinkDatabase(‘DATABASENAME’) 

 

CONSIDERATIONS

The Shrink Operation is an IO Intensive operation, It is advised to perform the operation with no other activities running on the database. The Shrink operation can take the Synapse Database Offline.

 

If you have very large CCI Objects consider rebuilding your CCI Objects with the largest RC available to ensure the highest quality row groups and to optimized the overall shrink operation. 

 

To determine the amount of unallocated space before running the shrink, run an sp_spaceused on your database and review the output to determine if it is necessary to perform the shrink. 

 

Do not shrink frequently or make this part of regular maintenance. 

 

Take into consideration that the shrink operation will have an affect on the data warehouse snapshot charges.  

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.