Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated we can get the status f the task in the form of alert and helpful while generating reports.
Automation can be done in 2 ways,
- Maintenance plans
Sql server agent jobs useful to perform daily scheduled maintenance tasks.
- All these jobs falls under database maintenance category
- We can start, stop, enable, disable job as per our requirement
- Easy to create and maintain jobs. Whenever any issue happen we can troubleshoot easily.
- Scheduling also easy with jobs.
How to create a job
Right click on the job folder under sql server agent → Select New job from options menu
General → it helps DBA’s to provide identification of jobs
Name → we need to provide name for the job
Owner → always job owner is to be sysadmin(SA)
Category → under which category this job falls, mostly all DBA tasks falls under database maintenance
Decryption → provide some description about the job
Enabled → job will be created now, we can enable later using this option.
Steps: Steps are nothing but a task under job
Select New button
Step name → PROVIDES DETAILS OF STEP OF JOB
Type → sql server uses T-sql language by default
Runs → Only that privileged persons can able to run this job. If we specify
Database → By default it points to master, we can change as per requirement
Command → T-sql command to perform the task
Parse → we can verify syntax errors of the T-sql command using this option.
On success → provide next steps once the first step complets means it has go to next step (or) it has to quit the job.
Retry attempts → no.of retry attempts need to specify if a job fails it will attempt try for specific times and report to user if fails again.
Retry Internal → wait for specified time and attempt next try
On failure → on failure where this job have to go whether it has to start from beginning (or) quit the job reporting failure to user.
Output file → we configure output file to get the information about the job. If it fails with what error it fails we will get from this.
Append output → overwrites the existing job information
Log to table → if we want job running status data in the form of table.
Output in history → to view history of job execution
We can add, edit, delete steps more the order of steps using insert, edit and delete buttons.
Name → name of schedule we need to provide here
Schedule type → whether it is one time job (or) daily job we need to provide type.
Enabled → schedules it to be enable now (or) later
Occurs → whether it is daily (or) weekly (or) Monthly
Recurs every → it will re-run the stops on specified day.
Occurs every → we need to specify the frequency of executing this job means every 1 hour (or) 4 hours as per our requirement
Summary of job will be displayed at the end.
Once all the details provide click OK, it will create a job with specified name.
Post Job Steps
- Once the job is created we can start, stop, enable disable and delete job by right clicking on the job
- We can edit the ob if we want
- We can script out the job and copy the script for creating same job on other server during migration
Right click on job → select script job as → open job in new query window → copy the script of job place on different server and execute.
Troubleshooting the Job Failures
- Output file → open output file which we configured while creating jobs under advanced steps of job. It records all the details of job for troubleshoot.
- View history → right click on job go to view history provides of job execution. If fails with what error it failed we’ll get all details here.
- Verify in Msdb tables like sysjobs, sysjobhistory
- Go through sql server error logs for failure notification messages.
Below topics further included in this series
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server