locked
Database Status Change Information RRS feed

  • Question

  • Hi All,

    How to know the Database Status (like :- Offline, Shutdown,Suspect,Recovery,Emergency) change Other than the online, with the help of the Database Mail.

    Thanks, Prasad.

    Wednesday, January 5, 2011 8:59 AM

Answers

  • There is no "magic" alert. As I wrote  you in my previous post you need to create a job with step that runs SELECT statetment I posted before.

    Schedule ruinning that job every minute or whatever...Add some login if SELECT statement returns OFFLINE send me an email

    http://www.mssqltips.com/tip.asp?tip=1100


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by SAAD's Wednesday, January 5, 2011 12:35 PM
    Wednesday, January 5, 2011 10:48 AM
    Answerer

All replies

  • SELECT

     

    name,DATABASEPROPERTYEX(name, 'Status')

    FROM

     

    sys.sysdatabases


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 5, 2011 9:16 AM
    Answerer
  • Hi Dimant thanks fro you replay,

    Actually i need once any production database gone to "Offline", "Shutdown","Suspec"t,"Recovery","Emergency" or any other status expect ONLINE Stats, how can i get aleart by mail.

    Thanks,Prasad.

    Wednesday, January 5, 2011 10:09 AM
  • You will have to create a job with that SELECT and schedule it .....if it is not ONLINE, send email

    >>how can i get aleart by mail.

    Configure db email

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'Profile',
         @recipients = 'emal@gmail.com'
         @subject = 'ALTER DB offline';


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 5, 2011 10:16 AM
    Answerer
  • Ok thanksyou very much Dimanat,

    I want small help how to know when which database gone to offline? and that time i need to send aleart mail give me stpes how to do it.

    Thanks, Prasad .

    Wednesday, January 5, 2011 10:42 AM
  • There is no "magic" alert. As I wrote  you in my previous post you need to create a job with step that runs SELECT statetment I posted before.

    Schedule ruinning that job every minute or whatever...Add some login if SELECT statement returns OFFLINE send me an email

    http://www.mssqltips.com/tip.asp?tip=1100


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 5, 2011 10:48 AM
    Answerer
  • There is no "magic" alert. As I wrote  you in my previous post you need to create a job with step that runs SELECT statetment I posted before.

    Schedule ruinning that job every minute or whatever...Add some login if SELECT statement returns OFFLINE send me an email

    http://www.mssqltips.com/tip.asp?tip=1100


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by SAAD's Wednesday, January 5, 2011 12:35 PM
    Wednesday, January 5, 2011 10:48 AM
    Answerer
  • Just to add to this thread.  Databases do not normally change status.  This is caused by either a user process or a problem with the server.  The database becomming offline or suspect, etc is a symptom of another problem.  You need to monitor the real issue.
    Wednesday, January 5, 2011 1:29 PM