如何在MS SQL批量插入前检测文件是否被占用并告警

这个场景太常见了——用户忘了关文件导致批量插入作业失败，还得手动排查原因。我之前帮几个客户做过类似的解决方案，核心就是在Bulk Insert前加个文件占用检测的前置逻辑，结合SQL Server代理作业和PowerShell脚本就能完美解决，具体步骤如下：

1. 编写文件占用检测的PowerShell脚本 #

先写一个能检测文件是否被占用，还能拿到占用用户的脚本，比如保存为Test-FileLock.ps1：

param(
    [string]$FilePath
)

try {
    # 尝试以无共享的读写模式打开文件，成功则说明未被占用
    $fileStream = [System.IO.File]::Open($FilePath, [System.IO.FileMode]::Open, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None)
    if ($fileStream) { $fileStream.Close() }
    Write-Output "False" # 返回未占用标记
}
catch {
    # 从异常信息中提取占用文件的进程ID
    $processId = $_.Exception.Message -split ', ' | Where-Object { $_ -match 'ProcessID' } | ForEach-Object { $_ -split '=' | Select-Object -Last 1 }
    # 通过WMI查询该进程的所有者用户名
    $processOwner = Get-WmiObject Win32_Process -Filter "ProcessId = $processId" | Select-Object -ExpandProperty GetOwner
    Write-Output "True|$($processOwner.User)" # 返回占用标记+用户名
}

2. 配置SQL Server代理作业的多步骤流程 #

把你的Bulk Insert作业拆成几个关联的步骤，实现“检测→告警→重试→执行”的逻辑：

步骤0：初始化状态表（可选，替代临时表） #

因为作业的每个步骤是独立会话，临时表跨步骤无法访问，所以建一个永久状态表来存检测结果：

CREATE TABLE dbo.JobFileLockStatus (
    JobRunID UNIQUEIDENTIFIER DEFAULT NEWID(),
    FilePath NVARCHAR(500),
    IsLocked BIT,
    LockingUser NVARCHAR(100),
    CheckTime DATETIME DEFAULT GETDATE()
)

每次作业运行时，插入一条新的状态记录。

步骤1：检测文件占用状态 #

作业类型选择PowerShell，调用上面的脚本并把结果写入状态表：

# 替换成你的文件路径和SQL实例/数据库信息
$targetFile = "D:\DataFiles\YourImportFile.csv"
$sqlInstance = "YourSQLServerInstance"
$sqlDB = "YourDatabaseName"

# 执行检测脚本
$detectResult = . "C:\SQLScripts\Test-FileLock.ps1" -FilePath $targetFile
$splitResult = $detectResult -split '\|'

# 把结果写入状态表
if ($splitResult[0] -eq "True") {
    Invoke-SqlCmd -ServerInstance $sqlInstance -Database $sqlDB -Query "
        INSERT INTO dbo.JobFileLockStatus (FilePath, IsLocked, LockingUser)
        VALUES ('$targetFile', 1, '$($splitResult[1])')
    "
    exit 1 # 标记步骤失败，触发告警流程
} else {
    Invoke-SqlCmd -ServerInstance $sqlInstance -Database $sqlDB -Query "
        INSERT INTO dbo.JobFileLockStatus (FilePath, IsLocked)
        VALUES ('$targetFile', 0)
    "
    exit 0 # 标记步骤成功，进入Bulk Insert
}

步骤2：发送占用告警邮件 #

作业类型选择Transact-SQL (TSQL)，设置为“步骤1失败时执行”，内容如下（确保已配置SQL数据库邮件）：

DECLARE @LockingUser NVARCHAR(100), @FileName NVARCHAR(200)
SELECT TOP 1 
    @LockingUser = LockingUser,
    @FileName = RIGHT(FilePath, CHARINDEX('\', REVERSE(FilePath)) - 1)
FROM dbo.JobFileLockStatus
WHERE FilePath = 'D:\DataFiles\YourImportFile.csv'
ORDER BY CheckTime DESC

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourDBMailProfile', -- 替换成你的邮件配置文件
    @recipients = 'ops-team@yourcompany.com', -- 告警接收邮箱
    @subject = '【Bulk Insert作业暂停】文件被占用',
    @body = '告警提示：*' + @LockingUser + '* 用户正在使用 *' + @FileName + '* 文件，批量插入作业已暂停，请通知用户关闭文件后，作业将自动重试或手动触发。'

步骤3：等待后自动重试 #

作业类型选择Transact-SQL (TSQL)，设置为“步骤2成功时执行”，添加等待逻辑后回到步骤1：

-- 等待10分钟后重试（可根据需求调整时长）
WAITFOR DELAY '00:10:00'

然后在作业的“流控制”里设置：步骤3执行成功后，跳转到步骤1，形成循环检测。

步骤4：执行Bulk Insert #

作业类型选择Transact-SQL (TSQL)，设置为“步骤1成功时执行”，放入你的批量插入语句：

BULK INSERT YourTargetTable
FROM 'D:\DataFiles\YourImportFile.csv'
WITH (
    FIELDTERMINATOR = ',', -- 根据你的文件格式调整分隔符
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- 如果文件有表头，从第2行开始读取
    TABLOCK,
    CODEPAGE = '65001' -- 若文件是UTF-8格式，添加此参数
)

3. 关键权限说明 #

确保SQL Server代理服务的账号拥有以下权限：

• 读取目标文件（包括网络共享文件的访问权限）
• 执行PowerShell脚本的权限
• 查询WMI服务以获取进程所有者的权限
• 写入状态表和发送数据库邮件的权限

内容的提问来源于stack exchange，提问作者RAJ