In this article, we will discuss the SQL Server Auto Shrink database property, which allows SQL Server to automatically shrink the database files when the value is configured as True in the database option. Automatic shrink operation is done by the server auto shrink database feature, which is the main course of this article.

在本文中，我们将讨论SQL Server自动收缩数据库属性，当在数据库选项中将该值配置为True时，该属性允许SQL Server自动收缩数据库文件。 服务器的自动收缩数据库功能可以完成自动收缩操作，这是本文的主要课程。

To avoid the activity of shrinking of data/log files whenever file size surpasses the free space in the data file, DBA (Database Administrator) must back up the log files at a regular interval. Backing up the whole database is not a good idea; one must construct or set up the transaction log also. Transaction logs will increase in numbers until it occupies all the available disk space if you don’t take back up of them. If you back up the database, it releases the free space to be reused again. Database Administrator must schedule a transaction log backup to reduce the size of log files at a fair size.

为了避免每当文件大小超过数据文件中的可用空间时就减少数据/日志文件的活动，DBA（数据库管理员）必须定期备份日志文件。 备份整个数据库不是一个好主意。 还必须构造或设置事务日志。 事务日志的数量将不断增加，直到不占用所有可用磁盘空间为止。 如果备份数据库，它将释放可用空间以再次使用。 数据库管理员必须安排事务日志备份，以以适当的大小减少日志文件的大小。

SQL Server Auto Shrink feature is disabled by default on SQL Server instance databases. In the scenario where you have a number of relatively small databases that grow to a larger size either by insertion of new tuples or by a large number of empty spaces created by deletion of several tuples, SQL Server Auto Shrink becomes pretty handy in this situation. Moreover, you must need not worry about the size fragmentation of database files.

默认情况下，SQL Server实例数据库上禁用SQL Server自动收缩功能。 在您有许多相对较小的数据库（通过插入新的元组或通过删除多个元组而创建的大量空白空间）将其增大到较大的情况下，在这种情况下，SQL Server自动收缩变得非常方便。 而且，您不必担心数据库文件的大小碎片。

While shrinking the database, you must have to consider shrinking operations on both critical as well as relatively larger databases as a part of maintenance. Besides, this one should avoid running manual shrink feature; the consequences of doing this are that one will never be acquainted with issues regarding the new or existing request. However, Transaction files shrinking is better than the data files shrinking.

在收缩数据库时，必须将收缩关键数据库和相对较大的数据库上的操作作为维护的一部分。 此外，这一操作应避免运行手动收缩功能； 这样做的后果是，您将永远不会熟悉有关新请求或现有请求的问题。 但是，事务文件收缩比数据文件收缩好。

如何激活和停用数据库SQL Server自动收缩？ (Way to activate and deactivate the SQL Server auto shrink for the database?)

Users can enable and disable database auto shrink option using SSMS and T-SQL both ways.

用户可以同时使用SSMS和T-SQL启用和禁用数据库自​​动收缩选项。

Activating the database auto shrink by using SSMS:

通过使用SSMS激活数据库自动收缩：

Users can enable or disable this option from the properties of the database, with a tag of Auto Shrink. Here, True in the drop-down will enable this option for your database.

用户可以使用自动收缩标签从数据库属性启用或禁用此选项。 在这里，下拉菜单中的True将为您的数据库启用此选项。

Enable database auto shrink using T-SQL:

使用T-SQL启用数据库自​​动收缩：

Users can execute below T-SQL statements to enable or disable the shrink database. We have used the AdventureWorks database for the above T-SQL statement.

用户可以执行下面的T-SQL语句来启用或禁用收缩数据库。 我们在上面的T-SQL语句中使用了AdventureWorks数据库。

--Enable Auto Shrink for the database AdventureWorksALTER DATABASE AdventureWorks SET AUTO_SHRINK ONGO--Disable Auto Shrink for the database AdventureWorksALTER DATABASE AdventureWorks SET AUTO_SHRINK OFFGO

数据库收缩对查询性能的影响 (Effects of database shrinking on the query performance)

Bad effects can be raised on the query performance side if you turn on the database auto shrink option and auto-growth settings together for the database. We can keep some room for the data files to grow and also avoid some frequent auto-growth events by setting up the database size to an optimum value or mostly every database have some parameters with auto-growth features enabled.

