sql - 删除大量数据后测量数据库可用空间

Question

我们将数据序列化为 xml，将其放入文件，然后从数据库中删除该数据。我们称此过程为“归档”。问题是我们必须在归档之前和归档之后记录数据库可用空间大小。我们使用存储过程确定可用空间

ALTER PROCEDURE [dbo].[SP_USED_SPACE]
AS
BEGIN
  declare @reservedpages bigint, @dbsize bigint

  select @reservedpages = sum(a.total_pages)
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
        left join sys.internal_tables it on p.object_id = it.object_id

  select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  from dbo.sysfiles

  SELECT (convert (dec (15,2), @dbsize)) * 8192 / 1048576 as database_size,
     (case when @dbsize >= @reservedpages then
            (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
            * 8192 / 1048576 else 0 end) as free_space

END

但在归档之后，该过程返回的值与归档前几乎或完全相同。好吧，我可以保证删除的数据相当可观，比如几个表中的数百行。

Screenshot of what is returned before and after archiving

Screenshot of what is returned by the same procedure just a bit after the first screenshot was made (that is what I expect)

我已经试过了:

1. DBCC 更新使用
2. 发送一个简单的查询，不使用可以缓存的存储过程
3. 在归档后调用过程之前，在 C# 中添加 Thread.Sleep(5000)。 (这适用于我的本地计算机，但不适用于服务器。增加括号之间的值并不能保证它在任何情况下都适用)。
4. 删除数据后执行数据库缩减
5. 使用不同的方法来确定可用空间大小，即使用 FILEPROPERTY 或 sp_spaceused

出现这种行为的原因是什么？我该如何解决？

Answer 1

当表没有聚簇索引时，DELETE 语句不会释放页面。因此该空间不能被其他对象重用。

您可以使用一些选项来取消分配页面:

1. 在 DELETE 语句中使用 TABLOCK 提示
2. 如果可能，在表上创建聚集索引在这里您可以在没有 TABLOCK 提示的情况下进行删除

在线看MSDN，主题锁定行为 https://msdn.microsoft.com/en-US/library/ms189835(v=sql.120).aspx