dbcc收缩数据库_使用DBCC SHRINKFILE收缩数据库

dbcc收缩数据库

介绍 (Introduction)

SQL Server is pretty good at managing disk space. As long as we do our part to set up appropriate storage types and place files and filegroups properly and set reasonable AUTOGROW settings, it’s almost a set-it-and-forget-it operation. Mind you, I said, “almost!” Sometimes, things do go BUMP! in the night and we need to act. Here’s what happened to me not too long ago:

SQL Server非常擅长管理磁盘空间。 只要我们尽力设置适当的存储类型并正确放置文件和文件组并设置合理的AUTOGROW设置，这几乎就是“设置后忘记”操作。 我说，“快点！” 有时候，事情确实会变得很糟糕！ 在晚上，我们需要采取行动。 这是不久前发生在我身上的事情：

I look after a fairly large database (about 10TB at the moment, and growing). Using table partitioning and switching and archiving, we can manage in the space we have, assuming other things don’t change. One day though, I noticed our free space was reaching a critical level: just 5%. Upon investigation, I discovered that the Windows drive my database was on was shared with another database that didn’t really belong there and it was growing with no end in sight. That got my attention!

我照顾一个相当大的数据库（目前大约10TB，并且还在不断增长）。 假设其他情况不变，使用表分区以及切换和归档功能，我们可以在现有的空间中进行管理。 但是有一天，我注意到我们的可用空间达到了一个临界水平：只有5％。 经过调查，我发现数据库所在的Windows驱动器与另一个并不真正属于该数据库的数据库共享，并且该数据库在不断发展，而且没有尽头。 那引起了我的注意！

应用压缩 (Applying compression)

The first thing I did to this rogue database was compress it. I have another SQLShack article, How to use SQL Server Data Compression to Save Space, that discusses compression, if you haven’t used it before. Simply put, SQL Server can achieve 40%, 50%, 60% and sometimes even more compression, depending on the data. After compressing all the tables and indexes in this particular database, I had a 1 TB database with more than 60% free space. But in case you’re thinking, “Problem solved!” that is only half the story. You see, the database had stopped growing but was still occupying that 1 TB on the file system. It’s just that now that file had tons of free space in it. I really wanted to reclaim that free space to give my main database some more headroom.

我对这个恶意数据库所做的第一件事就是压缩它。 我还有另一篇SQLShack文章，《 如何使用SQL Server数据压缩来节省空间》 ，其中讨论了压缩（如果您以前没有使用过的话）。 简而言之，根据数据的不同，SQL Server可以实现40％，50％，60％的压缩，有时甚至更高。 压缩了该特定数据库中的所有表和索引之后，我有了一个1 TB数据库࿰