SQL Server Agent Jobs
SQL Server Agent is the built-in job scheduler for SQL Server. It's available on SQL Server on VMs and SQL Managed Instance — but NOT on Azure SQL Database.
Where SQL Agent Works
| Platform | SQL Agent Available? | Alternative |
|---|---|---|
| SQL Server on VM | ✅ Full access | — |
| SQL Managed Instance | ✅ Full access | — |
| Azure SQL Database | ❌ Not available | Elastic Jobs, Azure Automation |
How SQL Agent Works
SQL Agent uses a 3-component model:
| Component | What It Does |
|---|---|
| Job | A container with one or more steps that execute in order |
| Step | A single unit of work (T-SQL, PowerShell, SSIS, OS command) |
| Schedule | When the job runs (recurring, one-time, on-demand) |
Job Step Types
| Step Type | Use Case | Example |
|---|---|---|
| T-SQL | Database operations | Index rebuild, statistics update, data cleanup |
| PowerShell | OS + Azure operations | Send email, call REST API, file operations |
| SSIS Package | ETL workflows | Data import/export between systems |
| OS Command (CmdExec) | Shell scripts | Run batch files, external programs |
| Replication | Log reader, snapshot, distribution | Transactional replication maintenance |
DP-300 key fact: SQL Agent jobs run under the context of the SQL Server Agent service account unless you configure a proxy with specific credentials. For security, always use proxies with least-privilege credentials for non-T-SQL steps.
Job Scheduling Options
| Schedule Type | Example |
|---|---|
| Recurring | Every day at 2:00 AM, every Sunday at midnight |
| One-time | Run once on March 15 at 10:00 AM |
| CPU Idle | Run when CPU is idle (useful for low-priority maintenance) |
| On-demand | Manual execution only |
| On Agent Start | Run when SQL Agent service starts |
Notifications and Alerts
SQL Agent can notify you when jobs succeed or fail:
| Notification Method | How It Works |
|---|---|
| Database Mail | Send email via SMTP (requires Database Mail profile) |
| Operator | A named recipient (email, pager, net send) |
| Event Log | Write to Windows Event Log |
| SQL Agent Alert | Trigger another job or notification on error/severity |
Alert Types
| Alert Type | Triggers On | Example |
|---|---|---|
| SQL Server Error | Specific error number | Error 823 (I/O error) |
| Severity Level | Error severity 17-25 | Severity 19+ (fatal resource errors) |
| Performance Condition | Counter threshold | CPU > 90% for 5 minutes |
| WMI Event | WMI query match | Deadlock detected |
Best practice: Always create alerts for severity 17-25 errors. These are fatal errors (resource issues, hardware errors, corruption) that need immediate DBA attention. Configure Database Mail + an operator to get email notifications.
Common Maintenance Jobs Every DBA Should Create
| Job | Frequency | What It Does |
|---|---|---|
| Index Rebuild/Reorganize | Weekly | Defragment indexes (rebuild > 30%, reorganize 10-30%) |
| Update Statistics | Daily | Keep query optimizer statistics current |
| DBCC CHECKDB | Weekly | Check database integrity for corruption |
| Backup Cleanup | Daily | Delete old backup files beyond retention |
| Cycle Error Log | Weekly | sp_cycle_errorlog — prevents log from growing too large |
| Purge Job History | Monthly | Clean old job history to prevent msdb growth |
Ola Hallengren's Maintenance Solution is the industry standard for SQL Server maintenance jobs. It creates optimized index, statistics, backup, and integrity check jobs. Know this exists for the exam — it's referenced in Microsoft documentation.
SQL Agent on Managed Instance — Differences
MI supports SQL Agent but with some differences from on-VM:
| Feature | SQL on VM | SQL MI |
|---|---|---|
| T-SQL steps | ✅ | ✅ |
| PowerShell steps | ✅ | ❌ (use Azure Automation) |
| SSIS steps | ✅ | ❌ (use ADF SSIS IR) |
| CmdExec steps | ✅ | ❌ |
| Database Mail | ✅ | ✅ |
| Operators | ✅ | ✅ |
| Proxies | ✅ | Limited |
| Multi-server jobs | ✅ | ❌ |
MI limitation: SQL Agent on MI only supports T-SQL job steps. No PowerShell, CmdExec, or SSIS steps. For non-T-SQL automation on MI, use Azure Automation Runbooks or Azure Logic Apps.
Job Monitoring
| Method | What It Shows |
|---|---|
| Job Activity Monitor | Current status of all jobs (SSMS) |
msdb.dbo.sysjobhistory | Historical job execution results |
sp_help_jobactivity | Currently running jobs |
| SQL Agent Error Log | Agent-level errors and warnings |
| Azure Monitor (MI) | Agent job metrics in Azure Portal |