SQLServer⾃动收缩数据库属性
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 AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
--Disable Auto Shrink for the database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
数据库收缩对查询性能的影响 (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 th
e 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 h
elpful. 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中的“⾃动收缩”数据库属性，以收缩数据和⽇志⽂件并删除未使⽤的空间。 收缩数据库是⼀项昂贵的操作，应谨慎使⽤。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。