SQL Server Agent

SQL Server Agent Service (SQLSERVERAGENT)
│
├── Jobs ─────── Steps (T-SQL, SSIS, PowerShell, CmdExec, …)
│                └── Schedules (one-time, recurring, on-startup, on-idle)
│
├── Alerts ────── Error number / Severity / Performance condition / WMI event
│                └── → Response: run job, notify operators
│
├── Operators ─── Email / Net Send (deprecated) / Pager (deprecated)
│
└── Proxies ────── Credential-backed Windows identity for non-sysadmin steps

USE msdb;
GO

-- 1. Create the job
EXEC sp_add_job
    @job_name        = N'Nightly Index Maintenance',
    @description     = N'Rebuild/reorganize fragmented indexes using Ola Hallengren IndexOptimize',
    @category_name   = N'Database Maintenance',
    @owner_login_name = N'sa',            -- or a service account login
    @enabled         = 1,
    @notify_level_email   = 2,            -- 1=success, 2=failure, 3=always
    @notify_email_operator_name = N'DBA Team';

-- 2. Add a step
EXEC sp_add_jobstep
    @job_name        = N'Nightly Index Maintenance',
    @step_name       = N'Run IndexOptimize',
    @step_id         = 1,
    @subsystem       = N'TSQL',
    @command         = N'EXEC master.dbo.IndexOptimize
                            @Databases = ''USER_DATABASES'',
                            @FragmentationLow  = NULL,
                            @FragmentationMedium = ''INDEX_REORGANIZE'',
                            @FragmentationHigh   = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
                            @FragmentationLevel1 = 5,
                            @FragmentationLevel2 = 30,
                            @LogToTable = ''Y'';',
    @database_name   = N'master',
    @on_success_action = 1,              -- 1=quit with success
    @on_fail_action    = 2,              -- 2=quit with failure
    @retry_attempts  = 0,
    @retry_interval  = 0;

-- 3. Add a schedule (every night at 02:00)
EXEC sp_add_schedule
    @schedule_name   = N'Nightly 2AM',
    @freq_type       = 4,                -- 4=daily
    @freq_interval   = 1,               -- every 1 day
    @active_start_time = 020000;         -- 02:00:00 (HHMMSS integer)

-- 4. Attach the schedule to the job
EXEC sp_attach_schedule
    @job_name        = N'Nightly Index Maintenance',
    @schedule_name   = N'Nightly 2AM';

-- 5. Target the local server
EXEC sp_add_jobserver
    @job_name        = N'Nightly Index Maintenance',
    @server_name     = N'(LOCAL)';
GO

EXEC msdb.dbo.sp_start_job @job_name = N'Nightly Index Maintenance';

EXEC msdb.dbo.sp_stop_job @job_name = N'Nightly Index Maintenance';

EXEC msdb.dbo.sp_delete_job @job_name = N'Nightly Index Maintenance';

-- Step 1: Extract
EXEC sp_add_jobstep
    @job_name            = N'ETL Pipeline',
    @step_name           = N'Extract',
    @step_id             = 1,
    @subsystem           = N'TSQL',
    @command             = N'EXEC dbo.usp_Extract;',
    @database_name       = N'Staging',
    @on_success_action   = 3,            -- go to next step
    @on_fail_action      = 4,            -- go to step 3 (cleanup)
    @on_fail_step_id     = 3;

-- Step 2: Load
EXEC sp_add_jobstep
    @job_name            = N'ETL Pipeline',
    @step_name           = N'Load',
    @step_id             = 2,
    @subsystem           = N'TSQL',
    @command             = N'EXEC dbo.usp_Load;',
    @database_name       = N'DWH',
    @on_success_action   = 1,            -- quit success
    @on_fail_action      = 4,            -- go to step 3 (cleanup)
    @on_fail_step_id     = 3;

-- Step 3: Cleanup / rollback (always runs on failure path)
EXEC sp_add_jobstep
    @job_name            = N'ETL Pipeline',
    @step_name           = N'Cleanup on Failure',
    @step_id             = 3,
    @subsystem           = N'TSQL',
    @command             = N'EXEC dbo.usp_CleanupStagingOnFailure;',
    @database_name       = N'Staging',
    @on_success_action   = 2,            -- quit failure (so job is marked failed)
    @on_fail_action      = 2;

