SQL/Windows OS释放未使用的空间 [英] SQL / Windows OS releasing unused space
问题描述
我最近对数据进行了一些存档，并执行了以下操作:
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(良好/活动表)-一千万条记录
- Table1_Backup(备份表)-3,300万条记录
执行完此操作后，我的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.
这已占用了我大部分的可用OS空间，而我的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.
完整答案:
您写过您一直在读的书-因此，我希望您遇到了 Brent Ozar 的使用收缩数据库有什么不好? DBCC SHRINKDATABASE?:
You wrote you've been reading about this - So I hope you've encountered posts like Brent Ozar's What’s So Bad About Shrinking Databases with 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.
迈克·沃尔什的<一个href ="https://straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/" rel ="nofollow noreferrer">请不要触摸SQL Server中的收缩数据库按钮！-他的解释相同:
收缩数据库时会发生什么?
收缩数据库时，是在要求SQL Server从数据库文件中删除未使用的空间.SQL使用的过程可能很丑陋，并导致索引碎片.从长远来看，这种碎片会影响性能.您已经释放了空间，并让操作系统根据需要执行所需的操作，因此至少可以满足您的要求.如果您的数据库正在增长，这意味着数据库将再次增长.根据您的自动增长设置，这种增长可能会超过必要的增长，最终您将再次收缩.充其量只是多余的工作(缩小/缩小)，并且可以正确处理产生的文件碎片.更糟糕的是，这会导致索引碎片，文件碎片，并可能在收缩期间导致性能问题.
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 对
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.
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 OS释放未使用的空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
