如何缩小SQL服务器数据库?

I have a Database nearly 1.9Gb Database in size, and MSDE2000 does not allow DBs that exceed 2.0Gb

我的数据库大小接近1.9Gb, MSDE2000不允许DBs超过2.0Gb

I need to shrink this DB (and many others like this at various client locations).

我需要缩小这个DB(以及其他类似的在不同客户端位置的数据库)。

I have found and deleted many 100's of 1000's of records which are considered unneeded: these records account for a large percentage of some of the main (largest) tables in the Database. Therefore it's reasonable to assume much space should now be retrievable.

我已经找到并删除了1000个被认为不需要的记录中的100个:这些记录占数据库中一些主要(最大)表的很大比例。因此，我们有理由假设现在有很多空间是可回收的。

So now I need to shrink the DB to account for the missing records.

所以现在我需要缩小DB来解释丢失的记录。

• I execute DBCC ShrinkDatabase('MyDB')...... No effect.
• 我执行DBCC ShrinkDatabase(“MyDB”)……没有效果。
• I have tried the various shrink facilities provided in MSSMS.... Still no effect.
• 我试过各种收缩设施中提供MSSMS ....仍然没有效果。
• I have backed up the database and restored it... Still no effect.
• 我备份了数据库并恢复了它……仍然没有效果。

Still 1.9Gb

还是1.9 gb

Why?

为什么?

Whatever procedure I eventually find needs to be replayable on a client machine with access to nothing other than OSql or similar.

我最终找到的任何过程都需要在客户机机器上重新运行，只能访问OSql或类似的东西。

15 个解决方案

#1

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

#2

This may seem bizarre, but it's worked for me and I have written a C# program to automate this.

这看起来很奇怪，但对我来说很有用，我已经编写了一个c#程序来实现自动化。

Step 1: Truncate the transaction log (Back up only the transaction log, turning on the option to remove inactive transactions)

步骤1:截断事务日志(只备份事务日志，打开选项删除不活动的事务)

Step 2: Run a database shrink, moving all the pages to the start of the files

步骤2:运行数据库收缩，将所有页面移动到文件的开头

Step 3: Truncate the transaction log again, as step 2 adds log entries

步骤3:再次截断事务日志，因为步骤2添加日志条目

Step 4: Run a database shrink again.

步骤4:再次运行数据库收缩。

My stripped down code, which uses the SQL DMO library, is as follows:

我的精简代码，使用了SQL DMO库，如下所示:

SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_NoTruncate);
SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);

#3

This is an old question but I just happened upon it.

这是一个老问题，但我碰巧遇到了。

The really short and a correct answer is already given and has the most votes. That is how you shrink a transaction log, and that was probably the OPs problem. And when the transaction log has grown out of control, it often needs to be shrunk back, but care should be taken to prevent future situations of a log growing out of control. This question on dba.se explains that. Basically - Don't let it get that large in the first place through proper recovery model, transaction log maintenance, transaction management, etc.

真正简短而正确的答案已经给出，而且得票最多。这就是如何缩小事务日志的方法，这可能是操作系统的问题。当事务日志变得失控时，通常需要缩小它，但是应该注意防止日志将来失控。这个问题在dba。se解释说。基本上——首先不要让它通过适当的恢复模型、事务日志维护、事务管理等方式变得这么大。

But the bigger question in my mind when reading this question about shrinking the data file (or even the log file) is why? and what bad things happen when you try? It appears as though shrink operations were done. Now in this case it makes sense in a sense - because MSDE/Express editions are capped at max DB size. But the right answer may be to look at the right version for your needs. And if you stumble upon this question looking to shrink your production database and this isn't the reason why, you should ask yourself the why? question.

但是当我读到关于缩小数据文件(甚至是日志文件)的问题时，我脑海中更大的问题是为什么呢?当你尝试的时候会发生什么坏事?看起来收缩操作已经完成了。在这种情况下，它在某种意义上是有意义的——因为MSDE/Express版本的上限是最大DB大小。但是正确的答案可能是看你需要的正确版本。如果你偶然发现这个问题想要缩小你的生产数据库这不是原因，你应该问自己为什么?的问题。

I don't want someone searching the web for "how to shrink a database" coming across this and thinking it is a cool or acceptable thing to do.

我不希望有人在网上搜索“如何缩小数据库”，并认为这是一个很酷或可以接受的事情。

Shrinking Data Files is a special task that should be reserved for special occasions. Consider that when you shrink a database, you are effectively fragmenting your indexes. Consider that when you shrink a database you are taking away the free space that a database may someday grow right back into - effectively wasting your time and incurring the performance hit of a shrink operation only to see the DB grow again.

收缩数据文件是一项特殊的任务，应该为特殊场合保留。考虑到当您收缩数据库时，您正在有效地分割索引。考虑一下，当您收缩一个数据库时，您正在剥夺一个数据库将来有一天可能重新增长的*空间——这实际上是在浪费您的时间，并导致收缩操作的性能下降，结果却看到DB再次增长。

