印刻数据库

外观

SQLServer 代理日志管理 ​

SQL Server 代理日志概述 ​

SQL Server 代理是 SQL Server 中的一个重要组件，用于自动化执行各种数据库任务，如备份、索引重建、数据导入导出等。SQL Server 代理日志记录了代理服务的运行状态、作业执行情况、错误信息和警告信息，对于监控作业执行、故障诊断和性能调优具有重要意义。

SQL Server 代理日志类型 ​

SQL Server 代理包含以下几种主要的日志：

1. SQL Server 代理错误日志 ​

  • 记录 SQL Server 代理服务的启动、停止、配置变更和错误信息
  • 默认位置：C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log
  • 默认文件名：SQLAGENT.OUT, SQLAGENT.1, SQLAGENT.2 等（数字表示归档日志）

2. 作业历史记录 ​

  • 记录作业的执行情况，包括作业开始时间、结束时间、执行结果和错误信息
  • 存储在 msdb 数据库的 sysjobhistory 表中
  • 可以通过 SSMS 或 T-SQL 查询

3. 作业步骤日志 ​

  • 记录作业中每个步骤的执行情况
  • 存储在 msdb 数据库的 sysjobhistory 表中
  • 包含步骤的开始时间、结束时间、执行结果、输出信息和错误信息

4. 操作日志 ​

  • 记录 SQL Server 代理操作员的操作情况
  • 存储在 msdb 数据库的 sysoperatorhistory 表中

SQL Server 代理日志配置 ​

配置代理错误日志 ​

1. 配置代理错误日志文件数量 ​

SQL Server 代理默认保留 10 个错误日志文件（当前日志 + 9 个归档日志）。可以通过以下方法调整：

2. 配置代理日志的详细程度 ​

可以通过以下方法调整代理日志的详细程度：

使用 SSMS 配置 ​
  1. 打开 SQL Server Management Studio (SSMS)
  2. 连接到 SQL Server 实例
  3. 展开 "SQL Server 代理" 节点
  4. 右键点击 "SQL Server 代理"，选择 "属性"
  5. 在 "高级" 选项卡中，调整 "日志级别"
  6. 日志级别选项：
    • 无：不记录任何日志
    • 错误：只记录错误信息
    • 警告：记录警告和错误信息
    • 信息：记录信息、警告和错误信息
    • 详细：记录所有信息
  7. 点击 "确定" 保存配置
使用 T-SQL 配置 ​
sql
-- 设置 SQL Server 代理日志级别为详细
USE msdb;
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level = 3;
GO
-- 日志级别说明：
-- 0 = 无
-- 1 = 错误
-- 2 = 警告
-- 3 = 信息
-- 4 = 详细

配置作业历史记录 ​

1. 配置作业历史记录保留策略 ​

可以通过以下方法配置作业历史记录的保留策略：

使用 SSMS 配置 ​
  1. 右键点击 "SQL Server 代理"，选择 "属性"
  2. 在 "作业系统" 选项卡中，调整以下设置：
    • 每个作业的最大历史记录行数
    • 要保留的最大作业历史记录行数
    • 历史记录日志选项
  3. 点击 "确定" 保存配置
使用 T-SQL 配置 ​
sql
-- 设置每个作业的最大历史记录行数为 100，要保留的最大作业历史记录行数为 1000
USE msdb;
GO
EXEC msdb.dbo.sp_set_sqlagent_properties 
    @jobhistory_max_rows_per_job = 100,
    @jobhistory_max_rows = 1000;
GO

2. 配置单个作业的历史记录 ​

可以为单个作业配置特定的历史记录保留策略：

使用 SSMS 配置 ​
  1. 展开 "SQL Server 代理" > "作业" 节点
  2. 右键点击要配置的作业，选择 "属性"
  3. 在 "历史记录" 选项卡中，调整以下设置：
    • 为该作业限制历史记录行数
    • 包括步骤输出
  4. 点击 "确定" 保存配置
使用 T-SQL 配置 ​
sql
-- 为作业 "BackupDB" 设置历史记录行数限制为 50
USE msdb;
GO
EXEC msdb.dbo.sp_update_job 
    @job_name = N'BackupDB',
    @max_history_rows = 50,
    @category_name = N'[Uncategorized (Local)]';
GO

SQL Server 代理日志查看方法 ​

1. 查看代理错误日志 ​

使用 SSMS 查看 ​

  1. 展开 "SQL Server 代理" 节点
  2. 右键点击 "错误日志"，选择 "查看代理日志"
  3. 在 "日志文件查看器" 中查看代理错误日志
  4. 可以使用过滤功能查找特定事件

使用 T-SQL 查看 ​

sql
-- 查看 SQL Server 代理错误日志的前 100 行
EXEC xp_readerrorlog 0, 2, NULL, NULL, NULL, NULL, 'DESC';
-- 参数说明：
-- 第一个参数：日志文件号（0=当前，1=归档1，2=归档2，以此类推）
-- 第二个参数：日志类型（1=错误日志，2=SQL Agent日志）

使用 PowerShell 查看 ​

powershell
# 导入 SQL Server 模块
Import-Module SQLPS -DisableNameChecking

# 连接到 SQL Server 实例
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"

# 查看 SQL Server 代理错误日志
$server.JobServer.ErrorLog.LogEntries | Select-Object -First 100

2. 查看作业历史记录 ​

使用 SSMS 查看 ​

  1. 展开 "SQL Server 代理" > "作业" 节点
  2. 右键点击要查看的作业，选择 "查看历史记录"
  3. 在 "日志文件查看器" 中查看作业历史记录
  4. 可以展开作业步骤，查看每个步骤的详细执行情况

使用 T-SQL 查看 ​

sql
-- 查看所有作业的历史记录
SELECT 
    j.name AS JobName,
    h.step_id AS StepID,
    h.step_name AS StepName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
    CASE h.run_status 
        WHEN 0 THEN '失败' 
        WHEN 1 THEN '成功' 
        WHEN 2 THEN '重试' 
        WHEN 3 THEN '取消' 
        WHEN 4 THEN '正在运行' 
    END AS RunStatus,
    h.message AS Message,
    h.duration AS Duration
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
ORDER BY 
    RunDateTime DESC;

-- 查看特定作业的历史记录
SELECT 
    h.step_id AS StepID,
    h.step_name AS StepName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
    CASE h.run_status 
        WHEN 0 THEN '失败' 
        WHEN 1 THEN '成功' 
        WHEN 2 THEN '重试' 
        WHEN 3 THEN '取消' 
        WHEN 4 THEN '正在运行' 
    END AS RunStatus,
    h.message AS Message,
    h.duration AS Duration
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE 
    j.name = N'BackupDB'
ORDER BY 
    RunDateTime DESC;

使用 PowerShell 查看 ​

powershell
# 查看所有作业的历史记录
Get-SqlAgentJobHistory -ServerInstance "localhost" | Select-Object -First 100

# 查看特定作业的历史记录
Get-SqlAgentJobHistory -ServerInstance "localhost" -JobName "BackupDB" | Select-Object -First 50

3. 查看作业步骤输出 ​

使用 SSMS 查看 ​

  1. 在 "日志文件查看器" 中，选择要查看的作业历史记录
  2. 点击 "查看步骤详细信息" 按钮
  3. 在弹出的对话框中查看作业步骤的详细输出

使用 T-SQL 查看 ​

sql
-- 查看作业步骤输出
SELECT 
    j.name AS JobName,
    h.step_id AS StepID,
    h.step_name AS StepName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
    h.output AS StepOutput
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE 
    h.output IS NOT NULL
ORDER BY 
    RunDateTime DESC;

SQL Server 代理日志分析 ​

常见代理日志错误分析 ​

1. 作业执行失败 ​

  • 错误信息："作业 'BackupDB' 执行失败。"
  • 常见原因：
    • 作业步骤中的 T-SQL 语句错误
    • 权限不足
    • 资源不足（如磁盘空间不足）
    • 依赖的对象不存在
  • 分析方法：
    • 查看作业历史记录中的详细错误信息
    • 检查作业步骤的输出
    • 检查相关的系统日志和错误日志

