我需要缩小数据库-我释放了很多空间

#1 楼

最初的警告：通常，收缩生产数据库或数据文件是最坏的做法（日志文件是此问题所谈论的另一个问题）。我建议人们不要在这样的博客文章中缩减数据库，在这些文章中我谈到“调整大小”和良好的计划。我并不孤单（Paul Randal，Brent Ozar，只是为了提供更多链接）。缩小数据文件或数据库碎片索引的过程非常耗时且费力，可能会浪费系统，这是一件坏事，通常
在这种情况下，我们都知道存在风险，我们已经准备好应对它，但是我们释放了很多我们永远都不再需要的空间。因此，在这种特定类型的情况下-缩小作为我们的选择之一是很有意义的。

如果您已经了解了担忧和风险，但仍然需要进行缩小，因为您释放了大量的空间，希望本答案的其余部分对您有所帮助。但是请务必考虑风险。

这里有两种主要方法：

1.）收缩是，进行实际收缩-考虑使用DBCC SHRINKFILE而不是DBCC SHRINKDATABASE，您可以对缩小的内容以及如何进行更多的控制。这肯定会导致性能下降-这是执行大量IO的大型操作。您可以通过重复缩小到逐渐减小的目标大小来摆脱困境。

这是上面DBCC SHRINKFILE链接中的“ A.）”示例。在此示例中，数据文件缩小到7MB目标大小。这种格式是在停机时间窗口允许的情况下反复缩小的一种好方法。我将在开发测试中进行此操作，以查看性能如何以及可以增加/降低的幅度，并确定预期的生产时间。这是一项联机操作-您可以与正在访问正在收缩的数据库的系统中的用户一起运行它，但是几乎可以保证性能会下降。因此，监视并观察并查看对服务器的操作，最好选择停机时间窗口或活动较少的时间段。

USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

始终记住：-每次收缩会使您的索引碎片化，如果要在很长一段时间内收缩成块，则应该重建索引。现在，如果您无法在一个窗口中完成所有操作，则每次都将产生该费用。

2.）新数据库-您可以创建一个新数据库并将数据迁移到该数据库。您必须将空数据库及其所有键，索引，对象，proc，函数等编写成脚本，然后将数据迁移到该数据库。您可以为此编写脚本，也可以使用Red Gate或其他供应商提供的类似SQL Data Compare之类的工具。这是更多的设置工作，需要进行更多的开发和测试，并且视您的环境而定，这可能还会使您的停机时间窗口变大，但您也可以考虑这样做。

当我被迫缩减数据库时
如果这是我的环境，我希望在数据文件中保留大量的空白空间，因为我喜欢做磁盘生猪，并且愿意为将来的/意外的增长做好准备。因此，如果我们只是删除大部分空间，我会回馈空间，但是我永远不会相信那些说“但它永远不会再增长”并且仍然留有空白的人。如果停机时间窗口较小并且不想增加创建空数据库并将数据迁移到数据库的复杂性，那么我可能会选择的方法是收缩方法。因此，我将逐步缩小它一堆（基于我认为需要在开发人员中进行的测试次数和所需的大小。逐渐选择较小的文件大小），然后重新构建索引。然后我d永远不要告诉任何人我缩小了数据库；-)

#2 楼

如何缩小数据库？我要缩小哪些文件？：您可以通过提及的DBCC SHRINKFILE命令分别缩小文件。这取决于您的服务器数据库包含多少个文件。一个简单的数据库有一个数据库文件和一个事务日志文件。

执行此操作时应考虑什么？：收缩会影响索引碎片，请参见第3点。还要注意，您不希望将数据库文件缩小到尽可能小的大小，因为在现实环境中，它无论如何都会增长。因此，我将调整大小（在您的示例中为7 MB），您将在数据库文件中保留10％-20％的可用空间，因为无论如何它都会在生产环境中填充，并且您可以这样可以节省一些自动增长周期。因此，实际数字需要仔细计算。还要注意，您执行的“大空间释放”将使事务日志文件膨胀得比在DB文件中获得的空间更多。另外，您可能实际体验到的空间增益将小于您的数学期望！因此，假设您在数学上释放了12个演出，那么您也许只能缩小

