Determining if a DB is running

[former_SQLA_member1694879]

Hi there

We have a utility that returns certain criteria after checking that a DB is running. The trouble with this is that if the DB is not running it takes forever to return the error 'Database server not found'

SELECT COUNT(*) as Status FROM sa_db_list() WHERE db_name(Number) = 'MyDBName'

If there a faster method, preferably a Boolean, to determine if the DB is running without throwing an error?

[VolkerBarth]

Have you checked the dbping and dblocate utilities?

[former_SQLA_member1694879]

DBLocate appears to just return a list of local servers/databases

DBPing looks promising though 🙂

Thank you

[VolkerBarth]

DBLocate appears to just return a list of local servers/databases

No, it also lists databases in the immediate network, and I guess via the dbns utility, it could also do so within different subnets.

Also dblocate can list databases on a specified host, too, so this should also help to check whether a database is running or not. But it does not check whether you can connect to it, say, it may have set up to temporarily disallow new connections.

[Breck_Carter]

FWIW Foxhound's custom C "ping" process works like this: It adds AutoStop=NO; AutoStart=NO; CPOOL=NO; to the connection string, then runs EXEC SQL CONNECT USING :connection_string;

The purpose is to determine if the target database is accepting connections or not, and it seems to fail right away if the answer is "no"... but that's not exactly the same as your question "is the DB running?"