SQL Server Agent tutorial ( Beginner Training video)
Articles,  Blog

SQL Server Agent tutorial ( Beginner Training video)


In this video we will try to understand what exactly is this thing over here called as The Sql Server Agent lot of times when you open your Sql Server Management Studio you would see this Small icon here called as Sql Server Agent Sql Server Agent is nothing but it is a feature given in Sql Server which will help you to run a Job. after specific interval of time for example now let us say that you want to go and run a backup process every night so that means you will go and you will define a backup process and that backup process you want to continuously execute in the evening time so that’s where this Sql Server Agent comes into picture so in simple words Sql Server Agent helps you to execute a Job after specific interval of time so the first thing is you need to go and enable this Sql Server Agent you can see that at this moment the Sql Server Agent is disabled so let us right click on this and let us click on start and let us say YES let us start the Sql Server Agent once the Sql Server Agent start’s it should actually turn into a green color you can see that now it is green color when you go and you open the Sql Server Agent you can see that it has something called as Jobs so at this moment you can see that i already have some Jobs here which is running but you can always go and define your job over here and this job can run after specific interval of time Now again when you say that you want to go and create a job a Job can be very complex or job can have series of steps or series of logic which you want you execute 1 after another in Sql Server Agent you can go and create a Job and inside every job you can go and define a lot of steps and all of these steps you can run after specific interval so let us go ahead and create a simple job here so let us say that i want to go and create a job which will actually go and delete records from this table you can see that there is a table here called as custable and let us say in this i have some records like one Shiv and two Raju so let us say that after every minute i want the Sql Server Agent to go and run a job which will actually go and delete the records from the this table so i want to go and create a job which will delete records after every one minute for this table so let us go here and a you can see that there is a small folders icon here called as jobs so let us right click on this and say i want to go and create a new job and let us say that i will name the job as Delete After Every one minute and now this job has to run under specific user account and the user account should have all the rights to fire the Sql so i will go and let us use the NE Authority so that we get all the access what we need i am running this job under this NT Authority System then next thing also need to go and specify that what is the type of this job so this job is database maintenance so i will say that this is a DB maintenance and a it delets records from CusTable now once you define job the next thing is you need to go and defines steps as i have told in the previous part of the video that job can be complex it can have series of steps so you can see that over here there is one more section here called a ssteos so at this moment we just have only one step so let us go and create at one step so i will say new i want to go and fire the delete query at this moment i just have only one step so the next thing he say’s is that so OK what is the type of step this is so i am saying this type of step is T-Sql and i want to run it on the customer database so i want to run here delete from CustTable and quickly let us go and parse this query so it looks all OK and i will say OK so you can see now i have a one step over here which actually goes and deletes the query in the same way i can go and again add one more steps if i wish so i can again go and define new step if i want so i can have serious of steps over here and if i wish i can use this small arrows over here to sequence the steps one after another so i have defined name for my job i have define the steps now the next thing is i need to go and i need to define a schedule a time after which i want this job to run so let us say i will say here i want to go and run this job every minutes so let us say i want to run this job every minute so it will occur daily occurs at every one minute and it’s starts ran i will say it will start at this moment in my clock it is 3:35 almost 3:36 so let say i will to say you need to go and start on 3:37 let say 3:38 37 would be to fast this is evening time so PM so that means at this moment just 3:36 you can see over here at 3:38 thsi job will run so i will say OK once i do a OK you can see that over here i see there Job over here delete after every one Minute so this job will run after every one minute so at this moment the time is approximately 3:36 point 35 so this will start after some time if you want to go and monitor the job has it ran or not or is it what errors are there when the job is running you can see that there is a small icon over here called as the job activity monitor so you can right click on this and you can say view job activity so you can see this delete after every one minute has still not ran but yes it is enabled so i hope that at 3:38 this job will run so i am doing a refresh let us see so at 3:38 you can see my clock down below so 3:38 it should run so let us see Sql Server Agent help’s you to go and run a job after at a specific interval of time succeeded can you see it has run and if you wish you can go and you can see the History and in the history you can see what as happened when it ran under which under which accounted executed in-case there are errors you should see those errors over here so now if i go and just try to reload this sol let me go and execute this you can see that records have gone from the database so this was a small video where we try to understand what exactly is a Sql Server Agent and how to go and configure job in Sql Server and how to and monitor a Sql Server Agent activity THANK YOU SO MUCH πŸ™‚

17 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *