Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    21

    Unanswered: Help - Script that checks for OFFLINE Database

    Hello ,

    I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.

    When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.

    So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.

    The script is:

    Set NoCount on
    DECLARE @dbname VARCHAR(100)
    deCLARE @Status varchar(100)
    Declare @Message VARCHAR(8000)
    DECLARE @date varchar(100)
    set @date = convert(varchar(100), getdate(),109)
    set @Message = ''DECLARE dbname_cursor
    CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
    as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
    order by name
    OPEN dbname_cursor
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    WHILE
    @@FETCH_STATUS = 0
    BEGIN select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ - + @date
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    END
    CLOSE dbname_cursor
    DEALLOCATE dbname_cursor

    print @message
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'testsql2000@is.depaul.edu',
    @TO = N'dvaddi@depaul.edu',
    @server = N'smtp.depaul.edu',
    @subject = N'Status of the Database on Testsqlserver!',
    @type = N'text/html',
    @message = @message


    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The problem is there is no conditional around the exec master.dbo.xp_smtp_sendmail. Try this. At the beginning, check to see if there are any databases offline with something like:
    Code:
    if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE'
      begin
    >>your code<<
      end

  3. #3
    Join Date
    Sep 2006
    Posts
    21
    sorry to ask, but can you be more clear on what you have mentioned. Like after giving the statement that you have given, what I should be giving next as part of my code.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cut-and-paste the code from your post, and place it into the code from MCrowley's post in place of the ">>your code<<" marker.

    -PatP

  5. #5
    Join Date
    Sep 2006
    Posts
    21
    It doesnot seem to be working. It still gives out the blank email , when no database is offline.

    Thanks

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Looks like I missed a close parenthesis, but since you got it to parse, looks like you found that problem with ease. Post what you have, now.

  7. #7
    Join Date
    Sep 2006
    Posts
    21
    The code that now I am executing is :


    if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
    --Set NoCount on
    DECLARE @dbname VARCHAR(100)
    deCLARE @Status varchar(100)
    Declare @Message VARCHAR(8000)
    DECLARE @date varchar(100)
    set @date = convert(varchar(100), getdate(),109)
    set @Message = ''
    --DECLARE dbname_cursor
    --CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
    --as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
    --order by name
    --OPEN dbname_cursor
    --FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    --WHILE
    --@@FETCH_STATUS = 0
    BEGIN select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ '- ' + @date
    --FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    --END
    --CLOSE dbname_cursor
    --DEALLOCATE dbname_cursor
    if (@message <> '')
    print @message
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'testsql2000@is.depaul.edu',
    @TO = N'dvaddi@depaul.edu',
    @server = N'smtp.depaul.edu',
    @subject = N'Status of the Database on Testsqlserver!',
    @type = N'text/html',
    @message = @message
    end

    So ,if the database is offline I am getting the mail correctly.

    But even if no database is offline, it is saying.....command completed successfully and I am getting a blank mail.

    Thanks

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about this
    Code:
    if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
      begin
    Set NoCount on
    DECLARE @dbname VARCHAR(100)
    deCLARE @Status varchar(100)
    Declare @Message VARCHAR(8000)
    DECLARE @date varchar(100)
    set @date = convert(varchar(100), getdate(),109)
    set @Message = ''DECLARE dbname_cursor
    CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
    as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
    order by name
    OPEN dbname_cursor
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    WHILE
    @@FETCH_STATUS = 0
    BEGIN select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ ‘- ‘ + @date
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    END
    CLOSE dbname_cursor
    DEALLOCATE dbname_cursor
    
    print @message
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'testsql2000@is.depaul.edu',
    @TO = N'dvaddi@depaul.edu',
    @server = N'smtp.depaul.edu',
    @subject = N'Status of the Database on Testsqlserver!',
    @type = N'text/html',
    @message = @message
    END

  9. #9
    Join Date
    Sep 2006
    Posts
    21
    Hello Mcrowley,

    I have got the script working. But I am using a different one.The script I have got was,

    SET NOCOUNT ON

    DECLARE @Msg VARCHAR(8000)

    SELECT @Msg = ISNULL(@Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
    FROM (
    SELECT TOP 100 PERCENT @@SERVERNAME ServerName,
    Name,
    CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
    CONVERT(VARCHAR, GETDATE(), 109) Now
    FROM master..sysdatabases
    WHERE status & 512 = 512
    ORDER BY Name
    ) z

    PRINT @Msg

    IF @Msg IS NOT NULL
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'from address',
    @TO = N'to address',
    @server = N'smtpaddress',
    @subject = N'Status of the Database on Testsqlserver!',
    @type = N'text/html',
    @message = @Msg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •