April 18, 2012

sp_dbmmonitordropalert (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_dbmmonitordropalert(nvarchar @database_name
, int @alert_id)

MetaData:

 create procedure sys.sp_dbmmonitordropalert  
(
@database_name sysname,
@alert_id int = null
)

as
begin
set nocount on
if (is_srvrolemember(N'sysadmin') <> 1 )
begin
raiserror(21089, 16, 1)
return (1)
end
if(@alert_id is not null and (@alert_id < 1 or @alert_id > 5) )
begin
raiserror( 32036, 16, 1)
return 1
end
-- Is this a system database?
if(@database_name = N'msdb' or @database_name = N'master' or @database_name = N'model' or @database_name = N'tempdb')
begin
raiserror( 32050, 16, 1)
return 1
end
if object_id ( N'msdb.dbo.dbm_monitor_alerts', N'U' ) is null
begin
raiserror( 32041, 16, 1 )
return 1
end
-- Does the database exist?
if not exists (select * from master.sys.databases where name = @database_name)
begin
raiserror(15010, 16, 1, @database_name)
return 1
end

if exists (select * from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)) -- TO DO: cache db_id to local variable
begin
if @alert_id is null
begin
update msdb.dbo.dbm_monitor_alerts set time_behind = null, enable_time_behind = null,
send_queue = null, enable_send_queue = null,
redo_queue = null, enable_redo_queue = null,
average_delay = null, enable_average_delay = null
where database_id = db_id(@database_name)
return 0
end
if @alert_id = 1
begin
update msdb.dbo.dbm_monitor_alerts set time_behind = null, enable_time_behind = null where database_id = db_id(@database_name)
return 0
end
if @alert_id = 2
begin
update msdb.dbo.dbm_monitor_alerts set send_queue = null, enable_send_queue = null where database_id = db_id(@database_name)
return 0
end
if @alert_id = 3
begin
update msdb.dbo.dbm_monitor_alerts set redo_queue = null, enable_redo_queue = null where database_id = db_id(@database_name)
return 0
end
if @alert_id = 4
begin
update msdb.dbo.dbm_monitor_alerts set average_delay = null, enable_average_delay = null where database_id = db_id(@database_name)
return 0
end
if @alert_id = 5
begin
update msdb.dbo.dbm_monitor_alerts set retention_period = null where database_id = db_id(@database_name)
return 0
end
end
-- Row was not there to drop....
return 1
end

No comments:

Post a Comment

Total Pageviews