EXEC sp_add_schedule
    @schedule_name        = N'Every 15 min business hours',
    @freq_type            = 4,              -- daily
    @freq_interval        = 1,
    @freq_subday_type     = 4,              -- 4=minutes, 8=hours
    @freq_subday_interval = 15,
    @active_start_time    = 080000,         -- 08:00:00
    @active_end_time      = 180000;         -- 18:00:00

-- Detach a schedule from a job (schedule still exists)
EXEC sp_detach_schedule
    @job_name       = N'Some Job',
    @schedule_name  = N'Nightly 2AM';

-- Delete a schedule only if no jobs use it
EXEC sp_delete_schedule
    @schedule_name  = N'Nightly 2AM',
    @keep_unused_schedules = 0;

EXEC msdb.dbo.sp_add_alert
    @name                  = N'Alert: Error 823 (I/O Error)',
    @message_id            = 823,          -- specific error number
    @severity              = 0,            -- 0 = use message_id, not severity
    @enabled               = 1,
    @delay_between_responses = 900,        -- 15 min minimum between firings (seconds)
    @notification_message  = N'Disk I/O error 823 detected',
    @job_name              = N'',          -- optionally run a job
    @raise_snmp_trap       = 0;

EXEC msdb.dbo.sp_add_alert
    @name                  = N'Alert: Severity 16-19',
    @message_id            = 0,            -- 0 = use severity, not message_id
    @severity              = 16,           -- fires on severity 16 OR HIGHER up to 18
    @enabled               = 1,
    @delay_between_responses = 300;

EXEC msdb.dbo.sp_add_alert
    @name               = N'Alert: Low PLE',
    @message_id         = 0,
    @severity           = 0,
    @enabled            = 1,
    @performance_condition = N'SQLServer:Buffer Manager|Page life expectancy||<|300',
    -- format: object|counter|instance|comparator|value
    @delay_between_responses = 300;

EXEC msdb.dbo.sp_add_notification
    @alert_name     = N'Alert: Error 823 (I/O Error)',
    @operator_name  = N'DBA Team',
    @notification_method = 1;   -- 1=email, 2=pager, 4=net send; combine as bitmask

-- Requires Database Mail to be configured first
EXEC msdb.dbo.sp_add_operator
    @name                         = N'DBA Team',
    @enabled                      = 1,
    @email_address                = N'dba-team@company.com',
    @weekday_pager_start_time     = 090000,   -- ignored if no pager configured
    @weekday_pager_end_time       = 180000,
    @pager_days                   = 62;       -- Mon-Fri bitmask (2+4+8+16+32)

-- Set a "fail-safe" operator: notified if Agent can't contact normal operators
EXEC msdb.dbo.sp_update_agent_parameter
    @param_name  = N'FailSafeOperator',
    @param_value = N'DBA Team';

EXEC msdb.dbo.sp_delete_operator @name = N'DBA Team';

-- 1. Create a Windows credential
CREATE CREDENTIAL [CORP\svc-sqlagent]
    WITH IDENTITY = N'CORP\svc-sqlagent',
    SECRET = N'<windows-password>';

-- 2. Create a proxy backed by that credential
EXEC msdb.dbo.sp_add_proxy
    @proxy_name        = N'SSIS Proxy',
    @credential_name   = N'CORP\svc-sqlagent',
    @enabled           = 1;

-- 3. Grant the proxy permission to a subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
    @proxy_name    = N'SSIS Proxy',
    @subsystem_id  = 11;   -- 11=SSIS; see subsystem IDs below

-- 4. Grant a login the right to use the proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
    @login_name  = N'CORP\developer1',
    @proxy_name  = N'SSIS Proxy';

-- On the master server: make it an MSX
EXEC msdb.dbo.sp_msx_set_account @credential_name = N'MSX Credential';

-- On a target server: enlist it in the MSX
EXEC msdb.dbo.sp_msx_enlist
    @msx_server_name  = N'SQLMASTERSVR',
    @location         = N'Datacenter East';

-- On the master: create a multi-server job (targets all enlisted servers)
EXEC msdb.dbo.sp_add_jobserver
    @job_name    = N'Nightly Backups',
    @server_name = N'ALL';  -- sends to all enlisted target servers

