文件自动增长和自动收缩sql server

1.4  文件自动增长和自动收缩

SQL Server允许用户设置数据库初始值、最大值，可以自动增长或者自动收缩。通过这些设置，可以防止数据库空间问题而导致的应用程序修改失败或者SQL Server把硬盘空间耗尽之类的事情发生。一般来讲，如果数据库不是很繁忙，默认的设置（开启自动增长）能够满足大部分的需求。但是数据文件和日志文件增长本身是一件耗费系统资源和影响性能的工作。所以如果完全依赖SQL Server自动完成，可能会导致系统性能不够稳定。一个管理得比较精细的系统，应该预先考虑到可能的空间使用需求，提前规划并引导数据的流向。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成，及时满足客户端应用的需求。

那么怎么才能达到这样的目的呢？在谈论最佳配置之前，首先要讨论一下SQL Server数据文件和日志文件空间申请的一些特点。还是以下面这个数据库（如图1-31所示）为例。它有3个数据文件（假设它们属于同一个文件组）和两个日志文件（见表1-7）：

表1-7  插入数据前的文件大小

假设现在有个客户端要插入40 MB的数据，20 MB的日志记录，SQL Server会怎样往这些文件里写呢？SQL Server对于数据和日志有不同的处理方法。

数据文件

SQL Server会按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里。如果某个文件已经写满了，SQL Server就不再继续往这个文件里写，而是写到其他有空间的文件里面。

在上面的例子里，因为3个文件空闲空间是200:100:100，40 MB的数据就按照20 MB:10 MB:10 MB的比例写入了这3个文件。

图1-31  范例数据库的数据文件和日志文件

 

日志文件

SQL Server对于日志记录是按照严格的顺序写入的。所以虽然这里有两个日志文件，SQL Server还是在一个时间点只写其中的一个。只有这个文件写满了，SQL Server才会写入另外一个。

在我们的范例数据库里，20 MB的日志记录都会写入MyDB_log1。

有时候我们会加入多个数据库文件，并把它们放在不同的硬盘上，以达到分散I/O负载的目的。从上面的处理方式我们可以看到，如果想要达到这个目的，对于数据文件，就必须保证同一个文件组里的所有数据文件都有基本一样大小的空闲空间。（不是这些文件一样大就可以的。）如果某个硬盘上的数据文件已经被写满了，SQL Server就不会再往这个硬盘上写了。如果空闲空间相对比较少，SQL Server写的数目也会相对减少。

对于日志文件，由于SQL Server在同一个时间只写一个文件，所以加入多个日志文件对性能基本不会有什么帮助。

如果文件全部都写满了，SQL Server会怎么处理呢？在这里数据文件和日志文件也会稍有不同。对于数据文件，SQL Server会选取其中一个文件（可能是任意一个）做自动增长，而不是让每一个数据文件都做自动增长。所有后面的数据都写入这个做了自动增长的文件里，直到这个文件再次写满，SQL Server要做下一次自动增长为止。换句话说，依靠自动增长，只能看到一个文件增长，很难享受到I/O负载平衡的效果。

对于日志文件，SQL Server自动增长当前的日志文件，以保证日志记录的连续性。

当某个操作触发了文件自动增长时，SQL Server会让那个操作等待。直到文件自动增长结束了，原先的那个操作才能继续进行。如果自增长用了很长时间，原先的操作会等不及就超时取消了（一般默认的阈值是15秒），不但这个操作会回滚，文件自动增长也会被取消。也就是说，这一次文件没有得到任何增大。最坏的情况是，在一个时间点，有很多操作需要申请新的空间，可是谁都没能够等文件自动增长完就超时。这时体现在终端用户的感觉，就是任何修改操作都不能被提交，全部超时。直到有一个连接能够等足够久，让SQL Server把这个自动增长做完。做完以后，其他本来超时的操作又忽然都能恢复正常。

为什么一个自动增长可能会花比较长的时间呢？这基本上都是由于每次需要增长的空间太大造成的。数据文件是按照8 KB为单位存储的。所以做数据文件自增长的时候，SQL Server也要对这些新增加的部分进行格式化。如果一次要增长很大的空间，比如，上GB或者几十GB，这个格式化的过程就会很耗时。SQL Server 2005以后的版本采用了延迟写技术。只要增长的新空间已经分配好，这次自动增长就算大功告成。SQL Server会用一个后台的线程把剩余的格式化做完。这样就大大缩短了一次自增长的时间。前端不再容易遇到超时失败。

还有一种极端，就是每次自动增长值太小。SQL Server要做好几次自增长才能满足操作需求。同样的大小，一次一步到位花的时间比分好几次增长要少许多。所以自动增长值也不能太小。

总之，设置数据库自增长要注意以下几点。

（1）要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比较小的数据库，设置一次增长50 MB到100 MB。对大的数据库，设置一次增长100 MB到200 MB。

（2）要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。

（3）设置文件最大值，以免SQL Server文件自增长用尽磁盘空间，影响操作系统。

（4）发生自增长后，要及时检查新的数据文件空间分配情况。避免SQL Server总是往个别文件写数据。

除了自动增长，数据库还有一个自动收缩的功能。如果设定了这个功能，SQL Server每隔半个小时就会检查文件使用情况。如果空闲空间大于25%，SQL Server就会自动运行DBCC SHRINKFILE的动作。所以这个功能能够防止数据库申请过多的空间而不使用。对一个硬盘空间很紧张的系统，这个设置无疑是有帮助的。但是从数据库自身的健康和性能考虑，这个设置并不建议多用。这是因为：

（1）SQL Server只有在空间用尽的情况下才会做自动增长。如果没有找出自增长的原因，从而从根本上避免空间用尽，虽然能够暂时用DBCC SHRINKFILE功能收缩文件大小，但是下次数据库还是有可能长大。收缩数据库只是一个治标不治本的方法。

（2）数据文件收缩会给文件带来更多的碎片。

（3）不管是数据库收缩，还是增长，对SQL Server来讲都是件浪费资源的事情。在负载比较重的系统里，对性能的影响尤其大。它们是应尽量避免而不是鼓励的操作。

因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

 

 

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO


size 就是初始大小，maxsize 是最大大小，filegrouth 是增量(可能是大小，可能是百分比)
当文件大小增长到一定值就会 按增量 增长。