I wrote about this concept in several blog posts about shrinking databases. This one called "Don't touch that shrink button" comes to mind first. I talk about these concepts outlined here - but also the concept of "Right-Sizing" your database. It is far better to decide what your database size needs to be, plan for future growth and allocate it to that amount. With Instant File Initialization available in SQL Server 2005 and beyond for data files, the cost of growths is lower - but I still prefer to have a proper initial application - and I'm far less scared of white space in a database than I am of shrinking in general with no thought first. :)

我在几篇关于收缩数据库的博文中提到了这个概念。首先想到的是“别碰那个收缩按钮”。我讨论了这里概述的这些概念，但也谈到了“适当规模”数据库的概念。最好是决定数据库的大小，规划未来的增长，并将其分配到这个数量。即时文件初始化可以在SQL Server 2005年及以后的数据文件,增生的成本较低,但我还是更喜欢有一个适当的初始应用程序——我更害怕空白萎缩的一般比我在数据库中没有思想。:)

#4

DBCC SHRINKDATABASE works for me, but this is its full syntax:

DBCC SHRINKDATABASE为我工作，但这是它的完整语法:

DBCC SHRINKDATABASE ( database_name, [target_percent], [truncate] )

where target_percent is the desired percentage of free space left in the database file after the database has been shrunk.

其中target_percent是数据库压缩后，在数据库文件中剩余的空闲空间的期望百分比。

And truncate parameter can be:

截形参数为:

NOTRUNCATE

Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

使释放的文件空间保留在数据库文件中。如果没有指定，释放的文件空间将被释放到操作系统。

TRUNCATEONLY

Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

使数据文件中的任何未使用的空间被释放到操作系统，并将文件压缩到最后分配的范围，在不移动任何数据的情况下减少文件大小。没有尝试将行重新定位到未分配的页面。当使用TRUNCATEONLY时，将忽略target_percent。

...and yes no_one is right, shrinking datbase is not very good practice becasue for example :

…是的，no_one是对的，收缩数据库不是很好的实践，因为例如:

shrink on data files are excellent ways to introduce significant logical fragmentation, becasue it moves pages from the end of the allocated range of a database file to somewhere at the front of the file...

收缩数据文件是引入重要的逻辑碎片的极好方法，因为它将页面从数据库文件的分配范围的末尾移动到文件的前面……

shrink database can have a lot of consequence on database, server.... think a lot about it before you do it!

收缩数据库可以有很多结果数据库,服务器....在你做这件事之前要好好想想!

on the web there are a lot of blogs and articles about it.

在网上有很多关于它的博客和文章。

#5

Late answer but might be useful useful for someone else

晚回答可能对其他人有用。

If neither DBCC ShrinkDatabase/ShrinkFile or SSMS (Tasks/Shrink/Database) doesn’t help, there are tools from Quest and ApexSQL that can get the job done, and even schedule periodic shrinking if you need it.

如果DBCC ShrinkDatabase/ShrinkFile或SSMS(任务/收缩/数据库)都没有帮助，那么有一些来自Quest和ApexSQL的工具可以完成任务，甚至可以在需要的时候定期收缩。

I’ve used the latter one in free trial to do this some time ago, by following short description at the end of this article:

我曾在一段时间之前使用后一种方法，通过以下简短的描述:

https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files/

https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files/

All you need to do is install ApexSQL Backup, click "Shrink database" button in the main ribbon, select database in the window that will pop-up, and click "Finish".

您只需安装ApexSQL备份，在主功能区中单击“收缩数据库”按钮，在弹出的窗口中选择database，然后单击“Finish”。

#6

You will also need to shrink the individual data files.

您还需要收缩各个数据文件。

It is however not a good idea to shrink the databases. For example see here

然而，缩小数据库的规模并不是个好主意。例如在这里看到

#7

You should use:

你应该使用:

dbcc shrinkdatabase (MyDB)

It will shrink the log file (keep a windows explorer open and see it happening).

它将收缩日志文件(打开一个windows资源管理器，看到它发生)。

#8

Here's another solution: Use the Database Publishing Wizard to export your schema, security and data to sql scripts. You can then take your current DB offline and re-create it with the scripts.

还有一种解决方案:使用数据库发布向导将模式、安全性和数据导出到sql脚本。然后，您可以将当前的DB脱机并使用脚本重新创建它。

Sounds kind of foolish, but there are a couple advantages. First, there's no chance of losing data. Your original db (as long as you don't delete your DB when dropping it!) is safe, the new DB will be roughly as small as it can be, and you'll have two different snapshots of your current database - one ready to roll, one minified - you can choose from to back up.

听起来有点傻，但也有一些好处。首先，不可能丢失数据。原来db(只要你不删除你的db下降的时候!)是安全的,新的数据库将大致尽可能小,你会有两个不同的当前的数据库快照——一个准备辊,一个缩小——你可以选择从备份。

#9

"Therefore it's reasonable to assume much space should now be retrievable."

“因此，我们有理由认为，现在有很多空间是可以收回的。”

