Alerts message 2

[eggy168]

Hi, I would like to know how can I create an alert message to let me know a database is running low of disk space? Or is there anyway I can find an inforamtion on how to learn it? I am kinda new of the administrative part of SQL.
Thanks

 

[JayKusch]

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

 

[Corran007]

Very nice jay. a star for you.

As an alternative way, i have a job that runs a dts package that creates a web page that gives me the amount of space availabe on the drives for all my servers. I find this to be quite handy as i just check it every morning. I have it highlight red any one that is less than 5g remainging so i can make preperations to resolve what ever reason it is running out of space for. Another reason i like a web page is that others who might have a need to see the space available can easly access the information.

 

[eggy168]

Thank You J. Kusch and Corran007. I am going to try it.

 

[eggy168]

Hi,
I am kinda new in the creating procedure. After I paste it in the Query Analyzer, it gives me the errors and I see the red color after N'xxxx..Is it a bug?
Thanks for the help

 

[SQLBill]

In QA, the red color identifies a String.

What errors?

-SQLBill

 

[eggy168]

Server: Msg 15043, Level 16, State 1, Procedure sp_addmessage, Line 127
You must specify 'REPLACE' to overwrite an existing message.
Server: Msg 201, Level 16, State 4, Procedure sp_add_category, Line 0
Procedure 'sp_add_category' expects parameter '@name', which was not supplied.
Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 170
The specified '@category_name' is invalid (valid values are returned by sp_help_category).

 

[SimonTheSponge]

Jay,

I was searching for a way to create and alert for diskspace problems and found your little gem (star awarded). I'm a little new to Admin, coming from a development background Could you give a little narrative to help explain how the steps relate and interact with each other, I think I've got a rough idea but it's always good to have it explained. I have a couple of questions (which maybe redundant upon explanation of the script) to help me explain what's happening.

Firstly on where the parameters from the message are being populated from

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.',

I guess these are being populated from the @command parameter that is being passed into sp_add_jobstep

@command = N'exec usp_Monitor_Disk_Space @drivename = ''C'', @spacelimit = 500' - The %s parameter in the message maps to @drivename and the first %d maps to @spacelimit, but where does the 2nd %d as in 'Available space is %d MB' map from - or is this just re-using the first %d parameter again such that specified limit of %d MB such that 'Available space is %d MB' will always say the same as 'specified limit of %d MB'

'specified limit of 500 MB. Available space is 500 MB' (or whatever @spacelimit has been set as)

Secondly - what part of the code or which parameter in the Job sets it to raise the 60001 alert

Thirdly, which is a bit of a newbie question what does the N denote when passing a string parameter eg @name = N'Monitor disc C: space'


Cheers