SELECT
    j.name                                                AS job_name,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'Running'
    END                                                   AS status,
    -- Convert YYYYMMDD + HHMMSS integers to a datetime
    CONVERT(datetime,
        STUFF(STUFF(CAST(h.run_date AS varchar(8)), 7, 0, '-'), 5, 0, '-')
        + ' '
        + STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':')
    )                                                     AS run_start,
    -- run_duration is HHMMSS integer
    (h.run_duration / 10000 * 3600)
    + ((h.run_duration % 10000) / 100 * 60)
    + (h.run_duration % 100)                              AS duration_seconds,
    LEFT(h.message, 500)                                  AS message
FROM msdb.dbo.sysjobhistory  h
JOIN msdb.dbo.sysjobs        j ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(varchar(8), DATEADD(day, -7, GETDATE()), 112))
ORDER BY h.run_date DESC, h.run_time DESC;

SELECT
    j.name        AS job_name,
    h.step_name,
    CONVERT(datetime,
        STUFF(STUFF(CAST(h.run_date AS varchar(8)), 7, 0, '-'), 5, 0, '-')
        + ' '
        + STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':')
    )             AS run_start,
    LEFT(h.message, 500) AS failure_message
FROM msdb.dbo.sysjobhistory  h
JOIN msdb.dbo.sysjobs        j  ON j.job_id = h.job_id
WHERE h.run_status = 0               -- failed
  AND h.step_id   > 0               -- exclude job-level summary row (step_id=0)
  AND h.run_date  >= CONVERT(int, CONVERT(varchar(8), DATEADD(hour, -24, GETDATE()), 112))
ORDER BY h.run_date DESC, h.run_time DESC;

SELECT
    j.name           AS job_name,
    a.start_execution_date,
    DATEDIFF(second, a.start_execution_date, GETDATE()) AS running_seconds,
    ja.last_executed_step_id,
    ja.last_executed_step_date
FROM msdb.dbo.sysjobactivity  a
JOIN msdb.dbo.sysjobs          j  ON j.job_id = a.job_id
LEFT JOIN (
    SELECT job_id, last_executed_step_id = step_id,
           last_executed_step_date = MAX(run_requested_date)
    FROM msdb.dbo.sysjobhistory
    GROUP BY job_id, step_id
) ja ON ja.job_id = j.job_id
WHERE a.session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.syssessions
)
  AND a.start_execution_date IS NOT NULL
  AND a.stop_execution_date  IS NULL;

SELECT
    j.name           AS job_name,
    j.enabled,
    s.name           AS schedule_name,
    s.enabled        AS schedule_enabled,
    CASE s.freq_type
        WHEN 1   THEN 'One time'
        WHEN 4   THEN 'Daily every ' + CAST(s.freq_interval AS varchar) + ' day(s)'
        WHEN 8   THEN 'Weekly'
        WHEN 16  THEN 'Monthly day ' + CAST(s.freq_interval AS varchar)
        WHEN 32  THEN 'Monthly relative'
        WHEN 64  THEN 'Agent start'
        WHEN 128 THEN 'CPU idle'
    END              AS frequency,
    s.active_start_time
FROM msdb.dbo.sysjobs           j
JOIN msdb.dbo.sysjobschedules   js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules      s  ON s.schedule_id = js.schedule_id
ORDER BY j.name;

