SQL Server Agent - Agent Alerts
What is an Alert?
A SQL server Agent Alert is an action in response to an event or a happening in the SQL Server System.
An Alert could be triggered for one of the following conditions
• When an error occurs in the SQL Server System.
• When there is a performance issue or threshold reached in the SQL Server System
• When there is a WMI event
In response to an alert you can either execute a SQL Server Job or email the DBA (Operator).
For automated fix to a problem we could run a Pre defined fix (SQL Server Job) and for human intervention to an issue we could email the information to the Operator.
Operator
An Operator is nothing but a reference to an email address. You create an Operator in SQL Server Agent by either providing Email id or Net Send Address or Pager Email Name. However Net Send should not be used in practice since it would be removed in the future versions. Email is the most preferred option here.
While creating an Operator it is mandatory to make sure the Enabled Checkbox is checked, else the operator won’t be getting any alerts. You could also define the type of alert the user requires.
Alternatively you can also create the operator using the system procedure sp_add_operator Refer: http://msdn.microsoft.com/en-us/library/ms186747.aspx
Defining an Alert
Defining an alert is quite straightforward and can be configured by right clicking on the ‘Alerts’ FOLDER in SQL Server Agent and select ‘New Alerts’
You could select the alert to be common for all the databases or specific to one particular database.
The alert can also be set for the error code or the based on the severity of the alert from a drop down box.
In the Response we can define on what needs to be done during the alert. Either running a Job or Notifying Operators.
In adition to this we can also write some addition messages along with the notification message,just to make your email more clear.
Defining an alert can be done using the system stored procedure sp_add_alertsystem
Refer: http://msdn.microsoft.com/en-us/library/ms189531.aspx
Example
Let me explain you a scenario where the alert could be very handy.
I encountered a situation where the Job needs to be triggered when there is a new file moved to a specified location .The Job is supposed to process the file on arrival. At first I came up with a suggestion of creating a windows service which could watch the folders and raise the job accordingly. I was so wrong. All this unnecessary work was avoided when my Architect rightly pointed me to use the alerts in SQL Server Agent.
I created an WMI Event Alert called Testing Alert with the following query.The namespace mentioned here similar to the namespace we give in c#.net
The following query picks up any event which is created due to file transfer of Test .txt to the folder c:\TestFolder\
SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘c:\\TestFolder\Test.txt’
In response to this alert I have defined the TestJob to be triggered.
And that’s how I managed to trigger the Job based on File arrival with out hardly any line of code Hope this was useful.
What is an Alert?
A SQL server Agent Alert is an action in response to an event or a happening in the SQL Server System.
An Alert could be triggered for one of the following conditions
• When an error occurs in the SQL Server System.
• When there is a performance issue or threshold reached in the SQL Server System
• When there is a WMI event
In response to an alert you can either execute a SQL Server Job or email the DBA (Operator).
For automated fix to a problem we could run a Pre defined fix (SQL Server Job) and for human intervention to an issue we could email the information to the Operator.
Operator
An Operator is nothing but a reference to an email address. You create an Operator in SQL Server Agent by either providing Email id or Net Send Address or Pager Email Name. However Net Send should not be used in practice since it would be removed in the future versions. Email is the most preferred option here.
While creating an Operator it is mandatory to make sure the Enabled Checkbox is checked, else the operator won’t be getting any alerts. You could also define the type of alert the user requires.
Alternatively you can also create the operator using the system procedure sp_add_operator Refer: http://msdn.microsoft.com/en-us/library/ms186747.aspx
Defining an Alert
Defining an alert is quite straightforward and can be configured by right clicking on the ‘Alerts’ FOLDER in SQL Server Agent and select ‘New Alerts’
You could select the alert to be common for all the databases or specific to one particular database.
The alert can also be set for the error code or the based on the severity of the alert from a drop down box.
In the Response we can define on what needs to be done during the alert. Either running a Job or Notifying Operators.
In adition to this we can also write some addition messages along with the notification message,just to make your email more clear.
Defining an alert can be done using the system stored procedure sp_add_alertsystem
Refer: http://msdn.microsoft.com/en-us/library/ms189531.aspx
Example
Let me explain you a scenario where the alert could be very handy.
I encountered a situation where the Job needs to be triggered when there is a new file moved to a specified location .The Job is supposed to process the file on arrival. At first I came up with a suggestion of creating a windows service which could watch the folders and raise the job accordingly. I was so wrong. All this unnecessary work was avoided when my Architect rightly pointed me to use the alerts in SQL Server Agent.
I created an WMI Event Alert called Testing Alert with the following query.The namespace mentioned here similar to the namespace we give in c#.net
The following query picks up any event which is created due to file transfer of Test .txt to the folder c:\TestFolder\
SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘c:\\TestFolder\Test.txt’
In response to this alert I have defined the TestJob to be triggered.
And that’s how I managed to trigger the Job based on File arrival with out hardly any line of code Hope this was useful.
Nicely dome Kannan, this could be handy in so many ways. Thanks for sharing this.
ReplyDeleteThank you fatherjack :)
ReplyDeleteHi ,I am also having similar issue,But in my case file name is not fixed, file name can be vary as prefixed with user_id name,In my case file extension is xlsx,So please tell me can I configure alert on file extension.
ReplyDeleteAshutosh
And In my case multiple file can be posted at same time.
ReplyDelete