Apologies if I misunderstood the question, but are you sure it's the database and not the log files that are using up the space? Check to see what recovery model the database is in. Chances are it's in Full, which means the log file is never truncated. If you don't need a complete record of every transaction, you should be able to change to Simple, which will truncate the logs. You can shrink the database during the process. Assuming things go right, the process looks like:

抱歉，如果我误解了这个问题，但是您确定是数据库而不是使用空间的日志文件吗?检查数据库的恢复模型。很有可能是完整的，这意味着日志文件不会被截断。如果您不需要每个事务的完整记录，那么您应该能够更改为Simple，它将截断日志。您可以在此过程中收缩数据库。假设一切顺利，整个过程看起来是这样的:

1. Backup the database!
2. 备份数据库!
3. Change to Simple Recovery
4. 改变简单的复苏
5. Shrink db (right-click db, choose all tasks > shrink db -> set to 10% free space)
6. 收缩db(右击db，选择所有的任务>缩小db ->设置为10%的*空间)
7. Verify that the space has been reclaimed, if not you might have to do a full backup
8. 验证空间已被回收，如果没有的话，您可能需要做一个完整的备份

If that doesn't work (or you get a message saying "log file is full" when you try to switch recovery modes), try this:

如果这不起作用(或者当您尝试切换恢复模式时，您会得到一个“日志文件已满”的消息)，请尝试以下方法:

1. Backup
2. 备份
3. Kill all connections to the db
4. 关闭到db的所有连接
5. Detach db (right-click > Detach or right-click > All Tasks > Detach)
6. Detach db(右键单击> Detach或右键单击>所有任务> Detach)
7. Delete the log (ldf) file
8. 删除日志(ldf)文件
9. Reattach the db
10. 再植db
11. Change the recovery mode
12. 改变恢复模式

etc.

等。

#10

I came across this post even though I needed to SHRINKFILE on MSSQL 2012 version which is little trickier since 2000 or 2005 versions. After reading up on all risks and issues related to this issue I ended up testing. Long story short, the best results I got were from using the MS SQL Server Management Studio.

尽管我需要在MSSQL 2012版本上压缩文件，但我还是遇到了这篇文章。在阅读了与此问题相关的所有风险和问题后，我最终进行了测试。长话短说，我得到的最好结果是使用MS SQL Server Management Studio。

Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file

#11

You also have to modify the minimum size of the data and log files. DBCC SHRINKDATABASE will shrink the data inside the files you already have allocated. To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size.

您还必须修改数据和日志文件的最小大小。DBCC SHRINKDATABASE将缩小已分配文件中的数据。要将文件缩小到小于最小大小的大小，请使用DBCC SHRINKFILE并指定新的大小。

#12

Delete data, make sure recovery model is simple, then skrink (either shrink database or shrink files works). If the data file is still too big, AND you use heaps to store data -- that is, no clustered index on large tables -- then you might have this problem regarding deleting data from heaps: http://support.microsoft.com/kb/913399

删除数据，确保恢复模型简单，然后skrink(收缩数据库或收缩文件都可以)。如果数据文件仍然太大，并且您使用堆来存储数据——也就是说，在大型表上没有聚集索引——那么您可能会遇到从堆中删除数据的问题:http://support.microsoft.com/kb/913399

#13

I recently did this. I was trying to make a compact version of my database for testing on the road, but I just couldn't get it to shrink, no matter how many rows I deleted. Eventually, after many other commands in this thread, I found that my clustered indexes were not getting rebuilt after deleting rows. Rebuilding my indexes made it so I could shrink properly.

最近我做了这个。我试图制作一个数据库的紧凑版本，以便在路上进行测试，但是无论我删除多少行，我都无法使它缩小。最后，在这个线程中执行了许多其他命令之后，我发现我的集群索引在删除行之后并没有重新构建。重建索引使我可以适当地收缩。

#14

Not sure how practical this would be, and depending on the size of the database, number of tables and other complexities, but I:

不知道这有多实际，取决于数据库的大小、表的数量和其他复杂性，但我:

1. defrag the physical drive
2. 整理磁盘碎片的物理驱动器
3. create a new database according to my requirements, space, percentage growth, etc
4. 根据我的需求、空间、百分比增长等创建一个新的数据库
5. use the simple ssms task to import all tables from the old db to the new db
6. 使用简单的ssms任务将所有表从旧db导入到新db
7. script out the indexes for all tables on the old database, and then recreate the indexes on the new database. expand as needed for foreign keys etc.
8. 为旧数据库上的所有表编写索引脚本，然后在新数据库上重新创建索引。根据需要扩展外键等。
9. rename databases as needed, confirm successful, delete old
10. 根据需要重命名数据库，确认成功，删除旧的

#15

I think you can remove all your log with switch from full to simple recovery. Right click on your Database and select Properties and select Options and change

我认为您可以通过从完全恢复到简单恢复来删除所有日志。右键单击数据库，选择属性，选择选项并更改

• Recovery mode to Simple
• 恢复模式简单
• Containment type to None
• 容器类型都