SQL Server DBA Tutorial 119-How to Create an Alert in SQL Server

welcome to tech mothers in this video we're going to learn how to create an alert in sequel server and we'll be creating alert in sequel server agent for sequel server and sequence several jobs and other events in sequel server so whenever any event in sequel server occurs if you have created the alert in and setup the alert and configure the alert it is always a good idea especially for production servers that if severe any severe alert occurs you would like to know and get to resolve it right away so in this video we'll be going through types of alert we'll be selecting what type of alerts available for us and there are three types of alerts available for us and then we can select the severity level of those alerts and we can set up the severity and there are system settings in there and we'll go through that and then message search setting and that are based on a log based on sequence of the log what error message you will see in the log and you would like to know that when this message occurs in sequel server log you would like to know that something went wrong in production server and that alert will be generated from that particular error and then after that you get the alert what would be the response options in alert and what would be the alert options whether it's an email pager or nets and so we're going to go through all that so let's go ahead and look at a sequel server this is my sequel server in order to create an alert you need to go to sequel server agents and expand the agent and you will see the alert option right now I do not have any other setup so we're going to go ahead and do the alert so right click and click alert so first thing we see is again when you create alert it has to be enabled in order for it to work so first time first thing we wanted to do is name the alert and that should be meaningful for me I'm going to go ahead and create an alert if any job on sequel server agent fails I would like to know so one is job failure alert and up here is the type of the alert sequence every event alert or sequence of a performance condition alert or WMI event alert for me is it's going to be sequel server event alert because whenever job fails in sequence every agent it's written down in sequence several logs so we're gonna go ahead and find that particular error message in here and send the alert so we're gonna go ahead and select sequel server event alert and I would like to know all about in this this error if it happens about any database in sequel server this particular instance I would like to know but if you have just wanted to set up alert on a specific database sometimes there are critical databases on your sequel server instance and some are not critical on your production system so if you have a lot of databases you don't want to do all the databases you wanted to select particular database so orden go ahead and select all the database in this case if you exactly know what error number that you are looking for you can go ahead and click on error number right here and specify if error number whenever error occurs it has a number assigned to it so based on the error number the if that particular error number occurs you would like to put it right here so some of the error numbers as you can see right here 0 0 1 and it's considered one error number miscellaneous system information and if you click on this you will get a lot of up here the the severity settings so these are the severity settings sometimes it's very important to put severity information right here is peddle error in resource current process if there is a fatal error in database process such as you run dbcc check DB and there are errors in the database you wanted to know that right away and you wanted to look at the integrity of the database and you wanted to also know that any hard disk failure happen which is hard drive which is hardware error or any fatal error fatal error when you click on that and there in sequel server error log it would have a word fatal error at any time fatal error occurs you would like to know that and they are really important especially for sequel server DVS and production support insufficient permission or insufficient disk space these kind of right here insufficient resources that means if even this space goes down to an extent and if you have threshold like 80% then is going to let you know that the space you're running out of space so these categories are really important to know and important to set when you're creating alert so for me I'm going to go ahead and just information for this demo so I'm going to go ahead and based on I'm not going to really do the error number since mine is just information it's gonna let me use it defined I'm going to go ahead and use the different use because I don't want to have alert anytime the information goes because usually you would like to have set up the alerts on errors so I'm going to go ahead and when error occurs in sequence ever log and I wanted to say job failed if it finds the text message job failed in sequel server error log it's going to send generate this alert for me and it's going to send that alert based on our settings to all its DBA team or if you are only one you wanted to know then you can put your email up here response right here if you go on response there are option right here there are a couple ways that you can assign this alert to jobs you have let's say that you have a lot of jobs already you know created and if you wanted to click on execute job and relate any particular one job that's these are the jobs already done so if you click on test jobs so if test job fails is gonna send me the error I don't want to basically specify any job because I want to know any job fails because I have if you notice right here I have a text message job field so any job fails basically it's going to send me an alert so I don't want to tie it with the job the other option is when you create a job which I'll show you in a second now this alert is created and you can basically put that this alert into that job so notify operator if you have setup operator already you can basically go ahead and click that operator operator could be your team and the whole team could be your operator so you want to send an email to your team DBA team so that they would know that it's a it's really it's very one alert and I should get right to it so options right here right now we don't have operator we'll be creating an operator basically in the next demo so we're going to go ahead once we created and that and we will basically come here in our alert and put that operator right here so you do have option if you click up here to create a new operator right here so we're now gonna go through this for now and let's look at the options how you wanted to basically send alert email pager net sent you can go ahead and send email and say job failed on TBS TBS client backslash sequel there so this would be and delay response will be let's say 30 30 seconds if job is keep failing every time it it finds in in your sequence every log job fail just gonna send you error but if you have a delay of 30 minutes it's gonna look after 30 minutes so you don't want to be you don't want to have your email flooded with all these emails coming from this particular sequence server and we're gonna go ahead and click okay as you can see the job alert is created and you can enable or disable alert from right-clicking after you know you can right-click and disable the alert and once you disable that you need to enable that alert in order for it to work so we're going to go ahead and enable it I just want to quickly show you that how to enable and disable alerts in sequel server so we're gonna go ahead and basically go in the job this is my job right here and alerts so up here when I was talking about that that we can go in the jobs and add the alerts if we have already the job you know the alerts created so we're gonna go ahead and click on add and it'll give you option that basically right here that witch alert you wanted to go ahead and select so this is the alert you can you have an option to create the alert from the job right here so this is how you do it in from the jobs as I said there are two ways to create basically the alert so we're gonna go ahead and cancel this and we're going to go ahead and delete this and use our script T sequel script to do the same thing so let's load our TCL script it's right here it's going to be job status underscore DBA this is going to be the name of the alert and up here all the entries as you can see delay between the responses is 1800 that means that thirty minutes so include event description one and job failed on production system this is a one of the error message that will be attached in your alert so event description keyword is I'm looking for a keyword called job fail so anytime job fails is going to generate this alert so I'm going to go ahead and run this command completed successfully let's go ahead pressure alert and see if that job this alert is created so basically this is how you create alert in sequel server and using the sequel server agent and we went through the type of the others very settings message setting such settings responses alert options and I hope this video helps

Channel: TechBrothersIT

Views: 26,939

Rating: 4.7798166 out of 5

Keywords: How to Create an Alert in SQL Server - Microsoft SQL Server DBA Tutorial, SQL, Server, Developer, TSQL, DBA, Interview, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, SQL Server 2014, SQL SErver Adavance Tutorial, SQL Server Tutorial for beginners, Tech Brothers, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Adminstoration Tutorial Step by Step, Create an Alert in SQL Server

Id: cUYvye80KN0

Length: 10min 57sec (657 seconds)

Published: Fri Mar 13 2015