The DBCC SHRINKDATABASE is used when it is necessary to shrink the size of the log and data files in a particular database.

Do you need to shrink a database?

All databases need some free space to execute the necessary operations. If you notice that the size of a database grows again after you have shrunk it, it means that the database needs this space for its regular operations. In such a case, it would be unproductive to shrink the database repeatedly.

Also, we would recommend not to change the AUTO_SHRINK database option from False to True, unless you are certain that you really need it.

But it is useful to apply a DBCC SHRINKDATABASE operation after a table was dropped or truncated because such operations create lots of unused space.

How to Use DBCC SHRINKDATABASE Operation

Let’s consider a few ways of how to execute the DBCC SHRINKDATABASE operation:

You can do it by using the T-SQL command

DBCC SHRINKDATABASE 
(database_name|database_id|0 
     [,target_percent] 
     [,{NOTRUNCATE|TRUNCATEONLY}])
[WITH NO_INFOMSGS]

The easiest way is to use SqlBak

If you prefer to use DBCC SHRINKDATABASE operation automatically on a regular basis, then use SqlBak. With the help of SqlBak, you can create not only a backup job but also set a maintenance job for your database. To do it, simply click “Add new job” on your SqlBak Dashboard page and create a maintenance job.

In the opening window, you will find six simple paragraphs: “Select server”, “Select DBMS connection”, “Schedule maintenance”, “Send email confirmation”, “Maintenance scripts”, and “Job options”.

To set a maintenance job, select the server that you need to work with, check the connection to your DBMS. If you need to make it regularly, set up “Schedule maintenance” by selecting the date of the first start and specifying the interval with which the operation will be repeated. Also, you can add an e-mail address that will get “success” or “fail” confirmation letters.

Once the settings above are made, add a maintenance script in the “Maintenance scripts” section.

You can do it by yourself, just type the T-SQL command or click the “Insert from a template” button and select the “Shrink database” option. Don’t forget to select the database to which it will be applied. Once it was done, simply press the “Add script” button.

Now the only thing you need to do is press the “Save & Exit” button to save and run your maintenance job.