What you will learn
SQL Server Agent runs jobs outside your interactive SSMS session. A maintenance plan is one way to orchestrate work, but a successful schedule definition is not evidence that its job runs. You will build a small integrity-check job with an observable outcome before introducing recurring execution.
Before you begin
Use an isolated SQL Server 2022 Developer lab on host1.sample.com and a current SQL Server Management Studio client. Developer edition is for nonproduction use. Connect with a dedicated lab administrator for setup; application access is tested separately. The examples use TutorialDB and synthetic rows only. GO is a client batch separator, not a Transact-SQL statement. Sample paths are on the database server, not your workstation. SQL Server Agent must be installed, running, and available in your edition; SQL Server Express does not include Agent. TutorialDB must exist. Use an authorized administrator to create this lab job and review its execution identity.
1. Create an initially disabled job
USE msdb;
GO
EXEC dbo.sp_add_job @job_name=N'TutorialDB integrity',@enabled=0;
EXEC dbo.sp_add_jobstep @job_name=N'TutorialDB integrity',
@step_name=N'Check database',@subsystem=N'TSQL',@database_name=N'master',
@command=N'DBCC CHECKDB (N''TutorialDB'') WITH NO_INFOMSGS;',
@on_success_action=1,@on_fail_action=2;
EXEC dbo.sp_add_jobserver @job_name=N'TutorialDB integrity';
The step exits the job with success or failure explicitly. Select a stable, least-privileged operational owner according to your deployment policy; do not depend on an individual employee account. Permissions must be adequate for the database check.
2. Run and inspect the job
EXEC msdb.dbo.sp_start_job @job_name=N'TutorialDB integrity';
EXEC msdb.dbo.sp_help_job @job_name=N'TutorialDB integrity';
EXEC msdb.dbo.sp_help_jobhistory @job_name=N'TutorialDB integrity';
A start request is asynchronous. Wait for the job to finish and inspect the final outcome and step messages. Test a controlled failure in a disposable copy of the job, for example by referring to a nonexistent lab database, and prove that your monitoring detects it.
3. Add a deliberate schedule
USE msdb;
GO
EXEC dbo.sp_add_jobschedule @job_name=N'TutorialDB integrity',
@name=N'Tutorial nightly',@freq_type=4,@freq_interval=1,
@active_start_time=020000;
EXEC dbo.sp_update_job @job_name=N'TutorialDB integrity',@enabled=1;
The example means daily at 02:00 in server local time. Confirm timezone, maintenance overlap, and the next run in SSMS. Configure an operator and Database Mail or your external monitoring route, then test delivery. Choose a real frequency from runtime, risk, and recovery requirements rather than copying this lab schedule.
4. Read the example output
The following is illustrative, normalized output, not a transcript from a customer system. Values depend on your release and lab state.
Illustrative job acceptance record:
Manual execution: succeeded
Controlled failure: detected
Next scheduled run: reviewed in server local time
Alert delivery: confirmed
Record actual results before enabling recurring work. The example does not assert that a notification integration is already configured.
Verify the result
Observe at least one scheduled run, not just a manual invocation. Check job history retention, step failure handling, and notification delivery. A backup job needs an additional restore test; Agent success alone cannot prove recoverability.
Troubleshooting
Jobs that work interactively but fail in Agent often use different security contexts or paths. Check service status, owner permissions, database context, and step output. A disabled job can still be started manually; disabling controls scheduling.
Recovery and next steps
Disable the lab schedule after the exercise if no recurring work is intended. Retain evidence of the test before removing the job. Avoid automatically retrying destructive or non-idempotent steps without understanding partial completion.