問題描述

我最近對(duì)我的數(shù)據(jù)進(jìn)行了一些存檔，并執(zhí)行了以下操作:

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

我有一個(gè)包含超過 3300 萬條記錄的數(shù)據(jù)庫表，其中許多是重復(fù)的.

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

我備份了表并將唯一數(shù)據(jù)插入到新表中，然后重命名/交換表名，這實(shí)現(xiàn)了我所需要的.

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.

然而現(xiàn)在我只剩下兩張桌子......

Now however I am left with two tables...

• 表 1(良好/活動(dòng)表)- 1000 萬條記錄
• Table1_Backup(備份表)- 3300 萬條記錄

執(zhí)行此操作后，我的 SQL mdf/數(shù)據(jù)文件已上升至 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.

這占用了我大部分可用的操作系統(tǒng)空間，而我的 D 盤空間不足.

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

我的問題是，一旦我對(duì)存檔數(shù)據(jù)感到滿意，我將刪除 _backup 表，只留下我好的活動(dòng)表.

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.

但據(jù)我所知，SQL 不會(huì)將任何可用空間歸還給操作系統(tǒng)，從 log/mdf 文件中回收該空間的最佳方法是什么，我已經(jīng)閱讀了很多關(guān)于縮小 db/log 的文章，但是很多人說這是不好的做法，任何建議都會(huì)很棒...

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; 不要縮小數(shù)據(jù)庫.曾經(jīng).

TL;DR; Do not shrink your database. Ever.

但如果你真的需要縮小它呢?

根據(jù) SQL Server 專家 Brant Ozar 鏈接的文章 - 在某些情況下，縮小數(shù)據(jù)庫是一個(gè)合理的選擇:

According to the article linked about by SQL Server Expert Brant Ozar - there are circumstances where shrinking your database is a legitimate option:

• 您的數(shù)據(jù)庫為 1TB 或更大
• 您刪除了 50% 的數(shù)據(jù)
• 您有 500GB 以上的空閑空間
• 您永遠(yuǎn)不需要那個(gè)空間，因?yàn)槟F(xiàn)在正在定期進(jìn)行刪除和存檔

完整答案:

您寫道，您一直在閱讀有關(guān)此內(nèi)容的內(nèi)容 - 所以我希望您遇到過諸如 Brent Ozar 的 收縮數(shù)據(jù)庫有什么不好DBCC SHRINKDATABASE?:

你有很高的碎片，所以你重建你的索引.

You have high fragmentation, so you rebuild your indexes.

這會(huì)留下很多空白空間，因此您會(huì)縮小數(shù)據(jù)庫.

Which leaves a lot of empty space around, so you shrink your database.

這會(huì)導(dǎo)致高度碎片化，因此您重建索引，這會(huì)使數(shù)據(jù)庫立即增長(zhǎng)并再次留下空白空間，并且循環(huán)不斷自我延續(xù).

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 中的收縮數(shù)據(jù)庫按鈕！ - 他解釋相同的地方:

Mike Walsh's Don’t Touch that Shrink Database Button In SQL Server! - where he explains the same:

縮小數(shù)據(jù)庫會(huì)發(fā)生什么?

當(dāng)您縮小數(shù)據(jù)庫時(shí)，您是在要求 SQL Server 從數(shù)據(jù)庫文件中刪除未使用的空間.SQL 使用的過程可能很丑陋并導(dǎo)致索引碎片.從長(zhǎng)遠(yuǎn)來看，這種碎片會(huì)影響性能.你已經(jīng)釋放了那個(gè)空間，并讓 O/S 用它做它需要做的事情，所以你至少得到了你所要求的.如果您有一個(gè)不斷增長(zhǎng)的數(shù)據(jù)庫，這意味著該數(shù)據(jù)庫將再次增長(zhǎng).根據(jù)您的自動(dòng)增長(zhǎng)設(shè)置，這種增長(zhǎng)可能會(huì)超出必要，最終會(huì)再次縮小.充其量這只是額外的工作(收縮增長(zhǎng)/收縮增長(zhǎng))并且由此產(chǎn)生的文件碎片處理得很好.更糟糕的是，這會(huì)導(dǎo)致索引碎片、文件碎片，并可能在收縮期間導(dǎo)致性能問題.

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 對(duì) dba.StackExchange.com 上的 SHRINKFILE 最佳實(shí)踐和經(jīng)驗(yàn) - 他基本上是在說你可以隨意忽略聰明、有經(jīng)驗(yàn)的人的好建議，并假設(shè)您的情況有所不同 - 但風(fēng)險(xiǎn)自負(fù).這是他的結(jié)案陳詞:

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，然后強(qiáng)制其增大以容納新數(shù)據(jù)的操作成本要高得多.這就像洗一條已經(jīng)干凈的毛巾，你將用它來擦掉一團(tuán)糟..

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..

結(jié)論 - 你真的，真的應(yīng)該注意專家在寫什么.需要說明的是:我并不認(rèn)為自己是該主題的專家.
我從開發(fā)人員方面牢牢掌握了 T-SQL，但我在 DBA 方面的經(jīng)驗(yàn)很少——我可以一方面計(jì)算我必須編寫維護(hù)計(jì)劃、數(shù)據(jù)庫遷移或處理任何問題的次數(shù).DBA 會(huì)做的系統(tǒng)管理工作.
然而，我提到的所有這些人都是領(lǐng)先的 DBA:Brent Ozar 是 MCM(微軟認(rèn)證大師)，Mike Walsh 是 9 次 MVP(自 2011 年以來)，Aaron Bertrand 是 22 次 MVP(自 1997 年以來)——這些伙計(jì)們真的知道他們?cè)趯懯裁?
我會(huì)在一周中的任何一天和周日兩次從他們中的任何一個(gè)那里獲得免費(fèi)建議.

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.

更新 - 關(guān)于日志文件:

縮小日志文件有點(diǎn)不同 - 定期執(zhí)行是不好的做法.
日志文件大小基本上取決于您的備份策略和選擇的恢復(fù)模式.

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 對(duì)同一帖子的完整答案.

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.

這篇關(guān)于SQL/Windows 操作系統(tǒng)釋放未使用的空間的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！