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 scripts that will help you test some of the alerts that you might choose to setup.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Choose to install script in msdb USE msdb GO -- Drop existing stored procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Test_Alerts]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].usp_Test_Alerts GO -- Add new stored procedure CREATE PROC dbo.usp_Test_Alerts @VAR_SEVERITY INT = 0 AS BEGIN DECLARE @VAR_MSG VARCHAR(125); SELECT @VAR_MSG = 'Testing alerting on ' + @@SERVERNAME + ' severity ' + STR(@VAR_SEVERITY, 2, 0) + '.'; RAISERROR (@VAR_MSG, @VAR_SEVERITY, 1) WITH LOG; END GO -- Trigger the error severity 17 alert msdb.dbo.usp_Test_Alerts 17 |
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 …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
-- Use AW2008 USE AdventureWorks2008R2 GO -- Make test table CREATE TABLE dbo.MyTest (MyKey VarChar(10), MyData Int); GO -- Add one row INSERT dbo.MyTest (MyKey, MyData) VALUES ('update', 1); GO -- Turn off messages SET NOCOUNT ON -- Create loop variables DECLARE @Index INT SELECT @Index = 0 -- Row modification loop (20K) WHILE (@Index < 20000) BEGIN UPDATE MyTest SET MyData = MyData + 1 WHERE MyKey = 'update' SELECT @Index = @Index + 1 END -- Turn on messages SET NOCOUNT OFF GO |
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.
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!
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.
I do trust all the ideas you have introduced in your post. They are really convincing and can definitely work. Nonetheless, the posts are too short for newbies. Could you please lengthen them a bit next time? Thanks for the post.
Great post, you have pointed out some great details, I likewise conceive this is a very wonderful website.
I went over this web site and I conceive you have a lot of excellent information, saved to bookmarks (:.