{"id":854,"date":"2013-10-31T12:07:47","date_gmt":"2013-10-31T19:07:47","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=854"},"modified":"2018-11-13T10:52:22","modified_gmt":"2018-11-13T18:52:22","slug":"creating-sql-server-agent-alerts-for-critical-errors","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/","title":{"rendered":"Creating SQL Server Agent Alerts for Critical Errors"},"content":{"rendered":"<p><font size=\"2\">One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the <\/font><a href=\"https:\/\/www.red-gate.com\/simple-talk\/\"><font size=\"2\">SimpleTalk web site<\/font><\/a><font size=\"2\">. Part One <\/font><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/provisioning-a-new-sql-server-instance-part-one\/\"><font size=\"2\">is here<\/font><\/a><font size=\"2\">, Part Two <\/font><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/provisioning-a-new-sql-server-instance-part-two\/\"><font size=\"2\">is here<\/font><\/a><font size=\"2\">, and Part Three <\/font><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/provisioning-a-new-sql-server-instance-part-three\/\"><font size=\"2\">is here<\/font><\/a><font size=\"2\">. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQLskills own Paul Randal previously <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/easy-monitoring-of-high-severity-errors-create-agent-alerts\/\"><font size=\"2\">wrote about this<\/font><\/a><font size=\"2\">. SQL Agent Alerts will fire when certain types of errors occur, and you can configure them to notify a SQL Operator when this happens. This will let you find out about these types of problems much earlier than you otherwise probably would, which is a good thing!<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">The code <a href=\"https:\/\/www.dropbox.com\/s\/xdxogn2qh57av86\/Add%20SQL%20Server%20Agent%20Alerts.sql?dl=0\">in this script<\/a><\/font><font size=\"2\"><\/font><font size=\"2\"> is an updated version of the script that I included in my article that automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. I have also recently added an additional alert for Error 832, and for Errors 855 and 856 (if you are running SQL Server 2012 Enterprise Edition).<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">SQL Server 2012 Enterprise Edition has a new feature called <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/memory-error-recovery-in-sql-server-2012\/\"><font size=\"2\">Memory Error Recovery<\/font><\/a><font size=\"2\">. It can detect (and sometimes correct) when clean database pages have become corrupted in memory. You need to have Windows Server 2012 or newer, and you must have a processor (such as an Intel Xeon 7500 series or Intel Xeon E7 series) that supports this functionality. If you have all of this, you will see this message in your SQL Server Error Log:<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<blockquote><p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p><\/blockquote>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">There are some more details about this in this KB article: <\/font><a href=\"https:\/\/support.microsoft.com\/kb\/2681562\"><font size=\"2\">Using SQL Server in Windows 8 and Windows Server 2012 environments<\/font><\/a><font size=\"2\">. There is a <\/font><a href=\"http:\/\/video.ch9.ms\/teched\/2012\/na\/WSV328.pptx\"><font size=\"2\">PowerPoint deck about this feature here<\/font><\/a><font size=\"2\">.<\/font><\/p>\n<p><font size=\"2\"><br \/>\n<\/font><\/p>\n<p><font size=\"2\">In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit. You need to supply a valid SQL Operator name for my script to work correctly.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,30,31,156,32],"tags":[324],"class_list":["post-854","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012","category-sql-server-2014","category-sql-server-agent-alerts","tag-sql-server-agent-alerts"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2013-10-31T19:07:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-13T18:52:22+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/\",\"name\":\"Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2013-10-31T19:07:47+00:00\",\"dateModified\":\"2018-11-13T18:52:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating SQL Server Agent Alerts for Critical Errors\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/","og_locale":"en_US","og_type":"article","og_title":"Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry","og_description":"One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/","og_site_name":"Glenn Berry","article_published_time":"2013-10-31T19:07:47+00:00","article_modified_time":"2018-11-13T18:52:22+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/","name":"Creating SQL Server Agent Alerts for Critical Errors - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2013-10-31T19:07:47+00:00","dateModified":"2018-11-13T18:52:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql-server-agent-alerts-for-critical-errors\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Creating SQL Server Agent Alerts for Critical Errors"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=854"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/854\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}