2. SQL Server 代理服务无法启动 ​

  • 错误信息："SQL Server 代理服务无法启动。"
  • 常见原因：
    • 服务账户权限不足
    • 依赖的 SQL Server 服务未启动
    • 代理配置错误
  • 分析方法：
    • 查看 Windows 事件日志中的详细错误信息
    • 检查 SQL Server 服务状态
    • 检查代理服务账户的权限

3. 作业调度失败 ​

  • 错误信息："作业 'BackupDB' 调度失败。"
  • 常见原因：
    • 调度配置错误
    • 作业已禁用
    • 代理服务未运行
  • 分析方法：
    • 检查作业的调度配置
    • 检查作业的启用状态
    • 检查 SQL Server 代理服务状态

代理日志分析技巧 ​

  1. 关注作业执行结果：重点关注失败的作业，及时处理
  2. 分析作业执行时间：检查作业执行时间是否异常，如突然变长
  3. 查看作业依赖关系：了解作业之间的依赖关系，便于分析连锁问题
  4. 使用过滤和搜索：利用 SSMS 或 T-SQL 的过滤功能，快速定位特定作业或错误
  5. 定期归档和清理：定期清理旧的作业历史记录，避免 msdb 数据库过大
  6. 自动化监控：配置自动化工具监控作业执行情况，及时发现问题

SQL Server 代理日志监控与告警 ​

配置作业执行告警 ​

可以通过以下方法配置作业执行告警：

1. 使用 SQL Server 代理警报 ​

sql
-- 创建作业失败告警
USE msdb;
GO
EXEC msdb.dbo.sp_add_alert 
    @name = N'作业执行失败告警', 
    @message_id = 0, 
    @severity = 0, 
    @enabled = 1, 
    @delay_between_responses = 300, 
    @include_event_description_in = 1,
    @category_name = N'作业',
    @performance_condition = N'SQLAgent:Jobs|Failed jobs/sec|_Total|>|0',
    @job_id = N'00000000-0000-0000-0000-000000000000';
GO

-- 添加操作员通知
USE msdb;
GO
EXEC msdb.dbo.sp_add_notification 
    @alert_name = N'作业执行失败告警', 
    @operator_name = N'DBA 团队', 
    @notification_method = 1; -- 1 = 邮件, 2 = 寻呼机, 4 = NetSend
GO

2. 使用 T-SQL 脚本监控 ​

sql
-- 监控失败的作业
USE msdb;
GO
DECLARE @FailedJobs TABLE (
    JobName VARCHAR(100),
    StepName VARCHAR(100),
    RunDateTime DATETIME,
    ErrorMessage VARCHAR(MAX)
);

INSERT INTO @FailedJobs
SELECT 
    j.name AS JobName,
    h.step_name AS StepName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
    h.message AS ErrorMessage
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE 
    h.run_status = 0 -- 0 = 失败
    AND msdb.dbo.agent_datetime(h.run_date, h.run_time) > DATEADD(HOUR, -1, GETDATE());

IF EXISTS (SELECT * FROM @FailedJobs)
BEGIN
    -- 发送告警邮件
    EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = N'DBAdminProfile',
        @recipients = N'dba-team@example.com',
        @subject = N'[告警] SQL Server 作业执行失败',
        @body_format = N'HTML',
        @body = (SELECT 
                    '<h2>失败的作业列表：</h2>' +
                    '<table border="1" cellspacing="0" cellpadding="5">' +
                    '<tr><th>作业名称</th><th>步骤名称</th><th>执行时间</th><th>错误信息</th></tr>' +
                    CAST((SELECT 
                            '<tr>' +
                            '<td>' + JobName + '</td>' +
                            '<td>' + StepName + '</td>' +
                            '<td>' + CONVERT(VARCHAR(20), RunDateTime, 120) + '</td>' +
                            '<td>' + ErrorMessage + '</td>' +
                            '</tr>'
                         FROM @FailedJobs
                         FOR XML PATH('')) AS VARCHAR(MAX)) +
                    '</table>');