如果同时打开数据库的数据库自动收缩选项和自动增长设置，则可能会对查询性能产生不良影响。 通过将数据库大小设置为最佳值，或者每个数据库都启用了自动增长功能，我们可以为数据文件腾出空间，并避免某些频繁的自动增长事件。

For such databases, auto shrink features must be activated by us when the database is smaller, one and no more CRUD operation, so it can make shrinking of data files possible and retrieve the free space that we provisioned purposefully to keep away our databases from auto-growth events. Free space will be released automatically in the data files and log files periodically in round-robin sequential priority by the Auto shrink feature if multiple databases have turned on this feature.

对于此类数据库，当数据库较小时，我们必须激活自动收缩功能，并且不再进行CRUD操作，这样它才能使数据文件收缩成为可能，并检索我们有意提供的可用空间，以使数据库远离自动-增长事件。 如果有多个数据库已打开此功能，则自动收缩功能将以循环顺序优先级定期自动释放数据文件和日志文件中的可用空间。

For large-sized databases, auto-growth, and thereafter, the auto shrink will be executed that will result in performance issues lead by system-level fragmentation. To sum up all these, for any database, auto shrink should not be activated based on the following reasons:

对于大型数据库，将自动增长，然后执行自动收缩，这将导致系统级碎片导致性能问题。 综上所述，对于任何数据库，不应基于以下原因激活自动收缩：

• Executing the SQL Server auto shrink algorithms without any purpose will definitely going to squander the resources in a massive quantity
  无目的地执行SQ​​L Server自动收缩算法肯定会浪费大量资源
• Whether you perform SQL Server auto shrink or manual shrink, it will obviously be going to cause the index fragmentation and this eventually executes the data files shrinking also
  无论您执行SQL Server自动收缩还是手动收缩，显然都会导致索引碎片，这最终还会执行数据文件收缩
• If the server is also forcing the boundaries of the IO subsystem, running shrink may push it over, resulting in long disk queue lengths and possibly IO timeouts, this will eat up Server IO and CPU resources in a massive quantity
  如果服务器也强加了IO子系统的边界，则运行收缩可能会将其推翻，从而导致较长的磁盘队列长度以及可能的IO超时，这将吞噬大量的服务器IO和CPU资源
• System performance will be retarded by file system disk-level fragmentation, which is again the direct effect of doing shrinking and growing data files often
  文件系统磁盘级碎片会降低系统性能，这又是经常收缩和增长数据文件的直接影响

To understand more about Growth and Shrink Events in SQL Server, read this article, .

要了解有关SQL Server中增长和收缩事件的更多信息，请阅读本文， 。

For the critical database, manual shrink operation could be run at the database file level by the expert. Manual shrink file activity can be done when the delete operation is executed, and thereafter, space has been reclaimed. We must rebuild the fragmented indexes upon execution of shrink operation as shrink operation can lead to index fragmentation. Index fragmentation percentage could be checked by the user with the use of T-SQL statements of DMV’s. However, shrinking the log file should be manually done as and when requires and shouldn’t be part of regular maintenance activity.

对于关键数据库，专家可以在数据库文件级别运行手动收缩操作。 执行删除操作后，可以执行手动收缩文件活动，此后，空间已被回收。 我们必须在执行收缩操作时重建碎片索引，因为收缩操作会导致索引碎片。 用户可以使用DMV的T-SQL语句检查索引碎片百分比。 但是，应在需要时手动收缩日志文件，并且不应该将其作为常规维护活动的一部分。

To monitor the disk usage regular basis, the disk usage report could be analyzed by the user before performing the manual shrink operation using SSMS, which gives you an insight of data and log space information for a database in the display. If the user would like to get a dashboard for a calculation of Reserved Space and Free Space for database files, then Disk Report becomes very helpful. However, the disk report inhabits the information using SQL Server DMV’s. The disk report is available on the below directory using SSMS.

为了定期监视磁盘使用情况，在使用SSMS执行手动收缩操作之前，用户可以先分析磁盘使用情况报告，这可以使您深入了解显示中数据库的数据和日志空间信息。 如果用户希望获得一个仪表盘来计算数据库文件的保留空间和可用空间，那么“磁盘报告”将非常有用。 但是，磁盘报告使用SQL Server DMV占用信息。 使用SSMS，可以在以下目录中找到磁盘报告。

Database >> Reports >> Standard Reports >> Disk Usage

