Welcome back. If you want to think like a real DBA, you must think in repeatable automation.
DBAs do not manually run the same tasks every day forever. They build reliable, auditable automation that runs on schedule,
logs results, and alerts the right people when something fails.
In SQL Server, the most common automation engine is SQL Server Agent.
When DBAs say “the job failed,” they usually mean “a SQL Server Agent job failed.”
Lesson description: You will learn what SQL Server Agent is, how jobs are structured (steps, schedules, history),
what operators are (who gets notified), and how DBAs design job schedules safely (not during peak load, predictable, with retries).
You will practice creating a simple DBA-style job, reviewing job history, and building a basic alerting path so failures do not go unnoticed.
SQL Server Agent is a service that executes scheduled tasks on your SQL Server instance.
It can run:
DBAs use Agent for:
DBA mindset: If SQL Server Agent is down, your backups can silently stop. That is a serious risk.
A job-ready DBA monitors the Agent service and job outcomes.
A job is a container for one or more actions you want automated.
Example: “Nightly Backups” or “DBCC CHECKDB Weekly.”
A job contains one or more steps. Each step has:
DBA best practice: Steps should be small, clear, and easy to troubleshoot.
Avoid “one huge step that does everything” unless you have strong logging.
A schedule controls when the job runs:
daily, hourly, weekly, at startup, or on a specific calendar plan.
DBA best practice: Schedules should match business reality:
SQL Server Agent maintains job history, including run duration, outcome, and error messages.
DBAs use job history to troubleshoot failures and prove compliance (“Yes, backups ran successfully.”).
An operator represents a person or team that receives notifications.
SQL Server Agent can notify operators via email (Database Mail), or other methods depending on configuration.
DBA reality: A failed backup that nobody sees is as bad as no backup at all.
Operators + notifications are how DBAs avoid silent failure.
In many environments:
A DBA-designed job step should not cause damage if it runs twice.
Example: a log cleanup step should only delete files older than X days, not “delete all backups.”
DBAs produce evidence. A job should:
Transient failures happen: brief network hiccup, a file lock, a short disk spike.
DBAs often configure retries for steps that can safely retry.
DBA best practice: Use a small number of retries with a reasonable delay,
then alert the operator if it still fails.
If a backup job overlaps with an index maintenance job, you can create I/O contention and timeouts.
DBAs build schedules that respect resource usage and maintain windows.
In SSMS, you can see SQL Server Agent under the instance node.
If it is stopped, jobs will not run.
You can also check service status from SQL Server:
-- Shows Agent service state if permissions allow
DECLARE @AgentStatus TABLE (
ServerName sysname,
ServiceName nvarchar(200),
Status nvarchar(200)
);
INSERT INTO @AgentStatus
EXEC xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent';
SELECT * FROM @AgentStatus;
Save output to:
C:\DBA\Baselines\InstanceConfig\agent_status_YYYYMMDD_HHMM.txt
DBAs check job history to answer:
This query lists recent job executions (success/fail) from msdb:
USE msdb;
GO
;WITH JobHistory AS
(
SELECT
j.name AS job_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.run_duration,
h.run_status,
h.message,
ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS rn
FROM dbo.sysjobs AS j
INNER JOIN dbo.sysjobhistory AS h
ON j.job_id = h.job_id
WHERE h.step_id = 0 -- step_id=0 is the overall job outcome row
)
SELECT TOP (25)
job_name,
run_status,
run_date,
run_time,
run_duration,
message
FROM JobHistory
WHERE rn = 1
ORDER BY run_date DESC, run_time DESC;
run_status meaning: 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, 4 = In Progress
Save output to:
C:\DBA\Baselines\InstanceConfig\job_history_snapshot_YYYYMMDD_HHMM.txt
You will create a job that captures a small health snapshot into a table.
This teaches you the real DBA pattern: schedule + logging + evidence.
-- Create a small utility DB for DBA logging (lab-safe)
IF DB_ID('DBAUtility') IS NULL
BEGIN
CREATE DATABASE DBAUtility;
END
GO
USE DBAUtility;
GO
IF OBJECT_ID('dbo.InstanceHealthSnapshot','U') IS NULL
BEGIN
CREATE TABLE dbo.InstanceHealthSnapshot
(
snapshot_id int IDENTITY(1,1) PRIMARY KEY,
snapshot_time datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
sqlserver_start datetime2(0) NULL,
cpu_count int NULL,
schedulers_online int NULL,
total_server_memory_mb bigint NULL,
target_server_memory_mb bigint NULL
);
END
GO
USE DBAUtility;
GO
DECLARE @sqlserver_start datetime2(0) =
(
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info
);
DECLARE @cpu_count int =
(
SELECT cpu_count
FROM sys.dm_os_sys_info
);
DECLARE @schedulers_online int =
(
SELECT COUNT(*)
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
);
DECLARE @total_server_memory_mb bigint =
(
SELECT total_physical_memory_kb / 1024
FROM sys.dm_os_sys_memory
);
DECLARE @target_server_memory_mb bigint =
(
SELECT (cntr_value / 1024)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Target Server Memory (KB)'
AND object_name LIKE '%Memory Manager%'
);
INSERT dbo.InstanceHealthSnapshot
(
sqlserver_start,
cpu_count,
schedulers_online,
total_server_memory_mb,
target_server_memory_mb
)
VALUES
(
@sqlserver_start,
@cpu_count,
@schedulers_online,
@total_server_memory_mb,
@target_server_memory_mb
);
How to add this as a job: In SSMS:
USE DBAUtility; GO SELECT TOP (25) * FROM dbo.InstanceHealthSnapshot ORDER BY snapshot_id DESC;
In production, DBAs configure Database Mail and then create an Operator so job failures email a team inbox.
In your lab, you may not have SMTP configured yet. That is okay—the important part is understanding the flow:
DBA best practice: Notify on failure for critical jobs (backups, integrity checks).
Avoid notifying on success for every job (notification spam).
agent_status_YYYYMMDD_HHMM.txtjob_history_snapshot_YYYYMMDD_HHMM.txtDBAUtility.dbo.InstanceHealthSnapshot.Next, you will learn which database options commonly cause performance problems and instability,
which ones DBAs standardize, and how to audit an instance for “dangerous defaults.”
Not a member yet? Register now
Are you a member? Login now