SQL Server数据库文件未被截断

Question

我有一个大约4GB大小的数据库。我已经复制了该数据库并删除了其中99％的数据，因为我只需要具有模式和基本数据（主要保留静态数据）的数据库。

现在的问题是MDF文件仍然大约有4GB大小。如果我读取表的大小（例如使用这个），它们总共加起来少于20MB。日志文件已经被收缩，但我运行的脚本都没有收缩DB文件。

注意：通常我不会这样做，但这次我需要缩小数据库（我知道这不是推荐的做法）

编辑：+有用信息

命令：

exec sp_spaceused

输出：

database_name       database_size   unallocated_space
AccudemiaEmptyDb    3648.38 MB      4.21 MB

命令：

select object_name(id) as objname, SUM(dpages*8) as dpages, COUNT(*) as cnt
from sysindexes
group by id
order by dpages desc

输出：

object_name(id)            sum(dpages*8)    count(*)
sysdercv                   675328           1
sysxmitqueue               359776           1
sysdesend                  72216            1
sysconvgroup               47704            1
sysobjvalues               4760             5
sec_OperationAccessRule    3472             5
sec_PageAccessRule         2232             5
syscolpars                 656              11
AuditObjects               624              2
sysmultiobjrefs            408              5
HelpPage                   376              8
sysschobjs                 352              9
syssoftobjrefs             328              7
sysidxstats                272              10
sysrscols                  200              1
Translation                160              3
sysallocunits              128              3
sysiscols                  128              8
syssingleobjrefs           96               5
sysrowsets                 80               4

- Diego Jancic

---

我在互联网上找到的所有解决方案包括：DBCC SHRINKDATABASE，更改为RECOVERY SIMPLE然后运行DBCC、对所有表进行DBCC DBREINDEX、碎片整理索引和更新统计信息然后再运行DBCC、备份/恢复、分离/附加、DBCC CLEANTABLE等脚本... - Diego Jancic

Answer 1

首次运行

exec sp_spaceused

在数据库中检查可以恢复多少空间。如果发现它显示没有未使用的空间，那么您可能误解了空间分配。

这是我通常缩小我的test1数据库的方法，这是我玩转所有StackOverflow查询的地方。我只将其从3GB减少到8MB。

use test1;
exec sp_spaceused;
checkpoint;
alter database test1 set recovery simple;
alter database test1 set recovery full;
dbcc shrinkfile(1,1);
dbcc shrinkfile(2,1);

就我所知，以下是我用来检查每个表的分配大小的方法。也许你之前的检查是错误的？这里包括索引。

select object_name(id), SUM(dpages*8), COUNT(*)
from sysindexes
group by id

编辑 - 基于表占用空间的问题编辑到问题中

马丁的评论移至答案：涉及的表是 Service Broker 对话。 http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06 该链接有一个解决方法。

还有一种替代方法; 使用已经削减过的数据库

1. 生成脚本 - 所有对象 - 包括所有选项（键、全文、默认值等）
2. 包括脚本数据选项
3. 创建一个新的数据库并从脚本填充它

（根据记忆，SSSB 队列不包括在生成数据脚本中）

- RichardTheKiwi

---

好的。它说数据库大小为3.6GB，未分配空间为8MB --- 我该如何找到空间使用情况？原问题中提到的脚本显示表的大小仅约为20MB。 - Diego Jancic

@Diego，你有检查过这个答案中的最后一个查询吗？ - RichardTheKiwi

好的，很酷！前5行是sysdercv、sysxmitqueue、sysdesend、sysconvgroup和sysobjvalues。这是什么意思？我该如何从中清空空间？谢谢！ - Diego Jancic

@Diego - 请编辑问题并附上(1) sp_spaceused 的输出结果以及(2) 在此答案中的最后一个查询。 - RichardTheKiwi

看起来是一个服务代理问题，Remus Rusanu在这里处理过。 (http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06) - Martin Smith

做得好，Martin。 很棒的团队合作 :) 为你的答案加1（无法为您的评论加1） - RichardTheKiwi

Answer 2

编辑：看起来空间仍然被某个地方占用。你可以尝试这个查询（基于sp_spaceused）吗？

select OBJECT_NAME(p.object_id),
 reservedpages = sum(a.total_pages),
    usedpages = sum(a.used_pages),
    pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
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
GROUP BY p.object_id
with rollup

- Martin Smith

---

请检查下面的“不是答案”评论：https://dev59.com/WlTTa4cB1Zd3GeqPv9bX#5173528 - RichardTheKiwi

@Richard - 是的，我刚在 Profiler 中查看了。该查询返回的值与从 DBCC FILEHEADER ('AdventureWorks2008', 1) 返回的 MinSize 不同，因此仍然不确定哪个是 DBCC SHRINKDATABASE 认为的最小大小。 - Martin Smith

好的。拥有更多保留空间的对象是“NULL”:(，之后是sysxmitqueue、sysdercv、sysdesend等等… - Diego Jancic

@Diego - 大量的NULL行只是因为我添加了一个rollup以获取总计。 - Martin Smith

Answer 3

谢谢大家，尤其是Richard提供的所有信息！

为了解决这个问题，我不得不删除并重新创建我的服务：

DROP SERVICE [//Audit/DataWriter] 
GO

CREATE SERVICE [//Audit/DataWriter] 
    AUTHORIZATION dbo 
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])

我曾经这样做过，结果数据库达到了5GB！但是这次我在问题中加入了第二个查询，显示sysxmitqueue作为第一个结果。通过在互联网上进一步挖掘，我成功地清除了大表：

ALTER DATABASE [your_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [your_database] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [your_database] SET MULTI_USER
GO

然后，运行DBCC SHRINKFILE就可以了!! =) 现在只有40MB了

谢谢大家！

- Diego Jancic

Answer 4

您可以使用DBCC命令来收缩数据库。

这里是DBCC SHRINKDATABASE和DBCC SHRINKFILE的参考资料。

- bobs

Answer 5

如果你复制数据库会怎样呢？右键点击数据库，选择任务，然后选择复制数据库。这只是一个可以尝试的想法。

- Decker97