分享程序员开发的那些事...
更新时间：2023-02-17 14:34:13
TL;DR; 不要缩小数据库.曾经.
TL;DR; Do not shrink your database. Ever.
根据 SQL Server 专家 Brant Ozar 链接的文章 - 在某些情况下，缩小数据库是一个合理的选择:
According to the article linked about by SQL Server Expert Brant Ozar - there are circumstances where shrinking your database is a legitimate option:
您写道，您一直在阅读有关此内容的内容 - 所以我希望您遇到过诸如 Brent Ozar之类的帖子> 的 收缩数据库有什么不好DBCC SHRINKDATABASE?:
你有很高的碎片，所以你重建你的索引.
You have high fragmentation, so you rebuild your indexes.
这会留下很多空白空间，因此您会缩小数据库.
Which leaves a lot of empty space around, so you shrink your database.
这会导致高度碎片化，因此您重建索引，这会使数据库立即增长并再次留下空白空间，并且循环不断自我延续.
Which causes high fragmentation, so you rebuild your indexes, which grows the databases right back out and leaves empty space again, and the cycle keeps perpetuating itself.
Mike Walsh的不要触摸 SQL Server 中的收缩数据库按钮！ - 他解释相同的地方:
Mike Walsh's Don’t Touch that Shrink Database Button In SQL Server! - where he explains the same:
缩小数据库会发生什么?
当您缩小数据库时，您是在要求 SQL Server 从数据库文件中删除未使用的空间.SQL 使用的过程可能很丑陋并导致索引碎片.从长远来看，这种碎片会影响性能.你已经释放了那个空间，并让 O/S 用它做它需要做的事情，所以你至少得到了你所要求的.如果您有一个不断增长的数据库，这意味着该数据库将再次增长.根据您的自动增长设置，这种增长可能会超出必要，最终会再次缩小.充其量这只是额外的工作(收缩增长/收缩增长)并且由此产生的文件碎片处理得很好.更糟糕的是，这会导致索引碎片、文件碎片，并可能在收缩期间导致性能问题.
When you shrink a database, you are asking SQL Server to remove the unused space from your database’s files.The process SQL uses can be ugly and result in Index fragmentation. This fragmentation affects performance in the long run. You’ve freed that space and are letting the O/S do what it needs to with it, so you got what you asked for at least. If you have a growing database, this means that database will grow again. Depending on your autogrowth settings, this growth will probably be more than necessary and you will end up shrinking again. At best this is just extra work (shrink grow/shrink grow) and the resulting file fragmentation is handled alright. At worse this is causing index fragmentation, file fragmentation, and potentially causing performance problems during the shrink.
和 Aaron Bertrand 对 dba.StackExchange.com 上的 SHRINKFILE ***实践和经验 - 他基本上是在说你可以随意忽略聪明、有经验的人的好建议，并假设您的情况有所不同 - 但风险自负.这是他的结案陈词:
and Aaron Bertrand's answer to SHRINKFILE best practices and experience on dba.StackExchange.com - where he is basically saying that you are free to ignore the good advice from smart, experienced people and assume that your case is different - but at your own risk. This is his closing argument:
将文件缩小到 4GB，然后强制其增大以容纳新数据的操作成本要高得多.这就像洗一条已经干净的毛巾，你将用它来擦掉一团糟..
It will be a much more expensive operation to shrink the file to 4GB, then force it to grow to accommodate the new data. This is like washing an already clean towel that you're about to use to wipe up a mess..
结论 - 你真的，真的应该注意专家在写什么.需要说明的是:我并不认为自己是该主题的专家.
我从开发人员方面牢牢掌握了 T-SQL，但我在 DBA 方面的经验很少——我可以一方面计算我必须编写维护计划、数据库迁移或处理任何问题的次数.DBA 会做的系统管理工作.
然而，我提到的所有这些人都是领先的 DBA:Brent Ozar 是 MCM(微软认证大师)，Mike Walsh 是 9 次 MVP(自 2011 年以来)，Aaron Bertrand 是 22 次 MVP(自 1997 年以来)——这些伙计们真的知道他们在写什么.
我会在一周中的任何一天和周日两次从他们中的任何一个那里获得免费建议.
In conclusion - you really, really should pay attention to what experts are writing. Just to be clear: I'm not considering myself an expert on the subject.
I have a firm grasp of T-SQL from the developer side but I have very little experience from the DBA side - I can count on one hand the number of times I had to write stuff like maintenance plans, database migrations or handle any of the system administration stuff a DBA would.
However, all these guys I've mentioned are leading DBAs: Brent Ozar is a MCM (Microsoft Certified Master), Mike Walsh is a 9 times MVP (since 2011), and Aaron Bertrand is a 22 times MVP (since 1997) - These guys really know what they are writing about.
I would take a free advice from either of them any day of the week and twice on Sunday.
更新 - 关于日志文件:
缩小日志文件有点不同 - 定期执行是不好的做法.
日志文件大小基本上取决于您的备份策略和选择的恢复模式.
Shrinking log files is somewhat of a different story - doing it on a regular basis is bad practice.
A log file size is basically derived from your backup strategy and selected recovery model.
推荐阅读:Mike Walsh 的 在 dba.stackexchange 上自我回答的帖子 - 如果您愿意，我建议您阅读他的完整答案以及 Aaron Bertrand 对同一帖子的完整答案.
Recommended read: Mike Walsh's self answered post over on dba.stackexchange - If you're up to it, I would advise reading both his full answer as well as Aaron Bertrand's full answer to the same post.