主页 / dba / 问题 / 206234
Accepted
Vorster
Asked: 2018-05-10 03:39:11 +0800 CST

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

  • 772

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

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

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

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

sql-server dbcc

3 个回答

  1. 问号将评估为数据库名称，而不是您尝试缩小的文件名。

    例如：

    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 ]
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    } ) [ WITH NO_INFOMSGS ]

    如果您只有一个 .mdf 和一个 .ldf，您可以（可能，但不是绝对）将您的代码替换为：

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

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

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

    如果您只想缩小整个内容，请改用DBCC SHRINKDATABASE。这需要一个数据库名称，并将与您的原始代码一起使用。

    DBCC SHRINKDATABASE (database_name | database_id | 0
    [, target_percent]
    [, { NOTRUNCATE | TRUNCATEONLY }]) [ WITH NO_INFOMSGS]

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

    • 4
  2. Best Answer

    @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%），然后手动缩小它们。这可能取决于恢复模型，以及这些数据库在该环境中获得多少活动。

    • 2

  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
    • 0

相关问题