如何在SQL Server 2012中减少mdf文件大小？ [英] How to reduce the mdf file size in SQL server 2012 ?

问题描述

我可以在给定查询的帮助下减少.ldf文件大小，但我不能减小.mdf文件中的大小



ALTER DATABASE [testdb] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE（testdb_log，100）

ALTER DATABASE [testdb]完全恢复使用NO_WAIT

GO



我尝试了什么：



i只截断一些不需要的表

i can reduce .ldf file size with help of given query but i can't reduce size in .mdf file

ALTER DATABASE [testdb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(testdb_log, 100)
ALTER DATABASE [testdb] SET RECOVERY FULL WITH NO_WAIT
GO

What I have tried:

i just truncate some unwanted tables

推荐答案

首先 - 不要在没有充分理由的情况下收缩数据库（或其文件）...如果让它以最佳方式分配文件，SQL将表现得更好......

如果尽管如此，你必须缩小，而不是缩小正在使用的数据库。收缩是一项非常昂贵的操作，而其他用户登录时这样做会影响他们的工作。

First of all - do NOT shrink a database (or its files) without good reason...SQL will perform better if you let it allocate the files in the way finds it best...
If despite this you have to shrink, than do not shrink a database in use. Shrink is a very IO expensive operation and doing it while other users are logged in will disturb their work.

DBCC SHRINKDATABASE (db_name-to-shrink)

您可以尝试 DBCC SHRINKDATABASE [ ^ ]但是一旦更新开始发生，它将不会长时间保持缩水......如果你的尺寸有任何显着减少的话。请记住，MDF文件存储您的数据 - 如果有大量数据，则无法腾出太多空间。

You could try DBCC SHRINKDATABASE[^] but it won't stay shrunk for long once updates start happening...if you get any significant size reduction at all. Remember that the MDF file stores your data - and if there is a lot of data, you can't free up much space.

这篇关于如何在SQL Server 2012中减少mdf文件大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！