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 avoiding 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

1. Shrink the size of the log file or data for the current database.
2. Remove files from the database.
3. Empty a file by moving the data from the specified file to other files in the same filegroup.
4. Shrink a file to a size that is less than the size specified when it was created.

Recommendations on using DBCC SHRINKFILE

1. Use DBCC SHRINKFILE following such operations as drop table or truncate table, because they create lots of unused space.
2. Please note, if after you shrunk your database it grew again it means that the database needs this space for its normal work.
3. The other reason not to use shrink operation is that after such operation the fragmentation state of indexes substantially increases.
4. 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 the 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 that will execute a DBCC SHRINKFILE operation regularly and automatically.

To set up a maintenance job click the “Add new job” button on your Dashboard page, in the pop-up window select “Maintenance job” as a “Job type” and press the “Create job” button,

and complete these simple steps:

In step #5 “Maintenance scripts” click on the”Add script” button and choose “SQL script”.

You can add a script you need manually in the opened box,

or choose it from the list of predefined by pressing the “Insert from a template” link.

Once completed, don’t forget to set up a script execution schedule and enable email notifications, if necessary. That’s it.