require db monitor script for sql 2000

  • Hi,

    Need help in writing a script for sql server 2000 to check the status of all db's in the instance and if anyone is not online for any reason (be it suspect,recovery....), I should get any alert. How to implement this?

    Thanks in advance,

  • something like this is what i have saved in my snippets,a nd modified for your question:

    SELECT * FROM

    (

    select name as DBNAME,STATUS,

    CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],

    CASE WHEN (STATUS & 64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],

    CASE WHEN (STATUS & 128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],

    CASE WHEN (STATUS & 256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],

    CASE WHEN (STATUS & 512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],

    CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],

    CASE WHEN (STATUS & 32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],

    CASE WHEN (STATUS & 4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],

    CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]

    from master.dbo.sysdatabases

    ) MyAlias

    WHERE [LOADING] = 'TRUE'

    OR [PRE_RECOVERY] = 'TRUE'

    OR [NOT_RECOVERED] = 'TRUE'

    OR [OFFLINE_(ALTER_DATABASE)] = 'TRUE'

    OR [READ_ONLY_(ALTER_DATABASE)] = 'TRUE'

    OR [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)] = 'TRUE'

    OR [SINGLE_USER_(ALTER_DATABASE)] = 'TRUE'

    OR [EMERGENCY_MODE] = 'TRUE'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • hows this as a starter?

    select * from master..sysdatabases where databasepropertyex([name], 'status') != 'online'

    ---------------------------------------------------------------------

  • Thanks for the responses. I am looking for something like an automated kind where as and when the database state is other than 'ONLINE' , DBA team should be notified or wmi alerts .... Did anyone implemented on Prod?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply