February 19, 2016 at 10:59 pm
Hi,
I am working on creating alert for db status when there is any change except online for all databases
I tested using if @state = 'Online' but it didn't give me any body. I suppose to get all database and state online
declare @state varchar(10)
declare @Database_Name varchar(100)
declare @email varchar(100)
select @state =
case
When a.state = 0 then 'Online'
when a.state = 1 then 'Restoring'
when a.state = 2 then 'Recovery_Pending'
when a.state = 4 then 'Suspect'
when a.state = 5 then 'Emergency'
when a.state = 6 then 'Offline'
end
from sys.databases a
if @state <> 'Online'
begin
set @email = 'The database ' + @Database_Name + ' is currently : ' + @state
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_name_of_DBMAIL',
@recipients = 'Recipient EmailID',
@body = @email,
@subject = 'Database Status' ;
end
February 20, 2016 at 8:16 am
ramana3327 (2/19/2016)
Hi,I am working on creating alert for db status when there is any change except online for all databases
I tested using if @state = 'Online' but it didn't give me any body. I suppose to get all database and state online
declare @state varchar(10)
declare @Database_Name varchar(100)
declare @email varchar(100)
select @state =
case
When a.state = 0 then 'Online'
when a.state = 1 then 'Restoring'
when a.state = 2 then 'Recovery_Pending'
when a.state = 4 then 'Suspect'
when a.state = 5 then 'Emergency'
when a.state = 6 then 'Offline'
end
from sys.databases a
if @state <> 'Online'
begin
set @email = 'The database ' + @Database_Name + ' is currently : ' + @state
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_name_of_DBMAIL',
@recipients = 'Recipient EmailID',
@body = @email,
@subject = 'Database Status' ;
end
You have no WHERE-clause on your check so if the last database that query sees is online your code will not do any work. Also, no need to have a CASE expression to resolve the state to a name, there is a column for that called state_desc. Try this instead:
select name
from sys.databases
where state_desc != 'ONLINE';
Also be aware that your code will only ever alert for one database, i.e. if there are multiple databases not in an online state you will only receive an email for one of them, and a random one at that.
You need to rewrite your code to potentially send multiple emails if there are multiple not-online databases.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 23, 2016 at 10:00 am
You could configure an alert for Error 5084
EventID: 5084
Severity: 10
Logged: Yes
Message: Setting database option %ls to %ls for database %.*ls.
https://msdn.microsoft.com/en-us/library/cc645602(v=sql.100).aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply