如何快速收缩所有数据库的所有文件？

在SQL Server（在本例中为2008）中，如何快速缩小实例上所有数据库的所有文件（日志和数据）？我可以遍历SSMS并右键单击每个任务，然后选择“任务”->“收缩”，但是我正在寻找更快的东西。

我编写了一些“创建数据库”脚本，但忘记了它们的默认值已膨胀，并且不需要在该项目中为这些文件保留太多空间。

#1 楼

当您从GUI执行“任务->收缩”时，实际上会在幕后发出DBCC SHRINKDATABASE命令。试试看。出现对话框时，请不要单击“确定”按钮。而是单击“脚本”按钮。您将在查询窗口中看到该命令。将其与对sys.databases的查询结合起来（省去master和msdb），然后可以制作一个脚本来收缩所有数据库。例如（摘自jcolebrand的评论）：

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

复制该查询的输出并运行它以收缩所有文件。

好的，我想我有想要的（难看，但确实满足了我的需要）SELECT'USE ['+ d.name + N']'+ CHAR（13）+ CHAR（10）+'DBCC SHRINKFILE（N '''+ mf.name + N'''，0，TRUNCATEONLY）'+ CHAR（13）+ CHAR（10）+ CHAR（13）+ CHAR（10）来自sys.master_files mf JOIN sys.databases d ON mf .database_id = d.database_id其中d.database_id> 4但是弄清楚这一点给了我一个新问题。下发另一个问题。

– jcolebrand♦
2011年1月6日在22:09

说真的查看@Sandy的答案。 sp_MSForEachDB（也有一个“表”存储程序）非常有用

–swasheck
2012年3月14日15:37

这是所有正在阅读本文的人的强制性提醒：缩小数据库很危险。

–尼克·查玛斯（Nick Chammas）
2012年3月14日15:40

过滤掉脱机数据库将使其变得更好。 :-)

– TiloBunt
2014年11月21日19:27

同意@TiloBunt，因为d.database_id> 4 AND d.state_desc ='ONLINE'，所以整个条件更好。

–毛罗（Mauro）
18年7月13日在9:15

#2 楼

一行单独的sql语句怎么样？

在执行以下sql语句之前，请阅读此非常有趣的博客文章。

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

如果单行代码可能无法正常工作，则不一定会更好。请同时阅读这些帖子，因为sp_msforeachdb可以跳过数据库而不发出警告：sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/…和mssqltips.com/sqlservertip/2201/…

–亚伦·伯特兰（Aaron Bertrand）
2012年3月14日15:55

这是否还会缩小LOG，因为这是OP所要求的，并且可能误导人们运行它并认为一切都很好。

–史蒂夫·德雷克（Steve Drake）
19/12/16在10:20

#3 楼

DBCC SHRINKDB（及其表亲SHRINKFILE）非常慢，因为该代码中有很多单线程执行。

收缩数据库文件的更快方法是：

向数据库分配一个新的文件组
使该文件组尽可能大（使用sp_spaceused确定其大小）
将所有索引重建到该新文件组
丢弃旧的文件组

由于索引重建是大规模并行的，因此该技术通常会导致数据库收缩更快。当然，它确实需要您在过程进行过程中为新文件组留一些额外的空间。但是，新文件组中仅需要足够的空间来容纳实例中最大的文件组（因为随着使用，您将回收空间）。

该技术还具有对磁盘碎片整理的额外好处。索引过程中。

您忘记了重要的部分。重建索引不会移动任何其他内容，包括存储过程，视图，函数，同义词，堆等。

–杰夫·摩登（Jeff Moden）
19年5月29日在18:01

而且这些都不会占用您应该关注的空间。它们也必须驻留在PRIMARY文件组中，您不能真正移动它们（也不应）

–托马斯·凯瑟（Thomas Kejser）
19年4月4日在10:28

你有一个脚本吗？

–艾卡伯伦
20年6月26日在18:57

#4 楼

我对查询进行了一些微调，以仅按要求缩小LOG：

set nocount on  
SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

“迅速缩小所有文件，包括日志和数据”

– dezso
13年2月14日在22:23

我一直在寻找这个，当我看到你的答案时将要加倍。不是直接的答案，而是非常相关，适合我的情况。

– Gomibushi
2013年12月17日在12:28

#5 楼

下面的代码获取非系统数据库的列表，将数据库设置为只读，然后缩小文件。我使用SQL Agent Job将这些代码保存在几个SQL Server框中，其中空间始终是一个问题。在每周的星期六/星期日晚上，它开始运行，并在几个小时内收缩所有数据库（取决于数据库的大小）。

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c

#6 楼

收缩除主数据库，模型数据库，msdb以外的所有日志文件：

EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
IF ''?'' NOT IN (''master'', ''model'', ''msdb'')
BEGIN
USE [?]
SELECT @sqlcommand = ''DBCC SHRINKFILE (N'''''' + 
name
FROM [sys].[database_files]
WHERE type_desc = ''LOG''
SELECT @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
END'

#7 楼

这个扩展了上面的答案，使用游标一个接一个地遍历SQL语句。它不像Emrah的回答那么短，但是确实允许在游标中的while循环内使用其他逻辑。.

SELECT 
    'USE [' 
    + databases.name + N']' 
    + CHAR(13) 
    + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' 
    + masterFiles.name 
    + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) 
    + CHAR(10) 
    + CHAR(13) 
    + CHAR(10)                                                                  AS sqlCommand
INTO
    #shrinkCommands
FROM 
    [sys].[master_files] masterFiles 
    INNER JOIN [sys].[databases] databases ON masterFiles.database_id = databases.database_id 
WHERE 
    databases.database_id > 4; -- Exclude system DBs


DECLARE iterationCursor CURSOR

FOR
    SELECT 
        sqlCommand 
    FROM 
        #shrinkCommands

OPEN iterationCursor

DECLARE @sqlStatement varchar(max)

FETCH NEXT FROM iterationCursor INTO @sqlStatement

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC(@sqlStatement)
    FETCH NEXT FROM iterationCursor INTO @sqlStatement
END

-- Clean up
CLOSE iterationCursor
DEALLOCATE iterationCursor
DROP TABLE #shrinkCommands

#8 楼

我们可以为所有数据库动态地重复SHRINKDB和SHRINKFILE：

while @DBID<=@MaxDBID
begin
  -- Used Dynamic SQL for all databases.
  Set @SQL ='Use '+@DBName+ ' '+Char(10)
  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)
  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

  --#6 Increment DBid for looping over all databases
  Set @DBID = @DBID+1
  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'
  Print (@SQL)
  Exec (@SQL)
end

您可以在本文中找到详细信息。

编程黑洞网