数据库>>报告>>标准报告>>磁盘使用情况

Here, we have a Disk Report for the [AdventureWorks] database. Real-time statistics for the data files and log files could be seen by the user. This report incorporates primary information of Total Space Reserved, Data Files Space Reserved, Transaction Log Space Reserved, and In-Memory OLTP Space Reserved.

在这里，我们为[AdventureWorks]数据库提供了一个磁盘报告。 用户可以查看数据文件和日志文件的实时统计信息。 此报告包含保留的总空间，数据文件的空间保留，事务日志的空间保留和内存中的OLTP空间的主要信息。

收缩数据库自动使用SQL Server作业 (Shrink database automatically uses SQL Server Jobs)

Such scripts could be performed by scheduling a job by the user to execute the database shrink operation with scheduler activity. As the first step in the script, find the free space in the database file and then shrink that file if defined criteria for the free space get matched. Free space should be calculated with the help of SQL Server DMV’s.

可以通过由用户调度作业以执行具有调度程序活动的数据库收缩操作来执行此类脚本。 作为脚本的第一步，请在数据库文件中找到可用空间，然后在定义的可用空间条件匹配时缩小该文件。 可用空间应在SQL Server DMV的帮助下进行计算。

Basically, database file monitoring is performed by the database administrator, which in turn steps on the database to monitor the database file size. If the pre-defined boundary is crossed by any file, then they need to perform such steps. So that activity can be automated using SQL Server job to perform on a daily basis, weekly or monthly at a predefined time.

基本上，数据库文件监视由数据库管理员执行，而数据库管理员又在数据库上跨步以监视数据库文件大小。 如果任何文件都超出了预定义的边界，则它们需要执行这些步骤。 因此，可以使用SQL Server作业自动执行该活动，以在预定时间每天，每周或每月执行一次。

The best practice is to schedule this job on the database log file only and monitor the data file free space manually. Because it can affect query performance as well. SQL Server DMV will return the data file and log file in the same result set, so file type needs to bifurcate in the T-SQL statement of SQL Server DMV. Before shrinking the file, the user can apply query logic on the total space and free space of the log file. If calculation criteria are matched with the file property, then the file will shrink with the target file size.

最佳实践是仅在数据库日志文件上安排此作业，并手动监视数据文件的可用空间。 因为它也会影响查询性能。 SQL Server DMV将在同一结果集中返回数据文件和日志文件，因此文件类型需要在SQL Server DMV的T-SQL语句中分叉。 在缩小文件之前，用户可以对日志文件的总空间和可用空间应用查询逻辑。 如果计算条件与文件属性匹配，则文件将缩小为目标文件大小。

Check free space for the database files:

检查数据库文件的可用空间：

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB,  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files;

If such criteria matched with the database files, for example, if Free space is greater than (n) MB/GB, if n(%) free space compare to total space, and many more. When free space is not available for the log file and file size is near about the max file size parameter value, then the database administrator needs to scout in the transaction log.

例如，如果此类条件与数据库文件匹配，则如果“可用空间”大于（n）MB / GB，则“ n（％）”的可用空间与总空间进行比较，等等。 当日志文件没有可用空间并且文件大小接近最大文件大小参数值时，则数据库管理员需要在事务日志中进行侦查。

Shrink database file:

缩小数据库文件：

DBCC SHRINKFILE(file_name, 5120);

Here, 5120 is a target file size in MB. So, the file will become a size of 5120 MB. Set up the T-SQL Query with required criteria in the SQL Server job step and schedule it on-off hours.

5120是目标文件大小（以MB为单位）。 因此，该文件将成为5120 MB的大小。 使用SQL Server作业步骤中的必需条件设置T-SQL查询，并安排其开关时间。

SQL Server auto shrink option cannot be enabled for all databases. Specifically, it helps for the smaller database, which performs less CRUD operation comparatively.

不能为所有数据库启用SQL Server自动收缩选项。 具体来说，它有助于较小的数据库，而数据库执行的CRUD操作相对较少。

结论 (Conclusion )

In this article, we discussed the Auto Shrink database property in SQL Server to shrink data and log files and remove unused space. Shrinking a database is an expensive operation and should be used carefully.

在本文中，我们讨论了SQL Server中的“自动收缩”数据库属性，以收缩数据和日志文件并删除未使用的空间。 收缩数据库是一项昂贵的操作，应谨慎使用。

翻译自: