SQL Server 2019事务日志扩容变量赋值报错：非动态SQL解决方案

ALTER DATABASE MODIFY FILE语句的SIZE参数仅接受常量数值，不支持直接使用T-SQL变量，这是SQL Server的语法限制，因此直接传入@newSize会触发语法错误。

SQLCMD模式下的变量会在脚本提交给SQL Server执行前被替换为具体常量值，以此绕过T-SQL变量的限制。这种方式可以在SQL Server Agent作业中直接使用，无需动态SQL。

修改后的脚本 #

:setvar DatabaseName "AdventureWorks2019"
:setvar LogFileName "AdventureWorks2019_log"

DECLARE @logSizeMB INT
DECLARE @logFreeSpaceMB INT
DECLARE @newSize INT

-- 获取当前日志文件总大小（MB）
SELECT @logSizeMB = size / 128 
FROM sys.database_files 
WHERE type_desc = 'LOG' AND name = '$(LogFileName)'

-- 计算可用日志空间（MB：总大小 - 已用大小）
SELECT @logFreeSpaceMB = (size / 128) - (FILEPROPERTY(name, 'SpaceUsed') / 128)
FROM sys.database_files 
WHERE type_desc = 'LOG' AND name = '$(LogFileName)'

-- 计算目标大小（当前大小增加20%，取整）
IF (@logFreeSpaceMB < (@logSizeMB * 0.10))
BEGIN
    SET @newSize = ROUND(@logSizeMB * 1.2, 0)
    -- 使用SQLCMD变量传递数据库名，SIZE参数替换为常量值
    ALTER DATABASE [$(DatabaseName)] MODIFY FILE (NAME = N'$(LogFileName)', SIZE = $(newSize))
END

作业配置说明 #

在SQL Server Agent中创建作业时，需将作业步骤的类型设置为“SQLCMD脚本”，而非默认的“Transact-SQL脚本(T-SQL)”，这样SQL Server会解析并替换脚本中的SQLCMD变量。

额外优化建议 #

• 日志空间计算可改用更精准的sys.dm_db_log_space_usage视图，避免FILEPROPERTY的局限性：

  SELECT 
      @logSizeMB = total_log_size_in_bytes / 1024 / 1024,
      @logFreeSpaceMB = (total_log_size_in_bytes - used_log_space_in_bytes) / 1024 / 1024
  FROM sys.dm_db_log_space_usage
  

• 增加日志文件大小前，建议先确认日志是否可以截断（若存在未备份/未截断的日志，扩容无法解决根本问题），确保数据库恢复模式和日志备份策略配置正确。

内容的提问来源于stack exchange，提问作者piotreks-uw-edu