END
GO

3. 使用 PowerShell 脚本监控 ​

powershell
# 导入 SQL Server 模块
Import-Module SQLPS -DisableNameChecking

# 连接到 SQL Server 实例
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"

# 获取最近 1 小时内失败的作业
$failedJobs = $server.JobServer.Jobs | ForEach-Object {
    $job = $_
    $job.HistoryEntries | Where-Object {
        $_.RunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobHistoryStatus]::Failed -and
        $_.RunDate -gt (Get-Date).AddHours(-1)
    } | Select-Object @{
        Name = "JobName";
        Expression = { $job.Name }
    }, StepName, RunDate, Message
}

# 如果有失败的作业，发送告警邮件
if ($failedJobs.Count -gt 0) {
    $body = "<h2>失败的作业列表：</h2>"
    $body += "<table border='1' cellspacing='0' cellpadding='5'>"
    $body += "<tr><th>作业名称</th><th>步骤名称</th><th>执行时间</th><th>错误信息</th></tr>"
    
    foreach ($job in $failedJobs) {
        $body += "<tr>"
        $body += "<td>$($job.JobName)</td>"
        $body += "<td>$($job.StepName)</td>"
        $body += "<td>$($job.RunDate.ToString('yyyy-MM-dd HH:mm:ss'))</td>"
        $body += "<td>$($job.Message)</td>"
        $body += "</tr>"
    }
    
    $body += "</table>"
    
    # 发送告警邮件
    Send-MailMessage -From "sqlagent@example.com" `
                    -To "dba-team@example.com" `
                    -Subject "[告警] SQL Server 作业执行失败" `
                    -Body $body `
                    -BodyAsHtml `
                    -SmtpServer "smtp.example.com"
}

最佳实践 ​

1. 配置最佳实践 ​

  • 合理设置日志级别：根据监控需求，设置合适的代理日志级别，避免日志过于冗余
  • 配置作业历史记录保留策略：根据作业数量和执行频率，设置合理的历史记录保留策略
  • 启用作业步骤输出：对于重要的作业，启用步骤输出，便于故障诊断
  • 定期备份 msdb 数据库：msdb 数据库包含作业历史记录等重要信息，应定期备份

2. 查看和分析最佳实践 ​

  • 定期查看作业执行情况：每天至少查看一次作业执行情况，及时发现失败的作业
  • 分析作业执行趋势：关注作业执行时间的变化，及时发现性能问题
  • 建立作业基线：了解正常情况下的作业执行情况，便于识别异常
  • 使用集中式日志管理：将多个 SQL Server 实例的代理日志集中管理，便于统一分析

3. 监控和告警最佳实践 ​

  • 配置作业执行告警：对于重要的作业，配置执行失败告警
  • 监控代理服务状态：确保 SQL Server 代理服务正常运行
  • 实现自动化监控：使用脚本或第三方工具，实现作业执行情况的自动化监控
  • 建立作业响应流程：明确作业失败的响应责任和流程

4. 性能优化最佳实践 ​

  • 定期清理作业历史记录：避免 msdb 数据库过大，影响性能
  • 优化作业调度：避免多个重要作业同时执行，造成资源竞争
  • 使用作业类别管理作业：将作业分类管理，便于组织和维护
  • 定期检查作业依赖关系：确保作业之间的依赖关系正确

版本差异 ​

SQL Server 版本SQL Agent 日志功能差异
SQL Server 2008/2008 R2基本的代理日志功能，支持查看作业历史记录
SQL Server 2012增强了作业历史记录的查看和过滤功能
SQL Server 2014引入了作业步骤输出的增强功能
SQL Server 2016增强了代理日志的安全性，支持加密
SQL Server 2017增强了 Linux 平台的代理日志支持
SQL Server 2019引入了作业执行统计信息的增强功能
SQL Server 2022增强了 Azure Arc 集成，支持将代理日志发送到 Azure Monitor

常见问题（FAQ） ​

Q: 如何清理旧的作业历史记录？ ​

A: 可以使用以下方法清理旧的作业历史记录：

  1. 使用 SSMS 配置作业历史记录保留策略
  2. 使用 T-SQL 脚本手动清理：
    sql
    -- 清理所有作业的历史记录
EXEC msdb.dbo.sp_purge_jobhistory;

-- 清理特定作业的历史记录
EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'BackupDB';

-- 清理指定日期之前的作业历史记录
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = '2023-01-01';
  3. 配置 SQL Server 代理属性中的历史记录保留设置

Q: 作业历史记录不完整怎么办？ ​

A: 可能的原因和解决方法：

  1. 作业历史记录保留策略设置过小：调整保留策略，增加保留的历史记录行数
  2. 作业步骤输出未启用：启用作业步骤输出选项
  3. msdb 数据库空间不足：扩展 msdb 数据库或清理旧的历史记录
  4. SQL Server 代理服务未正常运行：检查代理服务状态

Q: 如何监控多个 SQL Server 实例的作业执行情况？ ​

A: 可以使用以下方法：

  1. 使用 SSMS 的多服务器查询功能，同时查询多个实例的作业历史记录
  2. 使用 PowerShell 脚本自动化收集和分析多个实例的作业执行情况
  3. 使用第三方监控工具，如 Zabbix、SolarWinds 或 Redgate SQL Monitor
  4. 配置集中式日志管理系统，如 ELK Stack 或 Splunk

Q: 如何查看作业执行的详细输出？ ​

A: 可以通过以下方法查看作业执行的详细输出：

  1. 在 SSMS 中，查看作业历史记录，点击 "查看步骤详细信息"
  2. 使用 T-SQL 查询 msdb.dbo.sysjobhistory 表中的 output 列
  3. 在作业步骤中配置输出到文件，然后查看输出文件

Q: 如何优化 SQL Server 代理的性能？ ​

A: 可以采取以下措施优化 SQL Server 代理的性能：

  1. 定期清理作业历史记录，避免 msdb 数据库过大
  2. 优化作业调度，避免多个重要作业同时执行
  3. 合理设置代理日志级别，减少日志写入开销
  4. 确保 msdb 数据库有足够的空间和良好的性能
  5. 使用合适的服务账户，确保代理服务有足够的权限但不过度授权

案例分析 ​

案例一：作业执行失败告警 ​

问题描述：备份作业 "BackupDB" 执行失败，未收到告警通知

分析过程：

  1. 查看作业历史记录，发现作业执行失败，错误信息为 "磁盘空间不足"
  2. 检查告警配置，发现未为该作业配置执行失败告警
  3. 检查 SQL Server 代理服务状态，确认服务正常运行
  4. 检查 msdb 数据库空间，发现空间充足

解决方案：

  1. 为备份作业配置执行失败告警
  2. 增加备份目标磁盘的空间
  3. 配置磁盘空间监控，避免类似问题再次发生
  4. 调整作业历史记录保留策略，增加保留的历史记录行数

案例二：作业执行时间过长 ​

问题描述：索引重建作业 "RebuildIndexes" 的执行时间从 30 分钟增加到 2 小时

分析过程：

  1. 查看作业历史记录，确认作业执行时间确实增加
  2. 检查数据库大小，发现数据库大小增加了 50%
  3. 检查系统资源使用情况，发现 CPU 和内存使用率正常
  4. 检查磁盘 I/O，发现磁盘读写延迟增加

解决方案：

  1. 优化索引重建作业，使用分区索引重建或在线索引重建
  2. 调整作业调度，在系统负载较低时执行
  3. 升级存储设备，提高磁盘 I/O 性能
  4. 考虑使用增量索引重建，减少每次重建的时间

总结 ​

SQL Server 代理日志管理是数据库运维中的重要组成部分，它能够帮助 DBA 监控作业执行情况、诊断故障和优化性能。通过合理配置代理日志、定期查看和分析日志、配置自动化监控和告警，可以确保 SQL Server 代理的正常运行，提高数据库运维的效率和可靠性。

DBA 应该掌握 SQL Server 代理日志的配置、查看和分析方法，建立完善的代理日志管理体系，为数据库的稳定运行提供保障。