问题描述

我最近对我的数据进行了一些存档，并执行了以下操作:

I have recently done some archiving of my data, and performed the following:

我有一个包含超过 3300 万条记录的数据库表，其中许多是重复的.

I had a database table that had over 33 million records, many of which were duplicates.

我备份了表并将唯一数据插入到新表中，然后重命名/交换表名，这实现了我所需要的.

I backed up the table and inserted the unique data into a new table, then renamed/swapped the table names, this achieved what I needed.

然而现在我只剩下两张桌子......

Now however I am left with two tables...

• 表 1(良好/活动表)- 1000 万条记录
• Table1_Backup(备份表)- 3300 万条记录

执行此操作后，我的 SQL mdf/数据文件已上升至 319.7 GB，而我的日志文件已上升至 182 GB.

After doing this my SQL mdf/data file has rose to 319.7 GB, and my Log file has rose to 182 GB.

这占用了我大部分可用的操作系统空间，而我的 D 盘空间不足.

This has taken most of my free OS space and my D drive is now low on space.

我的问题是，一旦我对存档数据感到满意，我将删除 _backup 表，只留下我好的活动表.

My question is, once I am happy with the archive data I am going to delete the _backup table just leaving my good live table.

但据我所知，SQL 不会将任何可用空间归还给操作系统，从 log/mdf 文件中回收该空间的最佳方法是什么，我已经阅读了很多关于缩小 db/log 的文章，但是很多人说这是不好的做法，任何建议都会很棒...

But as I understand it SQL will not give me back any free space to the OS, what is the best way to reclaim that space from the log/mdf files, I have read much about shrinking the db/log but many people saying this is bad practice, any advice would be great...

推荐答案

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:

• 您的数据库为 1TB 或更大
• 您删除了 50% 的数据
• 您有 500GB 以上的空闲空间
• 您永远不需要那个空间，因为您现在正在定期进行删除和存档

完整答案:

您写道，您一直在阅读有关此内容的内容 - 所以我希望您遇到过诸如 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.

这篇关于SQL/Windows 操作系统释放未使用的空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！