主页 / dba / 问题 / 56883
Accepted
IT researcher
Asked: 2014-01-17 02:09:15 +0800 CST

仅将数据库收缩到创建数据库后设置的初始大小

  • 772

在 SQL Server 中，每个数据库都有一个属性Initial Size (MB)，可以在 SSMS 中的数据库属性中看到。默认情况下，mdf 为 3 MB，ldf 文件为 1 MB。

所以现在如果创建一个新数据库，那么它将被设置为默认大小（即 mdf 为 3 MB，ldf 文件为 1 mb）。但是在创建数据库后，我将初始大小更改为其他值，例如对于 mdf 10 MB 和 ldf 5 MB。（实时某些数据库管理员可能希望在创建数据库后更改初始值）

但是现在如果缩小数据库，它将缩小到我在创建后设置的初始大小之外（考虑数据库中没有数据，否则它将缩小到其实际内容大小）。它将缩小到创建数据库期间的初始大小（即 mdf 为 3mb，ldf 为 1mb）。我希望缩小到我设置的初始大小（即 mdf 为 10 mb，ldf 为 5mb）。有可能做到吗？如果可能，那怎么办？

在某些文章中，我看到 dbcc SHRINKFILE 可用于缩小超出初始大小，而 dbcc SHRINKDATABASE 不能缩小超出初始大小。但我只想缩小到我设置的初始大小。

注意：我知道收缩是不好的，不应该做。但我想知道怎么做？如果 sql server 只考虑创建数据库时设置的初始大小，那么在创建数据库后设置初始大小有什么用？

编辑：

来自微软收缩相关文章：使用 DBCC SHRINKDATABASE 您不能将数据库收缩到其初始大小以下。

我的问题是我可以更改现有数据库本身的初始大小吗？

编辑：

我有大约 5 个 sql server 实例。所有创建的数据库默认大小为 3 mb 的数据和 1 mb 的日志（大约 3 年前）。现在所有的数据库都在 100-1000 mb 的范围内。我不会正常收缩数据库，因为这不是一个好习惯。但是一些数据库增长超过 10 GB（mdf 文件本身，因为我在其中插入了这么多数据）。因此，当数据库很大时，我会将不重要的旧内容从该数据库转移到另一台服务器，因为该服务器不需要我。因此，数据库大小从 10 GB 减少到 1 GB。现在剩余的 9 Gb 未使用。现在我想指定数据库大小减少到 2 GB，这样我将获得 1 GB 可用空间（2 Gb 也是这个数据库的理想大小），这可用于避免自动增长，直到下一个 1 Gb 数据被填充. 我还为这种具有更多写入的大型数据库设置了 100 MB 的自动增长。因此，与其在 shrinkfile 命令中将大小指定为 2 GB，不如将初始大小更改为 2 GB（shrink 考虑），那么它会更好。在这个数据库创建期间，没有关于大小、autogroth 等的计划。因为它是在大约 3 年前创建的。现在我打算设置自动增长等。我不能再次使用新的初始大小和移位数据创建新数据库。所以我问我是否可以更改现有数据库的初始大小？因为它是大约 3 年前创建的。现在我打算设置自动增长等。我不能再次使用新的初始大小和移位数据创建新数据库。所以我问我是否可以更改现有数据库的初始大小？因为它是大约 3 年前创建的。现在我打算设置自动增长等。我不能再次使用新的初始大小和移位数据创建新数据库。所以我问我是否可以更改现有数据库的初始大小？

sql-server sql-server-2008-r2

3 个回答

  1. Best Answer

    初始大小不仅仅是 3MB，它取自模型数据库（如果在创建用户数据库期间未指定）。因此假设您在创建用户数据库期间没有指定初始大小并且您没有更改创建用户数据库后的模型数据库文件大小，您可以执行以下操作：

    --Create testDB
CREATE DATABASE [TEST_100]
GO


--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO


--switch context
USE [TEST_100]
GO



--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files 
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.


