Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

require db monitor script for sql 2000 Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 8:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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,
Post #1442338
Posted Tuesday, April 16, 2013 8:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
Check this link.

http://www.sqlservercentral.com/Forums/Topic326235-5-1.aspx

Post #1442758
Posted Tuesday, April 16, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 12,918, Visits: 32,088
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442765
Posted Tuesday, April 16, 2013 9:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:35 AM
Points: 5,992, Visits: 12,940
hows this as a starter?

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


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

Post #1442818
Posted Tuesday, April 16, 2013 10:44 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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?
Post #1443092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse