Alrighty ... here we go. I have generated code below that you should cut, paste and execute in Query Analyzer(QA). The code below will create a message, alert and job for monitoring the disc space avaiable on drive C. This particular alert/job fires off daily and check if the available disc space is above a set threshhold of 500MB. If the available space is BELOW this limit when the job runs, the job fails reporting back in the job history the error text we created.
So ... run this in, open the new job created, and in the step that runs the check, set the 500 to lets say 20, for 200000MB or 200GB. We know most C drives DO NOT have that much free room so when we run the job in a moment, we know it will fail and we can see the resulting error message in the job.
SO run the job and take a peak. This is just a simple example of the strutures in creating an alert, message and job. You can get very complex with alerting in this fashion.
-- Create Message for Job
EXEC sp_addmessage @msgnum = 60001, @severity = 16,
@msgtext = N'Availabe space on drive %s is less then specified limit of %d MB. Available space is %d MB.',
@lang = 'us_english'
-- Create Alert
IF (SELECT COUNT(*) FROM msdb.dbo.sysalerts WHERE name = N'Free disk space reached specified limit') < 1
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Free disk space reached specified limit', @message_id = 60001, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 5, @category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Free disk space reached specified limit', @operator_name = N'AccountingDataPull', @notification_method = 1
END
-- SCRIPTING JOB: Monitor Drive C: Disc Space
begin transaction
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
if (select count(*) from msdb.dbo.syscategories where name = N'MGAM Backups') < 1
execute msdb.dbo.sp_add_category N'MGAM Backups'
BEGIN
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'Monitor Drive C: Disc Space', @enabled = 1, @start_step_id = 1, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = N'This Job checks the available space on Drive C: and writes an error to the BusinessProcesses..MGAMBackupFailedJobs table if the space available is LESS than the buffer set as one of the input parameters.', @category_name = N'MGAM Backups', @owner_login_name = N'AccountingDataPull'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @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 = 1, @os_run_priority = 0, @flags = 0, @step_name = N'Check Drive C: Now', @subsystem = N'TSQL', @command = N'exec usp_Monitor_Disk_Space @drivename = ''C'', @spacelimit = 500', @database_name = N'BusinessProcesses'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Monitor disc C: space', @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20030121, @active_end_date = 99991231, @active_start_time = 100000, @active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
END
commit transaction
goto EndSave
QuitWithRollback:
if (@@TRANCOUNT > 0) rollback transaction
EndSave:
GO
Thanks
J. Kusch