April 18, 2012

sp_dbmmonitorhelpalert (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_dbmmonitorhelpalert(nvarchar @database_name
, int @alert_id)

MetaData:

 create procedure sys.sp_dbmmonitorhelpalert  
(
@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 object_id ( N'msdb.dbo.dbm_monitor_alerts', N'U' ) is null
begin
raiserror( 32041, 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
-- 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

declare @value int,
@enabled bit
if (@alert_id is null)
begin
declare @results2 table( -- remove temp table as done in other help sp.
alert_id int,
threshold int null,
enabled bit null
)
insert into @results2 select 1, time_behind, enable_time_behind from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
insert into @results2 select 2, send_queue, enable_send_queue from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
insert into @results2 select 3, redo_queue, enable_redo_queue from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
insert into @results2 select 4, average_delay, enable_average_delay from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
select * from @results2

return 0
end
else
begin
if(@alert_id < 1 or @alert_id > 5)
begin
raiserror( 32036, 16, 1)
return 1
end

if @alert_id = 1
begin
select @value = time_behind, @enabled = enable_time_behind from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
end
if @alert_id = 2
begin
select @value = send_queue, @enabled = enable_send_queue from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
end
if @alert_id = 3
begin
select @value = redo_queue, @enabled = enable_redo_queue from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
end
if @alert_id = 4
begin
select @value = average_delay, @enabled = enable_average_delay from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
end
if @alert_id = 5
begin
select @value = retention_period from msdb.dbo.dbm_monitor_alerts where database_id = db_id(@database_name)
set @enabled = null
end
select @database_name as 'database_name', @value as 'threshold', @enabled as 'enabled'

end
return 0
end

No comments:

Post a Comment

Total Pageviews