mssql数据库膨胀处理方法.docVIP

数据库膨胀处理方法 一、收缩处理 1.备份数据库。无疑安全保障总是第一位 2.用sa登录查询分析器dbcc?checkdb?(‘数据库名’)设置为单一用户dbcc?checkdb(‘数据库名’REPAIR_ALLOW_DATA_LOSS) 再次检查错误dbcc?checkdb?(‘数据库名’)dbcc?checkdb(‘数据库名’, REPAIR_ALLOW_DATA_LOSS)sp_dboption?‘数据库名’,’single’,?‘false’?? ④--单表修复 Dbcc checktable(‘表名’) 或 dbcc checktable(表ID号码) 若仍存在错误，再进行有损表修复 Dbcc checktable(‘表名’, REPAIR_ALLOW_DATA_LOSS) 或 dbcc checktable(表ID号码, REPAIR_ALLOW_DATA_LOSS) 3.收缩数据库 ①清空日志 DUMP TRANSACTION 数据库名 WITH NO_LOG ②截断日志 BACKUP LOG 数据库名 WITH NO_LOG ③收缩数据库 DBCC shrinkdatabase(‘数据库名’) 4.sa登录指定数据库，创建两存储过程 dbo.sp_SOS --检索各表占用空间大小 dbo.sp_defragment_indexes --重建索引 5.执行sp_SOS，检查各表大小，截图如下： 6.仔细查看未使用空间Unused(MB)和索引占用空间Index(MB)较大的表。对这些表做如下分析操作： ①是否有主键。若无主键，则增加一列ID（int型），设置为标识列自动增长，并设置该列为主键 ②是否存在20字节以上的char类型字段。若存在，则改成varchar类型 ③检查索引列是否有允许NULL的列。若存在，则改成不允许NULL ④检查索引列是否在不同的混合索引中存在共同的列。若存在，则修改索引将公用的列单独建成一个索引，或多个字段合在一起建一个大混合索引 注： 妙手中TJ_JYJGB，TJ_JKLOG，TJ_PACSJGB表是接口相关表，未设置主键，数据量大，按说明添加主键。 TJ_JKLOG——该表已无实际作用，可直接TRUNCATE TABLE TJ_JKLOG TJ_JYJGB——检验数据中间表，若确认检验数据可以重新发送，可直接TRUNCATE TABLE TJ_JYJGB，不能确认，则建议 DELTE TJ_JYJGB WHERE SHRQ DATEADD(dd, -30, getdate())，删除30天之前的数据。 TJ_PACSJGB——老接口模式PACS数据表，若该表有数据，则是老接口模式PACS数据，不能删除 --查找无主键的表 select * from sysobjects where id not in (select parent_obj from sysobjects) and type = U order by name --查找类型为char，长度20的字段及表 select object_name(id) as 表名,* from syscolumns where xtype=175 and length 20 and id in (select id from sysobjects where type=U) order by 表名 --查找有索引，有数据的表 select a.name as 表名, b.name as 索引名 from sysobjects a, sysindexes b where a.type = U and a.id = b.id and b.indid 0 and b.indid 255 and b.rows 0 order by 表名 7.数据库重建索引 --执行 sp_defragment_indexes 90 简述：重建索引根据 dbcc showcontig 各表，检索各表的索引碎片，参数90为扫描密度，即扫描密度在90%以下的都进行索引重建。详细说明请查看sp_defragment_indexes备注及SQL相关帮助。 8.再次执行sp_SOS，检查各表大小，若仍存在索引占用空间Index(MB)较大的表，则进入企业管理器进行手工清除/重建聚簇索引动作。如下图： 二、添加作业任务自动重建索引 ①新建作业 命名为 DBreindex，所有者为sa ②步骤、新建 步骤名：随意，因为只有一个步骤 数据库：选中体检数据库 命令：exec sp_defragment_indexes 70 即执行重建索引存储过程，参数给70即可 转入高级页面设置 输出文件：路径放置在数据库文件目录，命名为dbreind