In the previous discussion we discussed about the function of Profiler in SQL server, what is the use of it? Also we discussed about the Stored Procedures and SQL Plan.
So now in this part we will be discussing about another property or we can say functional tool present in our SQL server management studio. This tool will help us in doing some of the processes which we want them to complete in background while doing other work.
SQL Server Agent:
SQL Server Agent is a type of tool present in our SQL server management studio. The main function of this tool is to complete the tasks or processes in the back ground while we are doing some other work. Like for example we want to check our records daily in the evening or in the morning to make sure that any new records are added to the database or not and delete the duplicate records if there are any. Now here we cannot check the whole bunch of records in the database manually at a scheduled time while we have other work to complete.
That is where we use SQL server agent. In the SQL server agent we can create a job saying that the job will complete this task daily in the evening or morning at a scheduled time.
Here we see where the SQL server Agent is actually present in our SQL server management studio.
Here we see that the SQL server Agent is disabled and we have to enable it manually. We have to right click on the SQL server Agent and select the option Start.
Here we see in the second image that our SQL Server Agent has been enabled and it is shown in green color. Now if we expand the SQL server Agent we see many folders named Jobs, alerts, operators, etc.
Now for completing some specific task at a scheduled time we have to create a specific job for it containing some steps. An important thing here to remember is that a job can contain one step or many numbers of steps.
Here we see a job can contain many numbers of steps.
To understand how SQL server Agent works we will create a new job and create steps into it.
Just right click on the jobs and select the option new job. Now we want to create a job where it will delete the records in the table every one minute.
After selecting the new job option, give some name for the job like it is given above in the image “Delete records every 1 minute”. We can give any name we want. After that we have to select the login server which has all the permissions to run this job, so we have to select the option shown in red underline. Then browse for the login servers and select “NT AUTHORITY\SYSTEM” shown in red underline. Then select OK to confirm it.
After doing this select the category as Database Maintenance shown in the category drop down list. Then write some description about the job in the description textbox.
The next after doing this we have to define some steps as discussed earlier that every job has some steps. Here we have to define steps, but according to our job here we have to define only one step.
We have one more category known as Steps where we can define steps related to the job, so select that option of Steps and select new to create a new step.
Here as we have only one step, so we will write the step name as “DeleteQuery” and then select that what kind of step it is. So go to the type option and in the drop down select “T-SQL” or we can say Transact-SQL script. And select the database where our records are present. After that write the command like “Delete from dbo.Employee” and parse it. If it is successful then select OK.
We see here that a new step is created; if we want we can add more steps to it by following the same procedure.
Now after we have defined the name of our job and the steps of the job we will define the schedule for our job. Earlier we discussed that the job runs on a particular schedule which we decide and therefore here also we define the schedule for the job. So then go to the schedule option and then select new.
Fill the necessary details such as name of the schedule, details, etc. It is shown in the figure below,
We have to fill the details shown in red underline. Other types of details can also be filled according to our requirement. Select OK after that.
Now here our job will start at 4pm according to the PC clock. After 1 minute we will see that the job has executed.
To check whether our job has completed or not we go to the option called as “Job Activity Monitor”. In this option we can monitor our job activity and see that our job has completed successfully or not.
So in short in this discussion we saw that what is SQL Server Agent and how it is used.
So SQL Server Agent helps to run a job in the background or we can say run the job at a specific interval of time. Also there can be one step or many steps when we are creating a specific job. And to see whether our job ran properly or not and about the progress of the job we use the tool known as Job Activity Monitor.