========
用户使用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 _disibledevent=> FILLFACTOR填充因子表明当创建聚集索引时预留多少空间。但在以后的数据插入、删除和更新操作中，并不维护FILLFACTOR的值，也就是说，虽然创建了聚集索引，但还是会出现索引碎片。
3.如果您想在以后的工作中规避同样的问题，需要定期的使用上述脚本或DBCC SHOWCONTIG对表进行检查，并通过DBCC DBREDINEX来整理碎片。
最新评论