問題描述
我最近對我的數據進行了一些存檔，并執行了以下操作:
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 操作系統釋放未使用的空間的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！