Internet is teeming with information that using DBCC SHRINKFILE operation on a regular basis is a bad practice, and we agree in general. It can lead to low performance and high fragmentation. We recommend to avoid shrinking the data and log files unless you have a good reason to do it (see below) . If you have such a reason – you can find some useful tips below.
Use DBCC SHRINKFILE Only if You Need to
- Shrink the size of the log file or data for the current database.
- Remove files from the database.
- Empty a file by moving the data from the specified file to other files in the same filegroup.
- Shrink a file to a size that is less than the size specified when it was created.
Recommendations on using DBCC SHRINKFILE
- Use DBCC SHRINKFILE following such operations as drop table or truncate table, because they create lots of unused space.
- Please note, if after you shrunk your database it grew again it means that the database needs this space for its normal work.
- The other reason not to use shrink operation is because after such operation the fragmentation state of indexes substantially increases.
- If you decide to shrink multiple files in one database, make it sequentially not concurrently.
How to Use DBCC SHRINKFILE
Using T-SQL command
For example, you want to shrink the size of data file Adventureworks in the Adventureworks database to 10 Mb, to do this you need to execute the following command:
DBCC SHRINKFILE (Adventureworks, 10)
Using SqlBak maintenance job
With the help of SqlBak you can create not only scheduled backup jobs but also add a maintenance job which will execute a DBCC SHRINKFILE operation regularly and automatically.
To set up a maintenance job click “Add maintenance job” button on your Dashboard page and complete five simple steps.In step #3 “Maintenance scripts” you can add script manually
or choose it from the list of predefined by pressing the “Add from template” button.
Once completed, don’t forget to set up script execution schedule and enable email notifications, if necessary. That’s it.