April 8, 2015 at 9:04 am
Hello,
I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server. Thankfully a new server is coming.
However, for now, I need to restart the SQL server service because users begin complaining they can't connect.
1) I created an operator - me.
2) I created a job which runs a query.
INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC
3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.
Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.
4) I've set the alert to email and delay is between responses 2 minutes.
It history tables says number of occurences is 18964. However, I don't receive an email.
Shouldn't the alert send an email? Do I need to include email code in the job?
A little confused reading the articles. Any help is appreciated.
Thanks.
Things will work out. Get back up, change some parameters and recode.
April 8, 2015 at 9:35 am
A few things I would check first:
1. Is mail setup on the server (SSMS > Managment > Database Mail)
2. Can you send and recieve a test email from Database Mail (same place as above, right click and select Send Test Email)
3. Is a mail system and mail profile setup in the SQL agent settings (SQL Agent properties > Alert System)
4. Can you see your email message having been sent in the msdb logging tables:
SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
5. Have you checked your junk mail, may seem like a stupid question but so many people get caught out with this
MCITP SQL 2005, MCSA SQL 2012
April 8, 2015 at 9:57 am
Hello,
Yes, I have set up a mail profile.
I have tested it.
The Alert Systems do have my profile that I created.
The table mail_items does have the emails that I sent out while creating and testing the database mail.
However, nothing from the alerts. I think that is my disconnect. How does an alert send out mail?
I thought my creating the alert and checking email for the operator that would produce an email. There is no email code in the job which the alert runs.
Should there be?
Thanks for responding.
Things will work out. Get back up, change some parameters and recode.
April 8, 2015 at 10:12 am
I think I see the issue, your alert sounds like it is setup to start a job, rather than contact an operator when the condition is met.
Personnly I would have had the job setup to run pretty much continuously, then if the condition is met send you an email using the send mail stored procedure. Can't recall it off the top of my head but it's something like sp_send_dbmail.
Depending on the level of detail you want in the message you may want to use the HTML Format so it looks better.
I'm not at a machine at the moment to send an example but I should be able to tomorrow.
MCITP SQL 2005, MCSA SQL 2012
April 8, 2015 at 1:12 pm
Ok, my confusion increases! 🙂
When you set up an alert, you include a job to run. Then you set up the operator and then email.
Should the alert send out the email when the counter is reached or does the job send the email?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 8, 2015 at 2:53 pm
The only email a job can send without custom coding transact sql in the job steps is on completion, sucess or failure of the job.
When an alert fires it can be setup to send an email. without a machine in front of me to check I am pretty sure the alert can only be configured to send an email to an operator when a condition is met OR run a job not both.
We tend to not use alerts, alternatively jobs are setup with operators to notify on failure. When we want to alert when a condition is met we tend to use sql agent jobs which have a step or steps that will send email using transact sql and the send mail stored procedure. We can then control the subject, body, importance, format etc of the mail to have as much or little detail we require.
MCITP SQL 2005, MCSA SQL 2012
April 9, 2015 at 7:46 am
Ok, I found the answer.
I re-read the articles on creating the alerts. I saw one item: restart SQL Server agent.
When I restarted the agent, I started receiving emails. It works perfectly.
1) Create a mail profile.
2) Create an operator
3) Create a job
4) Create an alert and add the job and the operator.
Restart the SQL Server agent!!
5) Emails start automatically.
Things will work out. Get back up, change some parameters and recode.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply