Table of Contents
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,
Sql server agent jobs useful to perform daily scheduled maintenance tasks.
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.
1 |
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<strong>.</strong> |
[…] 11. SQL Server AUTOMATION Tutorial […]