MSSQLServer数据库空间优化指南.doc

SQL Server数据库空间优化指南由于新的持续集成测试需要同时配置SQL Server、Oracle、DB2三种数据库，但是在目前的测试环境中，只有一台可用的SQL Server数据库服务器，上面安装了数十个数据库，空间已经几乎耗尽（总共120G的数据文件空间，只剩下3G左右的磁盘空间），随时都可能出现磁盘空间不足的问题。为了解决磁盘空间的问题，对安装在此服务器上的数据库进行了初步分析，通过对日志文件和数据文件的大小进行整理，结果如下：1. 日志文件分析：l 日志文件超过10G的1个（如BOS63nanche，光日志文件就有12G）l 日志文件大小介于14G之间的6个l 其它的日志文件均小于1G2. 数据文件大小分析：l 数据数据超过5G的5个l 其它的数据文件14G的4个l 其它数据库的日志文件小于1G 通过上面的初步分析，决定对相关的文件进行瘦身处理：1. 日志文件瘦身：l 在SQL Server 2005数据库中，当日志文件大小达到一定的容量时（暂时还没有找到临界值，目前都是在10G以上的日志文件中收缩时发现无法收缩成功），已经无法通过自动收缩日志的（收缩过程中会报错导致收缩失败，同时也无法分离），对于这种情况，只能先停止SQL Server数据库，然后手工直接删除对应的ldf日志文件。然后再重启SQL Server数据库，在加载时会自动报告该数据库缺少日志文件，按照提示生成一个新的日志文件即可，此步骤直接腾出了12G磁盘空间；l 对于日志文件在14G之间的数据库，通过SQL脚本，直接进行日志收缩处理（也可直接通过管理控制台以界面的形式进行日志文件的收缩）-清除数据库日志dump tran yytest with no_logbackup log yytest with no_logdbcc shrinkdatabase(yytest) 2. 数据文件瘦身：l 分析数据库中哪些对象的记录数最多，方法如下：-统计大表数据量(找出10万以上的数据库表)select ,b.rows from sysobjects a, sysindexes b where a.id=b.id and b.indid in (0,1) and b.rows100000结果如下图：l 以大数据量的表结果输出来看，其中有一个表T_PM_UserOrgPerm记录数最多，达到百万行级。下面即以此表为例进行表占用的空间情况的分析及处理l 分析其空间占用情况：-统计表占用空间exec sp_spaceused T_PM_UserOrgPerm结果如下： 备注：从表空间的占用情况来看，索引的大小为数据日志的4倍左右，存在碎片的可能，进一步进行索引分析备注：总共9个索引，碎片率最小都在74以上（其中的0.01的为重建索引后的，未优化前大于80），明显碎片率过高，有重建索引的必要。l 对此表进行全部索引重建，可以有两种方式进行重建操作，a) 界面操作（选中该表-索引-全部重新生成（右健），重建完成后，结果如下：b) 通过SQL命令操作：-重建指定表的全部索引DBCC DBREINDEX(T_PM_UserOrgPerm,70) 备注：通过对该单表的索引优化重建，节省了约1.2G的磁盘空间。如果数据文件比较大，可能直接对整个数据库进行索引重建：-重建数据库全部索引exec sp_msforeachtable DBCC DBREINDEX(?)l 文件收收缩，对其它大数据量表重新上述操作步骤后，单表占用的空间会减少，但是默认情况下，数据库大小不变（数据库剩余空间增大），这里需要对数据库进行自动或手工收收缩处理（可以设置成自动收集，这样数据库会自动以一定的时间间隔对数据库进行收缩）。通过上面两步的处理，磁盘空间从原来的4G增加到了25G，腾出了21G的磁盘空间。结论与体会：平时我们可能对测试环境的优化与监控并不是很重视，但在实际测试中，常常会碰到资源争用与资源不足的情况，因此，对测试环境的优化不仅是必要的，而且是必须的。同时在优化的过程中，也可以形成对环境优化类知识的积累，既可以促进产品设计的改善，还可以帮助客户进行环境的定期优化与改善，提高用户的满意度（比如上面的BOS63Nanche，如果及时进行了日志文件的截断，则不会存在日志文件无法收缩的问题）。另外，本文只是讨论了对数据库文件进行优化，实际上在实际的应用中，还可以对数据库表中的数据进行优化调整，比如一些日志表记录，保存一定周期内的日志记录就够了，如果长时间保留，同样也会耗费大量的磁盘空间（之前使用过用友NC系统，由于该系统每天都有大量的数据交