MS SQL Server数据库空间优化指南 - 图文

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

本文来源：https://www.bwwdw.com/article/tsqd.html