SQL Server Maintenance Plan Shrink Database Task

By: Sergey Gigoyan | Comments | Related: > 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:

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…":

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

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:

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.

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.

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":

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.

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:

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips