使用sp_MSForEachDB收缩多个数据库文件

Question

我们最近从我们的生产数据库中清除了旧数据。数据库为3TB，1.4TB为空，但这给开发和QA实例带来了问题，因为我们没有使用6-8个有1.4TB空空间的数据库，特别是在开发方面有空间限制。我想设置一个作业来使用下面的代码缩小开发数据库

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (''?'' , 10)' 

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (''?'' , 0, TRUNCATEONLY)' 

我收到以下错误：“无法在sys.database_files中找到数据库'Test‘的文件'Test’。该文件要么不存在，要么被删除。”数据库有多个数据文件。如何改进我的代码以适应多个数据文件。

Answer 1

@sp_BlitzErik正确地识别了问题，但我提出了一个不同的解决方案:使用一个创建SHRINKFILE语句的一次性脚本，检查它们是否正常，然后手动运行它们，或者将它们放到代理作业中：

SELECT  dbname = DB_NAME(),
        file_name = name, 
        TotalMB = CONVERT(decimal(12,1),size/128.0),
        UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
        FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
        Command = CONCAT('USE ', DB_NAME(), '; DBCC SHRINKFILE (name = ',
              [name], ', size = ', 
              convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
 FROM sys.database_files WITH (NOLOCK)
 WHERE type_desc = 'ROWS'
 ORDER BY file_id;

在每个数据库中运行一次，它应该返回每个数据文件的总大小和使用的大小(它跳过日志文件，之后您可以立即手动缩小这些文件)，以及一个示例SHRINKFILE语句，它为您提供了一个目标文件中15%的空闲空间，根据当前使用的空间计算：

USE myDB; DBCC SHRINKFILE (name = myDBData, size = 148910MB)

您需要检查结果是否正确，如果文件的空闲空间已经不足15%，那么SHRINKFILE语句将指定比当前更大的大小，所以跳过它(它已经足够小了)。

在缩小了所有数据文件之后，为每个日志文件选择一个目标大小(我通常使用数据文件大小的10-25% )，然后手工缩小这些大小。这可能取决于恢复模型，也取决于这些dbs在该环境中得到多少活动。

Answer 2

问号将计算为数据库名称，而不是要收缩的文件名。

例如：

EXEC master.sys.sp_MSforeachdb ' USE [?]; PRINT N''?''; '; 

将返回(在我的服务器上)

master
tempdb
model
msdb
SUPERUSER
StackOverflow
StackOverflow_CS
Crap
DBAtools
StackOverflow2010
SUPERUSER_CX
ಠ_ಠ
StackOverflow2010ಠ_ಠ

DBCC SHRINKFILE并不认为这是一个论点：

DBCC SHRINKFILE ( { file_name file_id } { 、EMPTYFILE x[ ，目标_大小 ] }) 没有_INFOMSGS

如果您只有一个.mdf和一个.ldf，您可以(很可能，虽然不是绝对的)将代码替换为：

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (1 , 10)' 

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)' 

用于查找文件is等的更详细的代码留作读者练习。

如果你只想缩小整件事，就用DBCC SHRINKDATABASE代替。这需要一个数据库名，并将使用您的原始代码。

DBCC SHRINKDATABASE ( database_name x database_id x 0 ，目标_百分比 ，{ NOTRUNCATE \ TRUNCATEONLY } ) 没有_INFOMSGS

当然，这会导致各种各样的问题，我不想这么做。

Answer 3

@BradC这就是我如何修改您建议的代码

CREATE TABLE #ShrinkFile
(
DBName sysname,
File_Name sysname,
TotalMB decimal (18,2),
UsedMB decimal (18,2),
FreeMB decimal (18,2),
Command nvarchar(MAX)
) 
EXEC master.sys.sp_MSforeachdb ' USE [?]; 
    Insert Into #ShrinkFile (DBName, File_Name, TotalMB, UsedMB, FreeMB, 
Command)
    SELECT  dbname = DB_NAME(),
    file_name = name, 
    TotalMB = CONVERT(decimal(12,1),size/128.0),
    UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,''SpaceUsed'')/128.0),
    FreeMB = CONVERT(decimal(12,1),(size - 
    FILEPROPERTY(name,''SpaceUsed''))/128.0),
    Command = CONCAT(''USE '', DB_NAME(), ''; DBCC SHRINKFILE (name = '',
          [name], '', size = '', 
          convert(int,round(1.15 * 
FILEPROPERTY(name,''SpaceUsed'')/128,-1)), ''MB)'')
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc = ''ROWS''
ORDER BY file_id;'

IF EXISTS (SELECT * FROM  #ShrinkFile WHERE FreeMB > 1000)
BEGIN
   DECLARE @SQLText nvarchar(max)
   DECLARE Shrink_cursor CURSOR FOR 
   SELECT DISTINCT Command FROM #ShrinkFile
   WHERE FreeMB > 1000

   OPEN Shrink_cursor  
   FETCH NEXT FROM Shrink_cursor INTO @SQLText  

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
        EXEC (@SQLText)
        FETCH NEXT FROM Shrink_cursor INTO @SQLText 
   END 

 CLOSE Shrink_cursor  
 DEALLOCATE Shrink_cursor 
 END

 DROP TABLE #ShrinkFile