WITH LastRun AS (
    SELECT job_id,
           run_status,
           run_date,
           run_time,
           message,
           ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS rn
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0   -- job-level summary only
)
SELECT
    j.name         AS job_name,
    j.enabled,
    CASE lr.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        ELSE        'Unknown'
    END            AS last_status,
    CONVERT(datetime,
        STUFF(STUFF(CAST(lr.run_date AS varchar(8)), 7, 0, '-'), 5, 0, '-')
        + ' '
        + STUFF(STUFF(RIGHT('000000' + CAST(lr.run_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':')
    )              AS last_run,
    LEFT(lr.message, 200) AS message
FROM msdb.dbo.sysjobs j
LEFT JOIN LastRun      lr ON lr.job_id = j.job_id AND lr.rn = 1
WHERE j.enabled = 1
ORDER BY j.name;

-- If this returns a row, Agent is running
SELECT *
FROM sys.dm_server_services
WHERE servicename LIKE N'SQL Server Agent%';

EXEC msdb.dbo.sp_cycle_agent_errorlog;   -- rotate the log (new log file)

-- Read Agent error log (no T-SQL equivalent — read from filesystem or SSMS)
-- Log path: same directory as SQL Server error log, named SQLAGENT.OUT (plus numbered rollover files)

SELECT
    a.name          AS alert_name,
    a.occurrence_count,
    a.last_occurrence_date,
    a.last_occurrence_time,
    a.last_response_date,
    a.last_response_time
FROM msdb.dbo.sysalerts a
ORDER BY a.last_occurrence_date DESC, a.last_occurrence_time DESC;

-- View current history settings
EXEC msdb.dbo.sp_get_composite_job_info;

-- Change history limits (max rows per job and overall)
EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows           = 100000,  -- default: 1000 total
    @jobhistory_max_rows_per_job   = 1000;    -- default: 100 per job

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- ... work ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;   -- re-raise: non-zero exit → step fails
END CATCH;

IF NOT EXISTS (SELECT 1 FROM dbo.SomeTable WHERE ...)
BEGIN
    RAISERROR('Prerequisite check failed: no rows found in SomeTable', 16, 1);
    RETURN;
END;

DECLARE @rc int;
EXEC @rc = dbo.usp_SomeProc;
IF @rc <> 0
    RAISERROR('usp_SomeProc failed with return code %d', 16, 1, @rc);

-- Create elastic job agent (done once per resource group in Azure Portal or ARM)
-- Then use elastic jobs T-SQL (different stored procs than Agent):
EXEC jobs.sp_add_job @job_name = N'Nightly Maintenance';
EXEC jobs.sp_add_jobstep
    @job_name         = N'Nightly Maintenance',
    @command          = N'UPDATE STATISTICS dbo.Orders;',
    @target_group_name = N'All Prod Databases';
EXEC jobs.sp_start_job @job_name = N'Nightly Maintenance';

CREATE OR ALTER PROCEDURE dbo.usp_NotifyJobFailure
    @JobName     nvarchar(128),
    @StepName    nvarchar(128),
    @ErrorMsg    nvarchar(2048)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @body nvarchar(4000);
    SET @body = N'<h3>Job Failure Alert</h3>'
              + N'<b>Server:</b> ' + @@SERVERNAME + N'<br>'
              + N'<b>Job:</b> '    + @JobName + N'<br>'
              + N'<b>Step:</b> '   + @StepName + N'<br>'
              + N'<b>Time:</b> '   + CONVERT(varchar(25), GETDATE(), 120) + N'<br>'
              + N'<b>Error:</b> '  + @ErrorMsg;

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name  = N'Default',
        @recipients    = N'dba-team@company.com',
        @subject       = N'SQL Agent Job Failed: ' + @JobName,
        @body          = @body,
        @body_format   = N'HTML';
END;

-- Disable all backup jobs during migration
UPDATE msdb.dbo.sysjobs
SET enabled = 0
WHERE name LIKE N'%Backup%';

-- Re-enable afterwards
UPDATE msdb.dbo.sysjobs
SET enabled = 1
WHERE name LIKE N'%Backup%';

CREATE TRIGGER trg_AuditJobChanges
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    -- Agent changes fire through msdb stored procedures, not DDL events
    -- Use the sysjobs_view change_date column instead:
    -- SELECT * FROM msdb.dbo.sysjobs ORDER BY date_modified DESC
END;

IF NOT EXISTS (
    SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'My Job'
)
BEGIN
    EXEC msdb.dbo.sp_add_job @job_name = N'My Job', ...;
END;

SELECT
    j.name,
    j.enabled,
    SUSER_SNAME(j.owner_sid) AS owner
FROM msdb.dbo.sysjobs j
WHERE SUSER_SNAME(j.owner_sid) = N'CORP\svc-account'
ORDER BY j.name;

# Copy all Agent jobs from one server to another
Copy-DbaAgentJob -Source SQLOLD -Destination SQLNEW

# Export all jobs to JSON files for source control
Export-DbaAgentJob -SqlInstance SQLPROD -Path C:\AgentJobs\