Alert on db suspect mode

  • 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

  • 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

  • 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