December 31, 2009 at 7:54 am
Hi there
I have created an alert to fire when CPu usage goes above 90%:
SELECT * FROM __InstanceModificationEvent WITHIN 180 WHERE TargetInstance ISA "Win32_PerfFormattedData_PerfOS_Processor" AND TargetInstance.PercentProcessorTime > 90
I wanted it to kick-off a job which puts the PercentProcessorTime into a table:
INSERT INTO CPU(Use)
VALUES (N'$(ESCAPE_SQUOTE(WMI(TextData)))')
Unfortunately the job fails with the following error:
Message
Unable to start execution of step 1 (reason: Error retriving WMI variable WMI(TextData): 0x80041002). The step failed.
Any ideas how I could get this to work?
December 31, 2009 at 8:20 am
Is this WQL? Where are you running this query from? In SSIS?
December 31, 2009 at 8:24 am
An alert and a job:
Alert:
EXEC msdb.dbo.sp_add_alert @name=N'CPU Alert',
@message_id=0,
@severity=0,
@enabled=0,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\cimv2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 180 WHERE TargetInstance ISA "Win32_PerfFormattedData_PerfOS_Processor" AND TargetInstance.PercentProcessorTime > 25',
@job_id=N'892ab371-3ce3-42a7-9f8d-b846496db723'
Job:
USE [msdb]
GO
/****** Object: Job [CPU Use] Script Date: 12/31/2009 15:25:11 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/31/2009 15:25:11 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CPU Use',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 12/31/2009 15:25:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO CPU(Use)
VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
@database_name=N'TestDatabase',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
February 8, 2010 at 7:20 am
Shameless bump.
April 8, 2010 at 10:59 pm
I have the answer for you my friend but it might not serve your purpose completely ...
Youhave created the CPU table and feeding Textdata to it ....thats where the pain is .....I am going through the same pain .....when you execute this script originally using cscript ...it works fine ..
But its blowing up only at job execution .
So remove the text data column and capture only the time and server name in the CPU table ..
Arrange for a mail saying the at this time the CPU is > 90%...
Let me show you a script that I am working on since last few days for memory threshold ....
you just neds slight modification ....
The errror number 0x80041002 means incorrect column name ...but its not correct ..
Looks lke a bug ...will open a bug with Microsoft ..and see what happens ...
Reach me @ hi_abhay78@yahoo.co.in.
/*******************************************************************************************
* This script will create an Alert to Monitor Create User event.
* The alert will run a job and the job will enter data in a table.
*
* For any suggestion contact :gur.sethi@in.ibm.com and abhay.chaudhary@in.ibm.com
*******************************************************************************************/
/* Step 1: creating the table to capture the Event information */
USE Master
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[memory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[memory]
GO
CREATE TABLE [dbo].[memory] (
[PostTime] [datetime] NOT NULL default (getdate()) ,
[computerName] sql_variant Not Null ,
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_MEMORY_Flag] DEFAULT ((0)),
[availableMBytes] int
) ON [PRIMARY]
GO
CREATE INDEX [Memory_IDX01] ON [dbo].[memory]([recordid]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
/*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
/*Service account and sql operator option are optional*/
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture memory Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Memory Event', @delete_unused_schedule=1
GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N'<job_owner_account>'
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N'<sql_agent_operator>'
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Memory Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to memory events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/*Step 3: Insert graph into LogEvents*/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
declare @@server sql_variant
select @@server =serverproperty (''machinename'')
INSERT INTO memory (
PostTime,
Computername,
availableMBytes
)
VALUES (
GETDATE(),
@@server,
N''$(ESCAPE_Dquote(WMI("availableMBytes")))'')',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
/*Creating the alert and associating it with the Job to be fired */
USE [msdb]
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Memory_event')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to memory_event'
GO
DECLARE @server_namespace varchar(255)
SET @server_namespace = N'\\.\root\Cimv2\'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to memory_event',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Cimv2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Win32_PerfFormattedData_PerfOS_Memory'' AND TargetInstance.AvailableBytes > 256',
@job_name='Capture memory Event' ;
--EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to memory_event', @operator_name=N'Test', @notification_method = 1
--GO
--/* Step 5: Create a stored proc for sending the [Create_user] information as .CSV file */
--Create proc [dbo].[Deadlock_rpt]
--as
--DECLARE @SQL varchar(2000)
--DECLARE @date varchar (2000)
--DECLARE @File varchar(1000)
--select @date= convert(date,GETDATE())
--SET @SQL = 'select * from [Create_user] where flag = 0'
--SET @File = '[Create_user] report'+@date+'.csv'
--EXECUTE msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
--@recipients = 'your email.com',
--@subject = 'Deadlock report',
--@body = '***URGENT***Attached please find the [Create_user] report',
--@query =@SQL ,
--@attach_query_result_as_file = 1,
--@query_attachment_filename = @file,
--@query_result_header = 1,
--@query_result_separator = ' ',
--@query_result_no_padding = 1,
--@query_result_width = 32767
--/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
--update dbo.[Create_user] set flag = 1 where flag = 0
--go
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
April 8, 2010 at 11:02 pm
you can join my blog @ http://ms-abhay.blogspot.com/...
Like you I am a WMI / VB lover ....and will not rest till crack this stuf 🙂 ..
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
April 8, 2010 at 11:07 pm
the script text says that its for create user ....but its not .Actually i have tons of alerts created ..and when i wanted to create the memory alert i just modified the create_user one so the documentation text is not changed ..
Please forgive ...
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply