How to run “DBCC SHRINKDATABASE”

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 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 it’s regular operations. In such case it would be unproductive to repeatedly shrink the database.

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 DBCC SHRINKDATABASE operation:

You can do it by using 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 maintenance job” on your SqlBak Dashboard page.DBCC SHRINKDATABASE - DashboardIn the opening window you will find five simple paragraphs: Computer, SQL Server, Maintenance scripts, Schedule maintenance and Send email confirmation. To set a maintenance job select the computer that you need to work with, check the SQL Server connection and now add maintenance scripts. DBCC-SHRINKDATABASE-point-1-3You can do it yourself, just type T-SQL command or click “Add from 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 “Add script” button.DBCC SHRINKDATABASE Shrink databaseIf 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.DBCC SHRINKDATABASE point 4 and 5

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

Leave a Reply

Your email address will not be published. Required fields are marked *