SQL Server Maintenance Plan Shrink Database Task


By:   |   Updated: 2020-01-10   |   Comments   |   Related: More > Maintenance

Problem

In some cases, a SQL Server database occupies much more space than actually is used by the data or would ever be used. In such situations, it would be optimal to reduce the size of the database and return the storage space to the operating system. The "Shrink Database Task" in the SQL Server Maintenance Plan is designed especially for performing the above-mentioned process. In this article, we will use a Maintenance Plan to create a Shrink Database task.

Solution

When a new database is created, the initial size of the data and log files are usually set to a small value. Additionally, if the default settings are used, the autogrowth settings of these files will be set to "ON" and their growth will be unrestricted.  For example, the default settings were used while creating the TestDB database and as it is visible from the Properties window, the initial size of the log and data file is set to 8 MB and the autogrowth is unlimited:

database properties

However, many DBAs set their preferred settings for the database files initially for the size, autogrowth option and the maximum sizes of their files.  This is because when the database is used intensively and grows rapidly, autogrowth operations happen quite often. The autogrowth process is resource-consuming and it is reasonable to define the optimal initial size of the database depending on the workload in order to avoid frequent autogrowth processes.

In some cases, a SQL Server database can have much more space than its data actually consumes. An example of this can be when a huge amount of historical data is removed from the database and the actual data in the database is now much smaller and occupies a lot less space in the database. In such a situation, shrinking the database can be an optimal solution if a minimal amount of disk space is available.

Shrinking the database reduces the database size to a specified value by releasing unoccupied space to the operating system. Performing a database shrink regularly, however, is not recommended as it is a very resource-consuming operation and can significantly affect the database performance. Even more, shrinking can cause index fragmentation and therefore, it is important to perform index rebuilds after shrinking. Thus, in general the database shrink operation should be avoided and it should be done only if there is a strong reason, like the above example describes.

Now, we are going to illustrate how to create and configure the Shrink Database Task. We will use the Maintenance Plan Designer to create the task.

To do so, in SQL Server Management Studio we right click on "Maintenance Plans" under "Management" we choose "New Maintenance Plan…":

new maintenance plan

Then, we need to choose a name for our task. We are going to perform the shrink operation on the TestDB database.

new maintenance plan

After clicking "OK", the Maintenance Plan Designer opens and we can drag and drop the "Shrink Database Task" from the toolbox to the design surface:

shrink database task

We do not want to schedule this task, so we keep the schedule "On Demand". It is not recommended to shrink databases without a strong reason, so this should only be done as needed. To configure the task, we double-click on the Shrink Database Task we dropped in the designer.

shrink database task

We choose TestDB from the database list and then click on the "View T-SQL" to see what will really happen when this is run. We can see that a DBCC SHRINKDATABASE is executed. If we switch the options in the "Amount of free space in database files" we can see that the T-SQL code is changed slightly as underlined in both screen shots.

shrink database task

To understand this, we should understand the functionality of the DBCC SHRINKDATABASE. The syntax is the following:

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

The target_percent specifies the percentage of the free space of the database file after shrinking it. This is the "Amount of free space to remain after shrink" field in SQL Server Management Studio.

Selecting the NOTRUNCATE option means that the pages inside the file will be rearranged and the used pages will be moved in the front of the file. As a result, the free space will be at the end of the file. This action does not release the size of the file as the free space remains inside the file and is not returned to the operating system. NOTRUNCATE works only for the data files and does not affect the log file.

The TRUNCATEONLY option does not rearrange pages inside the file and releases all free space at the end of the file to the operating system. Unlike the NOTRUNCATE, it also affects the log file. In our example, we will use "Return freed space to operating system" and then save the task by clicking on the "Save" button. To test the task, we locate it under the "Maintenance Plans", right-click on it and select "Execute":

execute maintenance plan

The task is successfully executed and if we had a database with a visible amount of unused, free storage space, we could see that the files’ sizes were reduced.

execute maintenance plan

Hence, this article illustrates how to configure the Shrink Database Task via Maintenance Plans. It is vital to mention once again in this article that shrinking the database without having a justified reason is generally not recommended.

Conclusion

Overall, the Shrink Database Task can be used to reduce the database size by shrinking its unused space. This task is recommended to use only in case of a real necessity as it is a resource-consuming task and causes index fragmentation. Therefore, in our example, we have not scheduled it to emphasize that it should be executed manually and only if there is a strong argument supporting the reduction of the SQL Server database size. 

Next Steps

To find more information about the discussed topic, please follow the links below:



Last Updated: 2020-01-10


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools