Determine if a DB is running

[former_SQLA_member1694879]

What would be the correct SQL statement to determine if a database is running or stopped?

Thanks

[Breck_Carter]

Where do you want to run this "SQL statement"?

Is it a SQL statement you want to run in an existing connection to database A, to determine if database B is running or stopped?

Or is it a SQL statement you somehow want to run without first establishing a connection to any database?

Solutions differ in each case...

[former_SQLA_member1694879]

For each database server we have a 'dummy' running DB so that we can execute SQL statements using that to stop, copy (from a backup) and start another DB. Would like to have an easy utility to check if one is actually running as well.

It would be preferable for the same string to work on both v11 and v17 DB's

[VolkerBarth]

FWIW, you might also use the special "utility_db" for such purposes, unless you really need to store information in the dummy database (such as status of last backup/load etc.).

[VolkerBarth · Accepted solution]

You can only connect to a running database, so there is no direct way via SQL statements to query the database itself whether it is running. (You can however analyse the error code of a failing connect attempt to find out whether the database is not running in contrast to wrong credentials, missing authentication or the like.)

If there are several databases running on the same server, you can connect to one of them and use the sa_db_list() builtin procedure or the db_name()/next_database() functions to query the names of all running databases on that server. - Note, for v16 and above, querying properties of other databases requires particular privileges.

For v12 and below, you might also use the phantom utility_db database to query for databases running on a given server if using that has been enabled.

Additionally, the DBLOCATE (for network servers) and DBPING tools may be of help here.

[former_SQLA_member1694879 · Accepted solution]

sa_db_list returns a list from 0 to x running DB's db_name returns a name if a numeric is submitted (from the above list) Really looking for a method to query a DB name and return either the current status, or at a minimum, if it's actually running or not

[VolkerBarth · Accepted solution]

Yes, see the docs, here's how to use sa_db_list() to get the names of the running databases:

SELECT DB_NAME(Number) FROM sa_db_list();

and return either the current status, or at a minimum, if it's actually running or not

Are you coming from an ASE/MS SQL Server background - where user databases are managed by one central master database, so you know what databases "belong" to a particular server? - It's different with SQL Anywhere, here each database is on its own, and so any database can not "know" whether another database is "active or not", it can just tell whether there are other databases running on the same server (when the querying connection has appropriate privileges) or not. So there's no "status" for databases on a server.

But here's a query to return 1 if the desired database name is running on that engine, and 0 otherwise (and should work with v10 and above):

select count(*) as db_is_running
from sa_db_list()
where db_name(Number) = 'MyDbName';

[former_SQLA_member1694879 · Accepted solution]

If I return a dataset and loop through the results that would work - thank you

[former_SQLA_member1694879 · Accepted solution]

We have a master table that maps where each DB/Server is (DB Name, Server Name, Host Server Name... etc) so the connection string is built dynamically according to that record and ends up querying the correct server.. Looping the record gets the result.

How do you list code in white?

[VolkerBarth · Accepted solution]

How do you list code in white?

You can add "code" with 4 leading blanks per line or put a <pre> tag pair around your text so it is formatted as-is... - and is displayed with white background:)

[former_SQLA_member1694879 · Accepted solution]

It looks like you were editing your reply when I posted my earlier comment. Have now added this concept to a utility that lists all DB's. Sometimes when a customer runs a lockbox file against several DB's it throws an error - prior to running a backup is made of each DB and the software then attempts a restore, but for some reason, and only intermittently, it will fail to start the last DB on the list - finding that and starting it again has been a pain in the butt! Now I can see graphically which one is stopped