我以后应该做些什么吗？：如前所述，您想重新索引那些导致碎片变形的索引的变化。如果您需要对查询统计数据做一些特别的事情，我还没有做足够的尝试。

如果是大型数据库怎么办？我可以缩小一点吗？ SHRINK操作可以随时中断，您可以稍后继续。如果可能，我建议在离线数据库上执行它。通过打断和控制，虽然可以缩小尺寸。从理论上讲，您可以通过指定较小的目标大小（而不是7兆字节）来以较小的增量进行缩小，但是我想说的是，如果您在生产中执行它，则只给它一次。如您所见，索引碎片和可能的事务日志增长存在问题。因此，我只经历了一次。

我们都知道，不建议定期进行SHRINK。我会尽量省略您可能仍然知道的所有警告和免责声明。备份，如果可能，不要在家中执行此操作：）

奖金：在复制环境中，如果在发布者数据库上执行此操作，则不会导致订阅者数据库收缩（可能尺寸问题，因为它们是Express版本。）最后，我的重新编制索引脚本：

USE YourDBName

DECLARE @TbName VARCHAR(255)
DECLARE @FullTbName VARCHAR(255)
DECLARE @IxName VARCHAR(255)
DECLARE myCursor CURSOR FOR
    SELECT OBJECT_NAME(dmi.object_id) AS TableName,i.name AS IndexName
    FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED') dmi
    JOIN  sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 30
    ORDER BY avg_fragmentation_in_percent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TbName, @ixName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'dba' AND TABLE_NAME = @TbName)
BEGIN
        SET @FullTbName = 'dba.' + @TbName
        IF (@ixName IS NULL)
        BEGIN
            PRINT 'Reindexing Table ' + @FullTbName
            DBCC DBREINDEX(@FullTbName, '', 0)
        END
        ELSE
        BEGIN
             PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
             DBCC DBREINDEX(@FullTbName, @IxName, 0)
        END
    END
    FETCH NEXT FROM myCursor INTO @TbName, @ixName
END
CLOSE myCursor
DEALLOCATE myCursor

其中唯一的变量是14可以通过发出select DB_ID('YourDBName')获得，该脚本假定您仅对dba。*模式中的表感兴趣。

#3 楼

您已经听到了有关收缩数据库的所有警告，它们都是对的。它会碎片化您的索引，并且通常会破坏您的数据库，因此不应在生产系统上进行。

但是，当我在自己的备份上还原备份时，通常每周执行一次工作站，因为我的SSD驱动器上有空间。
请记住，我不是写此脚本的，但是几年前才找到的。在其他数据库[250 GB]上，我创建了一个SSIS程序包，该程序包将转移所需的表，然后为该索引重新创建索引，以使索引具有全新的感觉。

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000)
DECLARE @SizeMB INT
DECLARE @UsedMB INT

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB
    ,[StartUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN
    SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

    PRINT 'Start ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    EXEC (@sql)

    PRINT 'Done ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    SELECT @SizeMB = size / 128.
    FROM sysfiles
    WHERE NAME = @DBFileName

    SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

    SELECT [FileSize] = @SizeMB
        ,[UsedSpace] = @UsedMB
        ,[DBFileName] = @DBFileName
END

SELECT [EndFileSize] = @SizeMB
    ,[EndUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

    ,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

#4 楼

下面的引号直接来自Microsoft（适用于2008-2016版），并提供有关是否/何时以及如何使用DBCC SHRINKFILE命令的指南。

https://msdn.microsoft.com/ zh-cn / library / ms189493.aspx


最佳做法

当您计划收缩文件时，请考虑以下信息：


收缩操作在创建大量未使用空间的操作（例如截断表或删除表）之后最有效。
大多数数据库需要一些可用空间来用于常规的日常运营。如果您反复收缩数据库并发现
数据库大小再次增加，则表明常规操作需要
缩小的空间。在这些情况下，
重复收缩数据库是一种浪费的操作。
收缩操作不能保留数据库中索引的碎片状态，通常将碎片增加到
程度。这是不重复收缩数据库的另一个原因。顺序而不是同时收缩同一数据库中的多个文件。系统表上的争用可能由于
阻塞而导致延迟。