DBCC SHRINKDATABASE DBCC SHRINKFILE 没有反应 没有效果

时间:2024-10-06 07:33:45

问题描述

========
用户使用INSERT语句插入数据但是SQL Server报告说Primary Filegroup已满无法写入数据。用户检查了sp_spaceused发现还有unused空间

问题范围

========

协助检查这个问题并确保Insert语句可以执行完成。

故障排查

========

1.我们通过执行DBCC SHRINKDATABASE和DBCC SHRINKFILE均没有效果

2.经过确认,客户将该数据库设置了“最大文件上限”为2125MB,而当前数据库文件大小为2124MB,已经不能继续增长

3.通过使用sp_spaceused查询,发现还有reserved和unused的空间

提问:既然我有大量unused的空间,为什么不能插入数据?既然我有大量的unused的空间为什么不能收缩数据库?

答:首先我对于sp_spaceused返回的结果进行解释:

需要注意的是:请把sp_spaceused后面的参数改成表,这样才能解释该问题。如果参数是数据库,只是一个数字总和,不能说明问题。

Unallocated space – 这部分空间已经存在在数据库文件中,但是没有给任何表使用,属于公共空间,以后谁都可以使用它。

Reserved – 为该表分配的总空间大小,是后面三者的和(包括已经使用的和保留在该表中但还没有使用的)

Data – 该表中数据的实际空间大小

Index_size – 该表中索引使用的空间大小

Unused – 保留在该表中还没有使用的空间大小(请注意,这部分空间可以被该表使用,但是不能够被其他表使用)

虽然该表有大量的unused的空间,但是请注意,这部分空间能用用于对于这张表的数据插入,当其他表需要数据插入时,他不能够使用这块空间,由于该数据库设置了文件上限,并且数据文件已经达到了上限,数据就插不进去了。

DBCC SHRINKDATABSE和DBCC SHINKFILE都是以一个区(8个页为一个区)为单位进行数据文件收缩的,所以,如果这个区中有1个页面被使用,即使其他7个页面是空的,也是不能收缩数据库的,只有当这个区的8个页面都没有被使用,这个区才能被收缩。您的数据库就属于这种情况。

4.通过DBCC SHOWCONTIG,我们发现有很多表存在的扫描密度(Scan Density)非常小,这意味着这些表存在大量的数据碎片。

提问:数据碎片是如何出现的,它是必然出现的吗?

答:数据碎片是必然出现的,下面来解释数据碎片出现的原因:

当对于一张表进行过大量的数据插入后,又进行了大量的数据删除,虽然数据确实从表中删除了,但是该表并没有把这些空间释放给别的表,也就是说,这些空间成为unused,供这张表以后使用,当以后需要在此表中插入新数据时,这些空间是可以重用的。然而如果没有在这张表继续插入数据,这些空间又不能释放,就浪费掉了,这就是数据碎片,它的出现是对于OLTP系统是必然的。

我们可以通过重建聚集索引吧所有的数据重新整理一遍,来把这些没有用的空间释放给其他的表。对于没有聚集索引的表,我们建议为它创建一个聚集索引,如果由于一些要求您不希望创建聚集索引,您可以先创建后再删掉这个索引,这样也能起到对于数据重新排列的作用。

解决办法

========

1.通过以下语句,来查询那些表存在的索引碎片较为严重

use user_database

go

if exists(select name from sysobjects where NAME ='extentinfo' and type='U')

drop table extentinfo

go

create table extentinfo

( [file_id] smallint,

page_id 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) )

go

if exists(select name from sysobjects where NAME ='import_extentinfo' and type='P')

drop procedure import_extentinfo

go

create procedure import_extentinfo

as dbcc extentinfo('user_database')

go

insert extentinfo

exec import_extentinfo

go

select name as table_name,

[file_id],obj_id, index_id, partition_id, ext_size,

'actual page count'=sum(pg_alloc),

'actual extent count'=count(*),

'expected extent count'=ceiling(sum(pg_alloc)*1.0/ext_size),

'expected extents / actual extents' = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)

from extentinfo inner join sysobjects

on obj_id=id

group by [file_id],obj_id, index_id,partition_id, ext_size ,name

having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0

order by partition_id, obj_id, index_id, [file_id]

2.对于上一步的结果中的表,分别进行创建聚集索引的操作

CREATE CLUSTERED INDEX ix_indexname ON tablename (primary_key) WITH FILLFACTOR=90

FILLFACTOR填充因子表明当创建聚集索引时预留多少空间。但在以后的数据插入、删除和更新操作中,并不维护FILLFACTOR的值,也就是说,虽然创建了聚集索引,但还是会出现索引碎片。

3.如果您想在以后的工作中规避同样的问题,需要定期的使用上述脚本或DBCC SHOWCONTIG对表进行检查,并通过DBCC DBREDINEX来整理碎片。