--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

    编辑

    好的，在您的编辑和评论之后需要一些额外的信息。

    首先。我觉得您在 SSMS 中查看文件属性时看到的“初始大小”标签是用词不当。基本上，您的初始尺寸只是一个概念。它是创建数据库期间使用的第一个大小。您可以在CREATE DATABASE语句中显式指定它，也可以通过在创建期间省略该信息让 SQL Server 从模型数据库中隐式复制它。

    但是，一旦创建了数据库，从 DBA 的角度来看，就没有“初始大小”之类的东西，只有一个属性对 DBA 可见，那就是：实际大小。甚至 SSMS 中的“初始大小”属性也只显示实际大小，而不是初始大小。

    那么怎么来DBCC SHRINKFILE或DBCC SHRINKDATABASE“知道”初始大小呢？即使你改变了大小。有趣的问题。

    数据库文件的第一页是文件头页。在那里，您有 2 个属性：size 和 minsize。

    在创建文件时，两个文件头属性都填充了初始值：

    DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

    在此处输入图像描述

    两种大小都以数据页的数量计。在这种情况下。288 个数据页。

    现在，如果我更改文件大小：

    ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

    在此处输入图像描述

    您可以看到“大小”属性已更改以反映新大小。但是，“MinSize”属性仍包含“初始”大小。这是收缩命令将达到的最小尺寸。

    不过，说了这么多。我仍然不明白为什么要通过首先更改初始大小然后缩小到初始大小来使事情复杂化。而不是直接缩小到目标大小。

    无论如何，回答你的问题。“初始”大小不作为属性公开给用户/dba。

    • 9

  2. @Edward 对如何根据模型中设置的初始大小缩小文件有一个很好的答案。我会回答这个：

    创建数据库后设置初始大小有什么用？

    并假设您的意思是：

    创建数据库时设置初始大小有什么用？

    当您提前知道您的数据库大小应该是什么时，这对于数据和日志文件都非常有用。如果您正在创建两个数据库，一个用于配置数据，一个用于事务数据，它们可能会有非常不同的增长模式。因此，与其从模型中获取可怕的、可怕的、可怕的默认值（大小和增长设置），更有意义的是始终根据每个数据库的需要自定义它（即使您已将可怕的默认值更改为更多合理的，这通常不会是一刀切的）。您可以随时在繁忙时段之外手动自动增长文件，以适应对初始预测的更改。而不是仅仅让数据收集更改的性质使用您最初的、现在不正确的设置来指导增长。

    创建数据库的长期目标之一应该是最小化或完全消除任何和所有文件大小更改事件。在容量规划方面并不总是 100% 准确，但您应该考虑一下，例如，您希望在下个月、下一年等收集多少数据。这可能有点困难日志，但是一旦你知道你期望有多少数据，你可以用这样的公式粗略计算日志大小（这个想法来自 Geoff Hiten，实际上昨天刚刚穿过我的办公桌）：

    (Largest index * 1.25) 
+ 
(largest amount of log created during reindex run * 2.1)

    因此，如果您的最大索引为 4 GB，并且您的最后一次重新索引创建了 2 GB 的日志，那么 4 * 1.25 + 2 * 2.1 = 9.2 GB 的日志文件。这将为您提供一个日志文件，该文件可以处理您的典型重建 - 包括回滚，如果发生这种情况 - 而无需增加物理文件。

    最佳的自动增长设置有点难以预测，因为它是一种平衡。您希望尽量减少文件增长的次数，但也要尽量减少每次增长所需的时间。这意味着您永远不希望 1 MB 的增长大小。假设您有一个要插入 15 MB 数据的事务。一次增长 50 MB 以适应这种增长（以及接下来的两个事务也是如此）比在 15 个独立增长事件中增长要好得多。而且您可能也不希望增长到 20 GB，因为这本身可能需要很长时间。特别是对于日志文件，因为它们无法从即时文件初始化中受益，偏向较小的大小更好，但我不知道在不知道典型事务的平均大小以及日志文件所在存储的性能特征的情况下是否有任何最佳幻数。

    同样，重点是避免像瘟疫这样的增长和收缩事件。

    虽然有时是必要的，但增长和收缩事件都会损害数据库的性能。增长 - 即使启用了即时文件初始化，这仅适用于数据文件，因为无论如何日志文件都必须清零 - 要求所有事务在文件扩展时暂停。收缩通常会导致碎片——尤其是在慢速 I/O 上——在读取大表时可以真正感受到。使用 SSD，这变得越来越不重要，当所有数据都适合内存时，它就变得不那么重要了。但是除了这两点...

    关于人们不断缩小文件，我不明白的是——为什么？如果您的数据或日志文件曾经增长到该大小，它将再次增长到该大小。在此期间释放空间对我来说似乎是徒劳的。在此期间，您打算将空间用于什么？你不能放任何东西，因为你需要腾出空间，以便 SQL Server 可以再次增大文件 - 然后你可以再次缩小它 - 然后它可以再次增长。等等。

    对于数据文件，您需要分配长期需要的空间，而不是今天。对于日志文件，您需要通过采用正确的恢复模式并为该恢复模式实施适当的备份策略来管理其中使用的空间。

    如果您在紧急或异常事件之外缩小文件，则需要改变您做事的方式。

    • 2

  3. 我假设你的日志文件是用一定的初始大小创建的，现在你想减小 ldf 文件的初始大小？

    例如，MyDatabase 日志文件创建时初始大小为 10240 MB (10 GB)，但现在出于某种原因我想减小此初始大小（也许......为了利用更多空间）。

    不幸的是..当您的数据库状态在线时，您不能使用 DBCC SHRINKFILE 或 ALTER DATABASE 语法来减小日志 (*.ldf) 文件的初始大小。

    但是您可以使用删除并重新创建您的日志文件来完成这项工作，并且此过程需要您的数据库设置为脱机。这是将日志初始大小减小到低于第一次创建数据库时的初始大小的唯一方法。

    这里有一些你需要进一步研究的链接 http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

    http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html

    • 0

相关问题