Using WMI and SQL Agent to fire low memory threshold alert ...

This will work perfectly ....The only thing I wanted to add to the table was when it alerts you it should also fill the column with available MBytes so that you know how much memory was available .......But after trying it for 2 days , I realized that the class through which I am checking another class (Perfmon >> memory >> Available Mbytes) does not have a column for this.I am using "_instance modification" class .May be its due to this that the alert is fired but the job fails when it inserts the availableMbytes ...because this column is not in _instancemodificationevent class...the error number also suggests that .

By the way this one will only alert if your RAM is > 256 every 10 seconds ....this is because I wanted to test it ....you need to modify it to < 256 and every 300 seconds ...so that you get alert every 5 mins or whatever you decide ....


/*******************************************************************************************
* This script will create an Alert to Monitor Physical RAM reaching a low threshold.
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/

/* 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)),
) 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 table we 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''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

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
)

VALUES (
GETDATE(),
@@server)
',
@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(Respond to memory_event2000)
--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 = 'low memory threshold reached...',
--@body = '***URGENT***Attached please find the low memory threshold 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