{"id":1130,"date":"2011-10-26T02:37:30","date_gmt":"2011-10-26T02:37:30","guid":{"rendered":"http:\/\/craftydba.com\/?p=1130"},"modified":"2017-10-15T16:22:28","modified_gmt":"2017-10-15T16:22:28","slug":"testing-alerts","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1130","title":{"rendered":"Testing Alerts"},"content":{"rendered":"

Having set up system <\/a>and performance <\/a>alerting, it is now time to test those darn alerts to make sure they work! Alerting can notify a operator via email, paging, or a net send messages. Now adays, most people have a smart phone, not an old fashion pager. Therefore, I usually create a Active Directory group that has both the regular company email of the DBA group and their cell phone eqivalents which are usually the phone number @ provider name dot com.<\/p>\n

Today, I am going to talk about two scripts that will help you test some of the alerts that you might choose to setup.<\/p>\n

The first script create a stored procedure that takes a error level as a parameter. The code calls the RAISEEROR function to log a test error message to the windows application log and SQL server error log. These events will trigger off the ‘Alert For Severity Level 17’ alert we created in a earlier article.<\/p>\n

\r\n-- Choose to install script in msdb\r\nUSE msdb\r\nGO\r\n\r\n-- Drop existing stored procedure\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = \r\n    OBJECT_ID(N'[dbo].[usp_Test_Alerts]') AND type in (N'P', N'PC'))\r\nDROP PROCEDURE [dbo].usp_Test_Alerts\r\nGO\r\n\r\n-- Add new stored procedure\r\nCREATE PROC dbo.usp_Test_Alerts @VAR_SEVERITY INT = 0\r\nAS\r\nBEGIN\r\n    DECLARE @VAR_MSG VARCHAR(125);    \r\n    SELECT @VAR_MSG = 'Testing alerting on ' + @@SERVERNAME + \r\n        ' severity ' + STR(@VAR_SEVERITY, 2, 0) + '.';\r\n    RAISERROR (@VAR_MSG, @VAR_SEVERITY, 1) WITH LOG;\r\nEND\r\nGO\r\n\r\n-- Trigger the error severity 17 alert\r\nmsdb.dbo.usp_Test_Alerts 17\r\n<\/pre>\n<\/p>\n
The second script is used to trigger a the log file size SQL performance alert on the Adventure Works database.  This script works by creating a simple table, inserting one row of data, and updating the row 20,000 times.  This script is also handy when you want to shrink a log file and the active part is at the end of the file.  Well, I will talk about that later, someday …<\/p>\n
\r\n-- Use AW2008\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n-- Make test table\r\nCREATE TABLE dbo.MyTest (MyKey VarChar(10), MyData Int);\r\nGO\r\n\r\n-- Add one row\r\nINSERT dbo.MyTest (MyKey, MyData) VALUES ('update', 1);\r\nGO \r\n\r\n-- Turn off messages\r\nSET NOCOUNT ON\r\n\r\n-- Create loop variables\r\nDECLARE @Index INT\r\nSELECT @Index = 0\r\n\r\n-- Row modification loop (20K)\r\nWHILE (@Index < 20000)\r\nBEGIN\r\n   UPDATE MyTest \r\n   SET MyData = MyData + 1 \r\n   WHERE MyKey = 'update'\r\n   SELECT @Index = @Index + 1\r\nEND\r\n\r\n-- Turn on messages\r\nSET NOCOUNT OFF\r\nGO\r\n<\/pre>\n<\/p>\n
I usually do not endorse products; However, there is a ton of system stress test programs out there.  The free one I chose to download is called CPU Speed Professional<\/a>.  <\/p>\n
Armed with this utility, I wanted to see if my WMI alert would trigger when I ran the CPU load test several times.  I have to confess there seems to be some type of lag between the WMI counter and SQL Server acting on a threshold that was exceeded.  In short, I did get history count to increase on alert executions; However, more testing would be neccessary for this alert to be ready for prime time!<\/p>\n
In conclusion, SQL Server out of the box gives you the tools to create alerts for both standard errors and performance issues.  It is up to you to use these tools to build your own custom performance monitoring strategy.  I STRONGLY suggest creating test scripts for each alert to that you can really trust they will be triggered when you need them.<\/p>\n","protected":false},"excerpt":{"rendered":"
Having set up system and performance alerting, it is now time to test those darn alerts to make sure they work! Alerting can notify a operator via email, paging, or a net send messages. Now adays, most people have a smart phone, not an old fashion pager. Therefore, I usually create a Active Directory group that has both the regular company email of the DBA group and their cell phone eqivalents which are usually the phone number @ provider name dot com. Today, I am going to talk about two…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[179,12,15,180,178,28,29],"class_list":["post-1130","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-error-alert-testing","tag-free-code","tag-john-f-miner-iii","tag-performance-alert-testing","tag-raiseeror","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1130"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1130\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}