SQLServer数据库在使用时间久之后,往往数据文件及日志文件会变得很大,有时候甚至高达十几G,这个时候一般做法是裁断日志以缩小日志文件、归档历史记录到新数据库或备份文件以便释放空间,然后执行收缩数据库命令来缩小数据文件大小,然而收缩命令经常并不会有太大的作用,因为收缩是以区为单位进行的,并不会对页进行整理。如果数据库上有很多碎片,如一个可容纳8个页的区实际上只存放1个页,就会造成大量磁盘空间浪费并且无法收缩。
SQLServer磁盘碎片整理的目的在于,通过重建聚集索引或重建堆自增列的方式,以页为单位进行磁盘空间整理,然后再使用收缩命令收缩数据库文件,真正意义上实现了数据库的完全收缩。
以下存储过程在MSSQL2008上测试通过,由于需要读写操作和表锁定,请避免在数据库繁忙时段运行。
/* 读取磁盘分区信息 */ CREATE PROCEDURE SP_ExtentInfo AS DBCC ExtentInfo(0) GO /* SQLServer磁盘碎片整理 */ CREATE PROCEDURE SP_ShrinkSpaces ( @UsagePercent numeric(2,2) = 0.60 --整理小于指定使用率的表空间,1为100%使用率无需整理 ) AS BEGIN --创建保存分区信息的临时表 Create Table #ExtentInfo ( fileid smallint, pageid int, pg_alloc int, ext_size int, obj_id int, index_id int, partition_number int, partition_id bigint, iam_chain_type varchar(50), pfs_bytes varbinary(10) ) insert into #ExtentInfo exec SP_ExtentInfo --使用游标,对小于指定空间使用率的表进行整理 declare @Table sysname declare @Index sysname declare @IdentityName sysname declare @sql varchar(1000) declare cs cursor for select (select name from sysobjects where id=obj_id and xtype='u'), --xtype='u'的记录为数据表 (select name from sysindexes where id=obj_id and indid=1) --indid=1的记录为聚集索引 from #ExtentInfo group by obj_id having sum(pg_alloc)*1.0/max(ext_size)/count(*)<=@UsagePercent open cs fetch next from cs into @Table,@Index while @@FETCH_STATUS=0 begin if @Table is not null begin if @Index is not null begin --重建聚集索引 set @sql = 'alter index ' + @Index + ' on ' + @Table + ' rebuild' print @sql exec(@sql) end else begin --对于堆,清空并重新写表或给自增列加聚集索引(128代表自增列) select @IdentityName=name from syscolumns where id=OBJECT_ID(@Table) and status=128 if @@ROWCOUNT=0 set @sql = 'select * into #ExtentTable from ' + @Table + ' truncate table ' + @Table + ' insert ' + @Table + ' select * from #ExtentTable' else set @sql = 'create clustered index ExtentOperaPrimaryKey on ' + @Table + '(' + @IdentityName + ') drop index ' + @Table + '.ExtentOperaPrimaryKey' print @sql exec(@sql) end end fetch next from cs into @Table,@Index end close cs deallocate cs --收缩当前数据库 DBCC SHRINKDATABASE(0) --重新获取分区信息 truncate table #ExtentInfo insert into #ExtentInfo exec SP_ExtentInfo --显示当前分区信息 select fileid,obj_id,index_id,partition_id,ext_size, object_name(obj_id) as '对象名', count(*) as '实际区数', sum(pg_alloc) as '实际页数', ceiling(sum(pg_alloc)*1.00/ext_size)*ext_size as '最大可用页数', ceiling(sum(pg_alloc)*1.00/ext_size*100.00/count(*)) as '表空间使用比率' from ExtentInfo group by fileid,obj_id,index_id,partition_id,ext_size order by partition_id,obj_id,index_id,fileid --删除临时表 drop table #ExtentInfo END GO
相关推荐
碎片整理后重建索引 恢复完整备份 删减事务日志 人工通读错误日志 利用动态管理视图提高SQL Server索引效率 就如同数据库DBA了解的一样,合适的索引能够提高查询性能和应用程序可测量性。但是每个附加...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
9. Oracle数据库碎片整理 10.ORACLE性能调整1 11.ORACLE性能调整2 12.Oracle专家调优秘密 13.PL_SQL单行函数和组函数详解 14.PL-SQL 15.PLSQL异常处理初步 16.SQL语句性能调整原则 17.创建和使用...
检索全局sqlserver表,检查碎片率大于30并且数据量大于2500也的表,进行索引重建,从而提高查询效率,使用时直接放在BIN-DEBUG文件夹下即可,并自动检索配置文件
130. 如何用Windows Script Host进行磁盘碎片整理 475 131. 如何用Windows Script Host打开管理工具中的选项 476 132. 如何获得系统中的逻辑驱动器 476 133. 如何获得系统中的未使用的驱动器盘符 478 134. 如何...
CHK:由Windows磁盘碎片整理器或磁盘扫描保存的文件碎片 CHM:编译过的HTML文件 CHP:Ventura Publisher章节文件 CHR:字符集(字体文件) CHT:ChartViem文件;Harvard Graphics矢量文件 CIF:Adaptec CD 创建...
JOB使用方法.doc ORACLE RMAN快速入门指南.doc ORACLE 常见问题.doc ORACLE 常用命令.doc ORACLE 开发管理经验.doc ORACLE 回滚段管理.doc ORACLE 碎片整理.doc Oracle WebServer中文手册 .chm v2.0.pdfPrentice...
说明: 允许数据库管理员指定 PL/SQL 文件 I/O 许可的目录。使用多个 UTL_FILE_DIR 参数即可指定多个目录。请注意所有用户均可读取或写入 UTL_FILE_DIR 参数中指定的所有文件。 值范围: 任何有效的目录路径。 ...
20. dfrg.msc-------磁盘碎片整理程序 21. chkdsk.exe-----Chkdsk磁盘检查 22. devmgmt.msc--- 设备管理器 23. regsvr32 /u *.dll----停止dll文件运行 24. drwtsn32------ 系统医生 25. rononce -p----15秒关机...
绝对经典完整的入门教程 由于70MB的文件限制 分为两节下载 搜索“[Visual.Basic.2010.入门经典(第6版)].Thearon.Willi等.扫描版(2/2)”可以下载另外一半 或者直接打开链接...