SQL Server Agent & Jobs

SQL Server Agent Overview

SQL Server Agent là Windows Service (SQLAGENT.EXE) chịu trách nhiệm automation trong SQL Server: chạy scheduled jobs, phản hồi alerts, gửi notifications. Là backbone của mọi tác vụ tự động hóa trong SQL Server.

-- Kiểm tra SQL Agent service status
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent';

-- Hoặc dùng sys.dm_server_services
SELECT servicename, status_desc, startup_type_desc, last_startup_time
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%';

Thành phần của SQL Server Agent

Jobs

Tạo Job bằng T-SQL

-- Bước 1: Tạo Job
USE msdb;
EXEC sp_add_job
    @job_name        = N'Daily_Index_Maintenance',
    @enabled         = 1,
    @description     = N'Daily index rebuild and reorganize for all user tables',
    @category_name   = N'Database Maintenance',
    @owner_login_name = N'sa';

-- Bước 2: Thêm Job Step
EXEC sp_add_jobstep
    @job_name         = N'Daily_Index_Maintenance',
    @step_name        = N'Rebuild Fragmented Indexes',
    @step_id          = 1,
    @subsystem        = N'TSQL',  -- T-SQL step
    @command          = N'
EXEC master.sys.sp_MSforeachdb ''
    IF DATABASEPROPERTYEX(''''?'''', ''''Status'''') = ''''ONLINE''''
    AND ''''?'''' NOT IN (''''master'''', ''''model'''', ''''msdb'''', ''''tempdb'''')
    BEGIN
        USE [?];
        EXEC dbo.IndexMaintenance;
    END
'';',
    @database_name    = N'master',
    @on_success_action = 1,  -- 1=Quit with success, 2=Quit with failure, 3=Go to next step, 4=Go to step
    @on_fail_action    = 2,  -- Quit with failure
    @retry_attempts    = 2,
    @retry_interval    = 5;  -- Minutes

-- Bước 3: Thêm Schedule
EXEC sp_add_schedule
    @schedule_name        = N'Daily_2AM',
    @freq_type            = 4,          -- 4=Daily, 8=Weekly, 16=Monthly
    @freq_interval        = 1,          -- Every 1 day
    @active_start_time    = 20000,      -- 02:00:00 (HHMMSS format)
    @active_end_time      = 235959,     -- 23:59:59
    @active_start_date    = 20240101;   -- YYYYMMDD

-- Bước 4: Gán Schedule vào Job
EXEC sp_attach_schedule
    @job_name     = N'Daily_Index_Maintenance',
    @schedule_name = N'Daily_2AM';

-- Bước 5: Đăng ký job với server
EXEC sp_add_jobserver
    @job_name   = N'Daily_Index_Maintenance',
    @server_name = N'(local)';

Xóa Job

EXEC sp_delete_job @job_name = N'Daily_Index_Maintenance';
-- Hoặc
EXEC sp_delete_job @job_id = '12345678-1234-1234-1234-123456789012';

Job Steps và Subsystems

Các loại Job Step (Subsystems)

-- PowerShell Job Step
EXEC sp_add_jobstep
    @job_name    = N'Deploy_SSIS_Package',
    @step_name   = N'Run PowerShell Deployment',
    @subsystem   = N'PowerShell',
    @command     = N'
$filePath = "C:\SSIS\MyPackage.ispac"
$catalog = "SSISDB"
# ... PowerShell deployment script
',
    @on_success_action = 1,
    @on_fail_action    = 2;

-- CmdExec Job Step (dùng với Proxy để tránh chạy với SQL Agent account)
EXEC sp_add_jobstep
    @job_name      = N'Backup_Compress',
    @step_name     = N'Compress Backup Files',
    @subsystem     = N'CmdExec',
    @command       = N'"C:\7zip\7z.exe" a -t7z "D:\Backups\backup.7z" "D:\Backups\*.bak"',
    @proxy_name    = N'BackupProxy',  -- Chạy dưới identity của proxy
    @on_success_action = 1;

Schedules

Các loại Schedule

-- One-time (chạy một lần)
EXEC sp_add_schedule
    @schedule_name     = N'One_Time_Migration',
    @freq_type         = 1,          -- 1=Once
    @active_start_date = 20250401,   -- Run date
    @active_start_time = 020000;     -- 02:00:00

-- Daily (mỗi ngày)
EXEC sp_add_schedule
    @schedule_name     = N'Every_Day_3AM',
    @freq_type         = 4,          -- 4=Daily
    @freq_interval     = 1,          -- Every 1 day
    @active_start_time = 030000;

-- Weekly (mỗi tuần - ví dụ: thứ 2, thứ 4, thứ 6)
EXEC sp_add_schedule
    @schedule_name     = N'MWF_6AM',
    @freq_type         = 8,          -- 8=Weekly
    @freq_interval     = 42,         -- Bit mask: Mon=2, Wed=8, Fri=32 → 2+8+32=42
    @freq_recurrence_factor = 1,     -- Every 1 week
    @active_start_time = 060000;

-- Monthly (ngày đầu mỗi tháng)
EXEC sp_add_schedule
    @schedule_name     = N'Monthly_First_Day',
    @freq_type         = 16,         -- 16=Monthly
    @freq_interval     = 1,          -- Day 1 of month
    @freq_recurrence_factor = 1,     -- Every 1 month
    @active_start_time = 010000;

-- Recurring intraday (mỗi 15 phút từ 8AM đến 10PM)
EXEC sp_add_schedule
    @schedule_name          = N'Every_15min_8AM_to_10PM',
    @freq_type              = 4,          -- Daily
    @freq_interval          = 1,
    @freq_subday_type       = 4,          -- 4=Minutes
    @freq_subday_interval   = 15,         -- Every 15 minutes
    @active_start_time      = 080000,     -- 08:00:00
    @active_end_time        = 220000;     -- 22:00:00

freq_interval Bitmask cho Weekly Schedule

Alerts

Performance Condition Alert

-- Alert khi CPU vượt 90%
EXEC sp_add_alert
    @name                       = N'High CPU Usage',
    @alert_type                 = 2,           -- 2=SQL Server performance condition
    @performance_condition      = N'SQLServer:Resource Pool Stats|CPU usage %|default|>|90',
    @job_name                   = N'Investigate_High_CPU',  -- Job để chạy khi alert trigger
    @notification_message       = N'CPU usage exceeded 90%! Investigate immediately.';

-- Alert khi error number cụ thể
EXEC sp_add_alert
    @name              = N'Severity 19-25 Errors',
    @alert_type        = 1,          -- 1=SQL Server event
    @severity          = 19,         -- Severity level (1-25), NULL để dùng message_id
    @notification_message = N'Fatal SQL Server error occurred',
    @job_name          = N'Capture_Error_Details';

-- Alert cho một error number cụ thể
EXEC sp_add_alert
    @name              = N'Deadlock Detected',
    @alert_type        = 1,
    @message_id        = 1205,       -- Deadlock error number
    @notification_message = N'Deadlock occurred';

-- WMI Event Alert
EXEC sp_add_alert
    @name              = N'Low Disk Space',
    @alert_type        = 3,          -- 3=WMI event
    @wmi_namespace     = N'\\.\root\cimv2',
    @wmi_query         = N'SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA ''Win32_LogicalDisk'' AND TargetInstance.DriveType = 3 AND TargetInstance.FreeSpace < 1073741824';

Operators

Operators là người nhận notification khi job fail hoặc alert trigger.

-- Tạo Operator
EXEC sp_add_operator
    @name                  = N'DBA Team',
    @enabled               = 1,
    @email_address         = N'dba-team@company.com',
    @weekday_pager_start_time = 090000,  -- Pager hours (legacy)
    @weekday_pager_end_time   = 180000;

-- Gửi notification khi Job fail/succeed
EXEC sp_add_notification
    @alert_name   = N'Deadlock Detected',
    @operator_name = N'DBA Team',
    @notification_method = 1;  -- 1=Email, 2=Pager, 4=Net Send (legacy)

-- Cấu hình notification cho job
EXEC sp_update_job
    @job_name          = N'Daily_Index_Maintenance',
    @notify_level_email  = 2,          -- 1=Success, 2=Failure, 3=Always
    @notify_email_operator_name = N'DBA Team',
    @notify_level_page   = 0,
    @notify_level_netsend = 0;

Database Mail

Cấu hình Database Mail

-- Bước 1: Enable Database Mail XPs
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

-- Bước 2: Tạo Mail Account
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name            = N'SQLServer_DBMail',
    @description             = N'SQL Server Database Mail Account',
    @email_address           = N'sqlserver@company.com',
    @display_name            = N'SQL Server Notifications',
    @replyto_address         = N'no-reply@company.com',
    @mailserver_name         = N'smtp.company.com',
    @mailserver_type         = N'SMTP',
    @port                    = 587,
    @username                = N'sqlserver@company.com',
    @password                = N'YourPassword',  -- Lưu an toàn trong credential
    @use_default_credentials = 0,
    @enable_ssl              = 1;

-- Bước 3: Tạo Mail Profile
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = N'DBA_Alerts',
    @description  = N'Profile for DBA alert notifications';

-- Bước 4: Gán Account vào Profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name  = N'DBA_Alerts',
    @account_name  = N'SQLServer_DBMail',
    @sequence_number = 1;

-- Bước 5: Grant public access (hoặc chỉ cho các principals cụ thể)
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name      = N'DBA_Alerts',
    @principal_name    = N'public',
    @is_default        = 1;

-- Test gửi email
EXEC msdb.dbo.sp_send_dbmail
    @profile_name  = N'DBA_Alerts',
    @recipients    = N'dba-team@company.com',
    @subject       = N'Test Database Mail',
    @body          = N'Database Mail is configured and working.';

Monitoring Jobs

Xem Job Status và History

-- Xem tất cả jobs và trạng thái
SELECT 
    j.name AS job_name,
    j.enabled,
    j.description,
    CASE j.last_run_outcome
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 3 THEN 'Canceled'
        WHEN 5 THEN 'Unknown'
    END AS last_run_outcome,
    j.last_run_date,
    j.last_run_time,
    j.next_run_date,
    j.next_run_time
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobservers js ON j.job_id = js.job_id
ORDER BY j.name;

-- Xem job history chi tiết
SELECT TOP 100
    j.name AS job_name,
    jh.step_name,
    jh.step_id,
    CASE jh.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
    END AS run_status,
    -- Convert run_date (int) và run_time (int) sang datetime
    CONVERT(DATETIME, 
        STUFF(STUFF(CAST(jh.run_date AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    ) AS run_datetime,
    jh.run_duration,  -- Format: HHMMSS
    jh.message
FROM msdb.dbo.sysjobhistory jh
JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE jh.run_status = 0  -- Chỉ failed
ORDER BY jh.instance_id DESC;

-- Xem jobs đang chạy hiện tại
SELECT 
    j.name AS job_name,
    ja.start_execution_date,
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS running_minutes,
    ja.last_executed_step_id,
    s.step_name AS current_step
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps s ON ja.job_id = s.job_id 
    AND ja.last_executed_step_id = s.step_id
WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
    AND ja.start_execution_date IS NOT NULL
    AND ja.stop_execution_date IS NULL
ORDER BY ja.start_execution_date;

Job Categories

-- Tạo Job Category
EXEC sp_add_category
    @class    = N'JOB',
    @type     = N'LOCAL',
    @name     = N'Database Maintenance';

EXEC sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'ETL Jobs';
EXEC sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'Monitoring';

-- Xem tất cả categories
SELECT name, category_id, category_type
FROM msdb.dbo.syscategories
WHERE category_class = 1  -- 1=JOB
ORDER BY name;

-- Gán job vào category
EXEC sp_update_job
    @job_name      = N'Daily_Index_Maintenance',
    @category_name = N'Database Maintenance';

Proxy Accounts

Proxy cho phép Job Steps chạy với identity khác (không phải SQL Agent service account), cần cho CmdExec, PowerShell, SSIS steps.

-- Bước 1: Tạo Windows credential
CREATE CREDENTIAL [BackupServiceCredential]
WITH IDENTITY = N'DOMAIN\svc_backup',
SECRET = N'StrongP@ssword';

-- Bước 2: Tạo Proxy dùng credential
EXEC msdb.dbo.sp_add_proxy
    @proxy_name          = N'BackupProxy',
    @credential_name     = N'BackupServiceCredential',
    @enabled             = 1,
    @description         = N'Proxy for backup service account';

-- Bước 3: Grant proxy access cho subsystems
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
    @proxy_name    = N'BackupProxy',
    @subsystem_id  = 3;   -- 3=CmdExec

-- Subsystem IDs: 2=ActiveScripting, 3=CmdExec, 9=SSIS, 12=PowerShell

-- Bước 4: Grant logins quyền dùng proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
    @login_name  = N'JobOwnerLogin',
    @proxy_name  = N'BackupProxy';

-- Xem proxies
SELECT p.name, p.enabled, c.name AS credential_name
FROM msdb.dbo.sysproxies p
JOIN sys.credentials c ON p.credential_id = c.credential_id;

Multi-Server Administration (MSX/TSX)

-- Master Server (MSX) setup
-- Tư duy: MSX quản lý multiple Target Servers (TSX)
-- Jobs tạo trên MSX sẽ được download và chạy trên các TSX

-- Enlist một server làm MSX
EXEC sp_msx_enlist @msx_server_name = N'MASTER_SERVER';

-- Tạo multi-server job
EXEC sp_add_job
    @job_name = N'All_Servers_Backup',
    @enabled  = 1;

EXEC sp_add_jobstep @job_name = N'All_Servers_Backup', ...;

-- Target specific servers hoặc tất cả
EXEC sp_add_jobserver
    @job_name   = N'All_Servers_Backup',
    @server_name = N'ALL';  -- Chạy trên tất cả target servers
    
-- Hoặc chỉ một server cụ thể
EXEC sp_add_jobserver
    @job_name   = N'All_Servers_Backup',
    @server_name = N'TARGET_SERVER_1';

Maintenance Plans và Best Practices

Ola Hallengren’s SQL Server Maintenance Solution

-- Ola Hallengren solution là best practice cho maintenance
-- Download từ: https://ola.hallengren.com/

-- Sau khi install, tạo jobs dùng stored procedures:

-- Index Maintenance (rebuild/reorganize based on fragmentation)
EXEC dbo.IndexOptimize
    @Databases        = 'USER_DATABASES',
    @FragmentationLow = NULL,           -- Không làm gì khi ít fragmentation
    @FragmentationMedium = 'INDEX_REORGANIZE',   -- Reorganize 5-30%
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  -- Rebuild >30%
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 1000,           -- Bỏ qua indexes nhỏ
    @SortInTempdb = 'Y',
    @MaxDOP = 4;

-- Statistics Update
EXEC dbo.IndexOptimize
    @Databases    = 'USER_DATABASES',
    @Indexes      = NULL,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y';

-- Database Integrity Check
EXEC dbo.DatabaseIntegrityCheck
    @Databases = 'USER_DATABASES',
    @CheckCommands = 'CHECKDB';

-- Database Backup
EXEC dbo.DatabaseBackup
    @Databases   = 'USER_DATABASES',
    @Directory   = 'D:\Backups',
    @BackupType  = 'FULL',
    @Verify      = 'Y',
    @Compress    = 'Y',
    @CleanupTime = 48;  -- Xóa backup cũ hơn 48 giờ

Common Automated Tasks

Backup Job

-- Full backup job step
EXEC sp_add_jobstep
    @job_name    = N'Full_Database_Backup',
    @step_name   = N'Backup User Databases',
    @subsystem   = N'TSQL',
    @command     = N'
DECLARE @BackupPath NVARCHAR(500) = N''D:\Backups\'';
DECLARE @FileName NVARCHAR(500);
DECLARE @DBName NVARCHAR(128);

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = ''ONLINE''
    AND name NOT IN (''tempdb'')
    AND is_read_only = 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FileName = @BackupPath + @DBName + ''_FULL_'' + 
        REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '':'', ''''), '' '', ''_'') + ''.bak'';
    
    BACKUP DATABASE @DBName 
    TO DISK = @FileName
    WITH COMPRESSION, CHECKSUM, STATS = 10;
    
    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;
',
    @database_name = N'master';

DBCC CHECKDB Job

-- Chạy DBCC CHECKDB hàng tuần cuối tuần
EXEC sp_add_jobstep
    @job_name    = N'Weekly_Integrity_Check',
    @step_name   = N'DBCC CHECKDB All Databases',
    @subsystem   = N'TSQL',
    @command     = N'
EXEC sp_MSforeachdb ''
IF DATABASEPROPERTYEX(''''?'''', ''''Status'''') = ''''ONLINE''''
AND ''''?'''' NOT IN (''''tempdb'''')
BEGIN
    DBCC CHECKDB (''''?'''') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
END
'';';

Log Shipping Restore Job

-- Restore transaction log backup (phần của Log Shipping)
EXEC sp_add_jobstep
    @job_name    = N'LogShipping_Restore_YourDB',
    @step_name   = N'Restore Latest Log Backup',
    @subsystem   = N'TSQL',
    @command     = N'
DECLARE @LatestFile NVARCHAR(500);
SELECT TOP 1 @LatestFile = physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N''SourceDB''
    AND bs.type = N''L''
    AND bs.backup_finish_date > GETDATE() - 1
ORDER BY bs.backup_finish_date DESC;

IF @LatestFile IS NOT NULL
BEGIN
    RESTORE LOG YourDB
    FROM DISK = @LatestFile
    WITH STANDBY = N''D:\Standby\YourDB_standby.bak'', STATS = 10;
END;';

Q&A theo Cấp Độ

Junior Level

Q: SQL Server Agent là gì và tại sao quan trọng?

A: SQL Server Agent là Windows service tự động hóa các tác vụ trong SQL Server: chạy scheduled jobs (backup, maintenance, ETL), phản hồi alerts (disk space, high CPU, errors), gửi notifications qua Database Mail. Thiếu SQL Agent thì không thể có CDC capture jobs, log shipping, replication agents, database mail.

Q: Làm sao xem lịch sử chạy của một job và biết nó fail vì sao?

A:

-- Qua SSMS: SQL Server Agent → Jobs → Right-click → View History
-- Qua T-SQL:
SELECT TOP 50
    j.name, jh.step_name, 
    CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' END AS status,
    jh.message
FROM msdb.dbo.sysjobhistory jh
JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE j.name = 'YourJobName'
ORDER BY jh.instance_id DESC;

Q: Database Mail là gì và cần cấu hình gì để gửi được email?

A: Database Mail là component của SQL Server cho phép gửi email từ T-SQL (sp_send_dbmail). Cần cấu hình:

1. Enable Database Mail XPs qua sp_configure
2. Tạo Mail Account (SMTP server, port, credentials)
3. Tạo Mail Profile và gán Account vào Profile
4. Grant principals quyền dùng profile Test bằng EXEC msdb.dbo.sp_send_dbmail.

Mid Level

Q: Proxy Account trong SQL Server Agent là gì và khi nào cần dùng?

A: Proxy là identity context (Windows account) mà một job step chạy dưới đó, thay vì chạy dưới SQL Agent service account. Cần dùng khi:

• CmdExec hay PowerShell steps cần quyền Windows cụ thể
• SSIS package cần truy cập file share, network resource với specific credential
• Security principle of least privilege: không muốn SQL Agent service account có quá nhiều quyền

Cách tạo: CREATE CREDENTIAL → sp_add_proxy → sp_grant_proxy_to_subsystem → sp_grant_login_to_proxy.

Q: Giải thích các on_success_action và on_fail_action options trong job steps?

A: Mỗi step có 2 flow control actions:

• 1 = Quit with success (kết thúc job thành công)
• 2 = Quit with failure (kết thúc job thất bại)
• 3 = Go to next step (tiếp tục step tiếp theo)
• 4 = Go to step N (nhảy đến step cụ thể - dùng cho conditional logic)

Pattern phổ biến: Step 1 → success: Go to next, fail: Go to Step 5 (step cleanup/notification). Step 5 → always quit với failure. Cho phép tạo “workflow” phức tạp.

Senior Level

Q: Làm sao thiết kế một robust job framework với alerting, retry logic, và audit trail?

A: Framework đầy đủ cần:

1. Control table để track job execution:

CREATE TABLE dbo.JobExecutionLog (
    LogID         BIGINT IDENTITY PRIMARY KEY,
    JobName       NVARCHAR(128),
    StepName      NVARCHAR(128),
    StartTime     DATETIME2 DEFAULT SYSUTCDATETIME(),
    EndTime       DATETIME2,
    Status        VARCHAR(20),  -- Running, Success, Failed, Retrying
    ErrorMessage  NVARCHAR(MAX),
    RetryCount    INT DEFAULT 0,
    RowsProcessed BIGINT
);

2. Wrapper procedure với retry logic:

CREATE OR ALTER PROCEDURE dbo.usp_JobStepWrapper
    @ProcName  NVARCHAR(200),
    @MaxRetries INT = 3,
    @RetryDelaySeconds INT = 60
AS BEGIN
    DECLARE @Attempt INT = 0, @LogID BIGINT;
    
    INSERT INTO dbo.JobExecutionLog (JobName, Status) 
    VALUES (@ProcName, 'Running');
    SET @LogID = SCOPE_IDENTITY();
    
    WHILE @Attempt < @MaxRetries
    BEGIN
        SET @Attempt += 1;
        BEGIN TRY
            EXEC sp_executesql @ProcName;
            UPDATE dbo.JobExecutionLog 
            SET Status = 'Success', EndTime = SYSUTCDATETIME() 
            WHERE LogID = @LogID;
            RETURN;
        END TRY
        BEGIN CATCH
            UPDATE dbo.JobExecutionLog 
            SET Status = 'Retrying', ErrorMessage = ERROR_MESSAGE(), RetryCount = @Attempt
            WHERE LogID = @LogID;
            
            IF @Attempt < @MaxRetries
                WAITFOR DELAY @RetryDelaySeconds;
        END CATCH
    END
    
    -- All retries exhausted
    UPDATE dbo.JobExecutionLog SET Status = 'Failed' WHERE LogID = @LogID;
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA_Alerts',
        @recipients = 'dba@company.com',
        @subject = 'Job Failed: ' + @ProcName,
        @body = 'All retries exhausted.';
    RAISERROR('Job failed after all retries', 16, 1);
END;

3. Monitoring query:

-- Jobs chạy quá lâu, failed gần đây, hoặc chưa chạy đúng schedule
SELECT j.name, ja.start_execution_date, 
       DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS running_min
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.stop_execution_date IS NULL
    AND DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) > 60  -- Running > 1 hour
ORDER BY running_min DESC;

Q: Giải thích cách implement job dependencies (Job A phải chạy xong trước Job B)?

A: SQL Server Agent không có built-in job dependency mechanism. Các cách implement:

Option 1: Linked steps trong cùng job: Đặt tất cả logic vào một job với nhiều steps — đơn giản nhất.

Option 2: Check job completion trong step:

-- Ở đầu Job B, kiểm tra Job A đã complete hôm nay chưa
DECLARE @JobAStatus INT;
SELECT TOP 1 @JobAStatus = run_status
FROM msdb.dbo.sysjobhistory jh
JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE j.name = 'Job_A'
    AND run_status = 1  -- Success
    AND run_date = CONVERT(INT, CONVERT(VARCHAR, GETDATE(), 112))
ORDER BY instance_id DESC;

IF @JobAStatus != 1
    RAISERROR('Job A has not completed successfully today.', 16, 1);

Option 3: SQL Server Agent Tokens + Custom table: Job A update dbo.JobStatus sau khi complete. Job B kiểm tra table này.

Option 4: SSIS Pipeline: Dùng SSIS như orchestration engine với built-in precedence constraints.

Option 5: External orchestration: Azure Data Factory, Apache Airflow, hoặc SQL Server 2019+ với external job scheduler.