咱们先解决你的脚本问题，再聊聊SQL Server空间管理的最佳实践，帮你从根源避免这类问题。

First: Fixing the Script Errors #

你的原脚本主要有三个核心问题：分支逻辑漏洞导致tempdb误执行恢复模式修改、硬编码文件名的兼容性风险、未排除敏感系统库。下面是优化后的存储过程，解决了这些问题：

CREATE OR ALTER PROCEDURE dbo.ShrinkDatabaseSpace
AS
BEGIN
    SET NOCOUNT ON;

    -- 遍历数据库时排除master、model、msdb这些不能随便操作的系统库
    DECLARE @DBName sysname;
    DECLARE db_cursor CURSOR FOR
        SELECT name 
        FROM sys.databases 
        WHERE name NOT IN ('master', 'model', 'msdb')
        ORDER BY name;

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            PRINT 'Processing database: ' + @DBName;

            -- 场景1: 单独处理tempdb，不修改恢复模式（tempdb不允许）
            IF @DBName = 'tempdb'
            BEGIN
                PRINT 'Shrinking tempdb files...';
                DECLARE @TempFileSQL NVARCHAR(MAX) = '';
                -- 动态获取所有tempdb文件，避免硬编码
                SELECT @TempFileSQL += 'DBCC SHRINKFILE (''' + name + ''', 1);' + CHAR(10)
                FROM tempdb.sys.database_files;

                EXEC sp_executesql @TempFileSQL;
                PRINT 'tempdb shrink completed.';
            END
            -- 场景2: 处理BD_MAIN，仅操作最后2个数据文件和最后2个日志文件
            ELSE IF @DBName = 'BD_MAIN'
            BEGIN
                PRINT 'Shrinking BD_MAIN''s last 2 data and log files...';
                DECLARE @BDMainSQL NVARCHAR(MAX) = '';

                -- 动态获取最后2个数据文件（type=0代表数据文件）
                SELECT TOP 2 @BDMainSQL += 'DBCC SHRINKFILE (''' + name + ''', 1);' + CHAR(10)
                FROM sys.master_files
                WHERE database_id = DB_ID('BD_MAIN') AND type = 0
                ORDER BY file_id DESC;

                -- 临时切换到简单恢复模式收缩日志
                SET @BDMainSQL += 'ALTER DATABASE BD_MAIN SET RECOVERY SIMPLE;' + CHAR(10);
                
                -- 动态获取最后2个日志文件（type=1代表日志文件）
                SELECT TOP 2 @BDMainSQL += 'DBCC SHRINKFILE (''' + name + ''', 1);' + CHAR(10)
                FROM sys.master_files
                WHERE database_id = DB_ID('BD_MAIN') AND type = 1
                ORDER BY file_id DESC;

                -- 切回完整恢复模式，同时触发一次日志备份重建日志链
                SET @BDMainSQL += 'ALTER DATABASE BD_MAIN SET RECOVERY FULL;' + CHAR(10);
                SET @BDMainSQL += 'BACKUP LOG BD_MAIN TO DISK=''NUL:'';' + CHAR(10); -- 空备份，仅用于重建链

                EXEC sp_executesql @BDMainSQL;
                PRINT 'BD_MAIN shrink completed.';
            END
            -- 场景3: 处理其他用户数据库
            ELSE
            BEGIN
                PRINT 'Shrinking user database: ' + @DBName;
                DECLARE @UserDBQL NVARCHAR(MAX) = '';

                -- 获取主数据文件（file_id=1）
                SELECT @UserDBQL += 'DBCC SHRINKFILE (''' + name + ''', 1);' + CHAR(10)
                FROM sys.master_files
                WHERE database_id = DB_ID(@DBName) AND file_id = 1;

                -- 临时切换恢复模式收缩日志
                SET @UserDBQL += 'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE;' + CHAR(10);
                
                -- 获取主日志文件（file_id=2）
                SELECT @UserDBQL += 'DBCC SHRINKFILE (''' + name + ''', 1);' + CHAR(10)
                FROM sys.master_files
                WHERE database_id = DB_ID(@DBName) AND file_id = 2;

                -- 切回完整恢复模式并重建日志链
                SET @UserDBQL += 'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL;' + CHAR(10);
                SET @UserDBQL += 'BACKUP LOG [' + @DBName + '] TO DISK=''NUL:'';' + CHAR(10);

                EXEC sp_executesql @UserDBQL;
                PRINT @DBName + ' shrink completed.';
            END
        END TRY
        BEGIN CATCH
            -- 捕获单库错误，不中断整个脚本执行
            PRINT 'Error processing ' + @DBName + ': ' + ERROR_MESSAGE();
        END CATCH

        FETCH NEXT FROM db_cursor INTO @DBName;
    END

    CLOSE db_cursor;
    DEALLOCATE db_cursor;
END
GO

关键改进点： #

• 替换sp_MSforeachdb为游标：这个未公开的系统存储过程逻辑不透明，游标更可控，能精准排除敏感系统库。
• 修复分支逻辑：用IF...ELSE IF...ELSE确保每个场景互斥，tempdb不会误进入修改恢复模式的分支。
• 动态获取文件名：避免硬编码，适配文件名变更的情况。
• 添加错误处理：单库出错不会终止整个脚本，还能打印错误信息便于排查。
• 日志链重建：切回完整恢复模式后加空日志备份，避免后续日志备份失败。

Second: Best Practices for SQL Server Space Reclamation #

空间释放的合理方法 #

1. 优先用DBCC SHRINKFILE而非SHRINKDATABASE：
   • 单个文件收缩更可控，避免整个数据库碎片化。
   • 注意：收缩会产生大量索引碎片，收缩后必须重建/重组索引，否则查询性能会暴跌。
2. 日志文件收缩的正确姿势：
   • 完整恢复模式下，必须先备份日志再收缩（否则日志链未断开，无法释放空间）。
   • 临时切换到简单模式是快速收缩的方法，但切回完整模式后一定要做一次日志备份重建链。
3. 归档旧数据才是根本：
   • 频繁收缩是饮鸩止渴，最好的方式是归档不再需要的历史数据，从根源减少数据量。

空间释放的合适时机 #

• 批量删除数据后：比如清理了大量历史数据，数据文件有大量空闲空间时。
• 业务低峰期：收缩和索引重建都是IO密集型操作，一定要在非业务时段执行，避免影响用户。
• 避免频繁收缩：频繁收缩会导致严重的索引碎片化，大幅降低查询性能，只在必要时执行。
• tempdb收缩场景：只有当tempdb因大查询、未释放的临时对象异常膨胀时才需要收缩；正常情况下，SQL Server重启后tempdb会自动重置到初始大小。

内容的提问来源于stack exchange，提问作者Fran.J