SQLServer数据库文件碎片整理

　　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