通过链接服务器执行存储过程：跨三台服务器磁盘空间HTML邮件作业方案咨询

我来给你梳理下实现这个需求的最佳方案，分步骤走，每一步都明确可操作：

要让Server1能访问另外两台服务器的磁盘信息，首先得建立链接服务器。你可以用系统存储过程sp_addlinkedserver来创建，示例代码如下：

-- 创建到Server2的链接服务器
EXEC sp_addlinkedserver   
    @server = N'Server2', -- 链接服务器名称
    @srvproduct=N'SQL Server';  

-- 创建到Server3的链接服务器
EXEC sp_addlinkedserver   
    @server = N'Server3',
    @srvproduct=N'SQL Server';  

注意：记得给链接服务器配置合适的登录映射，确保用来访问的账号在Server2和Server3上有足够权限（至少能执行xp_fixeddrives，通常需要serveradmin或sysadmin角色，或者你可以给账号授予执行这个扩展存储过程的权限）。可以用sp_addlinkedsrvlogin来配置：

-- 配置Server2的登录映射（假设用Windows域账号）
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = N'Server2',
    @useself = N'True', -- 使用当前登录账号的凭据
    @locallogin = NULL;

-- 同理配置Server3
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = N'Server3',
    @useself = N'True',
    @locallogin = NULL;

如果用SQL账号的话，把@useself设为N'False'，然后指定@rmtuser和@rmtpassword即可。

接下来要写一个脚本，从三台服务器获取磁盘信息，整合后转成HTML格式。这里我们用临时表存储数据，再用FOR XML PATH生成美观的HTML表格：

-- 临时表存储所有服务器的磁盘信息
CREATE TABLE #DiskSpace (
    ServerName NVARCHAR(100),
    DriveLetter CHAR(1),
    FreeSpaceMB INT,
    FreeSpaceGB DECIMAL(10,2)
);

-- 插入Server1本地的磁盘信息
INSERT INTO #DiskSpace
SELECT 
    @@SERVERNAME AS ServerName,
    drive AS DriveLetter,
    CAST(freespace AS INT) AS FreeSpaceMB,
    CAST(freespace AS DECIMAL(10,2))/1024 AS FreeSpaceGB
FROM master.dbo.xp_fixeddrives;

-- 插入Server2的磁盘信息
INSERT INTO #DiskSpace
SELECT 
    'Server2' AS ServerName,
    drive AS DriveLetter,
    CAST(freespace AS INT) AS FreeSpaceMB,
    CAST(freespace AS DECIMAL(10,2))/1024 AS FreeSpaceGB
FROM Server2.master.dbo.xp_fixeddrives;

-- 插入Server3的磁盘信息
INSERT INTO #DiskSpace
SELECT 
    'Server3' AS ServerName,
    drive AS DriveLetter,
    CAST(freespace AS INT) AS FreeSpaceMB,
    CAST(freespace AS DECIMAL(10,2))/1024 AS FreeSpaceGB
FROM Server3.master.dbo.xp_fixeddrives;

-- 生成HTML内容
DECLARE @HTML NVARCHAR(MAX);
SET @HTML = N'<html><head><style>
table {border-collapse: collapse; width: 100%;}
th, td {border: 1px solid #ddd; padding: 8px; text-align: left;}
th {background-color: #f2f2f2;}
.warning {background-color: #ffebee; color: #c62828;}
</style></head><body>
<h2>服务器磁盘空间报告</h2>
<table>
<tr><th>服务器名称</th><th>盘符</th><th>剩余空间(MB)</th><th>剩余空间(GB)</th></tr>' +
CAST((
    SELECT 
        td = ServerName, '',
        td = DriveLetter, '',
        td = FreeSpaceMB, '',
        td = FreeSpaceGB, '',
        -- 可选：给剩余空间不足10GB的行加警告样式
        [@class] = CASE WHEN FreeSpaceGB < 10 THEN 'warning' ELSE '' END
    FROM #DiskSpace
    ORDER BY ServerName, DriveLetter
    FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) + N'</table></body></html>';

-- 发送邮件（需要先配置Database Mail）
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'你的Database Mail配置文件名称', -- 替换成你的邮件配置文件
    @recipients = N'收件人邮箱@xxx.com', -- 替换成实际收件人
    @subject = N'每日服务器磁盘空间报告',
    @body = @HTML,
    @body_format = N'HTML';

-- 清理临时表
DROP TABLE #DiskSpace;

如果Server1还没设置Database Mail，你需要先完成配置：

• 打开SQL Server Management Studio，展开“管理”节点，右键“Database Mail”选择“配置Database Mail”
• 按照向导创建邮件账户（填写SMTP服务器、发件人邮箱等信息）
• 创建邮件配置文件，并把刚才的邮件账户添加进去
• 确保SQL Server代理服务的启动账户有访问SMTP服务器的权限

最后，在Server1上创建一个SQL Server代理作业来定期执行这个脚本：

1. 展开SQL Server代理，右键“作业”选择“新建作业”
2. 填写作业名称（比如“磁盘空间报告邮件作业”），设置所有者为有足够权限的账号
3. 切换到“步骤”选项卡，点击“新建”：
   • 步骤名称填“生成并发送磁盘报告”
   • 类型选择“Transact-SQL脚本(T-SQL)”
   • 数据库选择“master”（或者你想存放脚本的数据库）
   • 在“命令”框中粘贴刚才的完整脚本
4. 切换到“调度”选项卡，点击“新建”，设置作业的执行频率（比如每天凌晨2点执行一次）
5. 保存作业，你可以手动运行测试一下是否正常发送邮件

额外提示：

• 测试链接服务器是否正常：可以在Server1上执行SELECT * FROM Server2.master.dbo.sysdatabases，如果能返回数据说明链接正常
• 权限问题：确保SQL Server代理的启动账户有足够权限访问链接服务器，以及执行xp_fixeddrives和发送邮件的权限
• 可以自定义告警阈值：比如调整脚本里的FreeSpaceGB < 10条件，根据你的实际需求设置告警标准

内容的提问来源于stack exchange，提问作者Ahsan Mumtaz Abbasi