sqlserver自动收缩数据库引起大量阻塞

最近遇到一个数据库设置了自动收缩，收缩期间引起大量阻塞的问题，记录下看到的文章学习 

什么是自动收缩？

 

随着数据量的增加数据库的设备文件(MDF\LDF)会不断增长,当数据库中的某些数据删除,数据库设备文件的大小并不会随着数据量的减少而减少,数据库设备需要占用的磁盘空间就没那么大了,这时候自动收缩就可以释放出磁盘空间,主要直观体现在数据库设备文件的大小上,避免资源的浪费.

在什么条件下会触发？

在开启自动收缩选项的情况下，SQL Server定期会检查文件使用情况。如果空闲空间大于25%，SQL Server就会自动运行自动收缩数据库文件的动作。

（附上微软官方链接：ALTER DATABASE SET 选项 (Transact-SQL) - SQL Server | Microsoft Learn）

例如：数据迁移删除大量数据时，空闲空间大于25%时，会触发自动收缩功能。

带来的危害（自动收缩和手动收缩）？

对于一个磁盘空间很紧张的系统，这个设置无疑是有帮助的。但是从数据库自身的健康和性能考虑，这个设置并不建议多用。这是因为：

1、数据文件收缩导致了索引的完全碎片化，索引的效率大大降低，严重影响性能。

2、数据文件的收缩同样产生了大量的I/O操作，耗费大量的CPU资源，性能下降。

3、在业务高峰期的时候可能会造成大量的阻塞。

关于收缩的建议

不到万不得已，千万不要收缩数据库。收缩数据库影响极大：

1.收缩数据库对数据库的影响极大，产生大量日志和碎片，而且会锁表。如果你的库当前正在被使用，收缩不下去非常正常。
2.收缩数据库一定要手工来做的，而且是在维护窗口期做。
3.尽量使用语句来执行，可以提示错误

4.尽量一次不要收缩太多，分几次收缩。

下面的文章详细介绍：
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/.

收缩的正确姿势

在不得不收缩的时候，参考下面的步骤

1.找到数据库中最大的几个表，重建所有索引。首先尝试指定Truncate Only收缩方式.它只是移除文件尾部的空闲空间，并不重新组织已经使用的数据页。

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);  

2 最后才考虑，不带选项的收缩。收缩不要一次性全部收缩。 可以每次收缩2G左右。不要把空间可用空间全部收缩了，可以剩余一部分比如4G。收缩完后，记得重建索引.

补充：

还有一种办法就是新建文件组，使用CREATE INDEX ... WITH（DROP_EXISTING = ON）ON语法将所有相关的的表和索引移动到新文件组。然后收缩旧的文件组。

3.可在进程中的任一点停止 DBCC SHRINKDATABASE 操作，任何已完成的工作都将保留。

4. 不能在备份数据库时收缩数据库

 可能需要收缩的场景

1.你删除了大量数据，而且数据不太可能增长。

2.要移除某个文件时，你需要先清空数据文件。

总结

那我们处理磁盘空间不足的最好的办法是什么呢？最好的办法是在最初规划时，预估好未来一年或者二年的数据增长。给磁盘划分足够的空间。设置好数据库的初始大小，并且将自动增长使用固定量增长。

参考

SQL SERVER运维日记--收缩数据库 - owen zeng - 博客园

数据库自动收缩带来的严重问题 - Double_K - 博客园

[翻译]为什么你不要收缩数据库文件 - 潇湘隐者 - 博客园