数据库规模巨大
我有以下问题。 我们有一个数据库，在数据库中存储二进制文件。 我们知道数据库的大小可能很大，因此我们从数据库中删除了所有二进制文件并对其使用了任务“收缩”。 这样我们希望数据库会小很多。 结果如下：
删除之前大小为：20 GB 删除后大小为：25 GB（包括日志文件） 收缩后大小为：13 GB
现在我不知道这 13 GB 是从哪里来的，数据库中最大的表是一个日志表，它是 1.3 GB，所有其余的加起来不需要 200 MB ...
可能是那里日志文件中是否仍有一些收缩任务无法删除的数据？ 这个问题有解决办法吗？
I have the following problem.
We have a database that stores binaries in the database.
We know the size of the database can be big so we removed all the binaries from the database and used the task "shrink" on it.
This way we hoped that the database would be much smaller.
These are the results:
before removal size was: 20 gigabyte
after removal size was: 25 gigabyte (incl log file)
after shrink size was: 13 gigabyte
now I have no idea where that 13 gig is from, the largest table in the database is a logtable and it's 1.3 gig all the rest combined doesn't take 200 mb ...
could it be that there is still some data in the logfile that the shrink task cannot remove?
is there a solution for this problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
一种可能性是您从中删除数据的表是堆（意味着没有聚集索引），并且当您从堆中删除时，为表分配的空间不一定会被释放。 查看来自 MS 的这篇文章：http://support.microsoft.com/kb/913399
One possibility is that the table you've deleted the data from is a Heap (means there is no clustered index), and when you delete from a heap the space allocated for the table won't necessarily be freed. Check this article from MS: http://support.microsoft.com/kb/913399
如果您的恢复模式是“完整”并且您没有备份然后缩小事务日志，那么它仍然可能很大。
根据您的情况，收缩事务日志的最简单方法之一是将恢复模式设置为简单，然后收缩事务日志文件，然后将恢复模式设置回完整。但是，如果这是一个生产系统，您可以在其中使用可能需要某个时间点恢复，那么您应该执行事务日志的备份。
If your recovery model is "Full" and you haven't backed up and then shrunk your transaction log then it could still remain large.
Depending on your situation, one of th easiest ways of shrink the transaction log is to set your Recovery Model to simple, then shrink the transaction log files and then set recovery model back to full.. However, if this is a production system where you may need a point in time recovery then you should perform backups of your transaction log instead.
要获取有关空间使用情况的更多信息，您可以尝试：
To get more information about space usage you can try:
在您采纳 Robin Day 的建议并缩小日志后，请记住设置事务日志备份（不仅仅是数据库备份，因为它们不会使日志保持较小，正如您所发现的），否则您的日志将再次变大。 我们的交易日志每 15 分钟备份一次。 您的计划可能需要增加或减少频率，具体取决于发生故障时您可以承受丢失的数据量。 至少我会每天进行一次日志备份，以保持日志的合理大小。
Please remember to set up transaction log backups (not just database backups as they will not keep the log small as you have found out) after you take Robin Day's advice and shrink the log or your log will just grow large again. Our transaction logs are backed up every 15 minutes. Your schedule may need to be more or less frequent depending on how much data you can afford to lose if you have a failure. At a minimum I'd do a daily log backup just to keep the log a reasonable size.