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