SQL Server Automation Tutorial

11. SQL Server Automation Tutorial

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,

1. Jobs
2. Maintenance plans

JOBS

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

1. Once the job is created we can start, stop, enable disable and delete job by right clicking on the job
2. We can edit the ob if we want
3. We can script out the job and copy the script for creating same job on other server during migration

Troubleshooting the Job Failures

1. 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.
2. 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.
3. Verify in Msdb tables like sysjobs, sysjobhistory
4. Go through sql server error logs for failure notification messages.