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

Thanks

asked 03 Jan, 19:47

gchq's gravatar image

gchq
1814819
accept rate: 33%

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...

(04 Jan, 07:12) Breck Carter

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

(04 Jan, 07:35) gchq
Replies hidden

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.).

(04 Jan, 08:56) Volker Barth

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.

permanent link

answered 04 Jan, 02:12

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 04 Jan, 02:13

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

(04 Jan, 08:39) gchq
Replies hidden

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';
(04 Jan, 08:42) Volker Barth

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

(04 Jan, 08:48) gchq

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?

(04 Jan, 09:22) gchq
Replies hidden

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:)

(04 Jan, 09:29) Volker Barth

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

(04 Jan, 11:21) gchq
showing 4 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 03 Jan, 19:47

question was seen: 365 times

last updated: 04 Jan, 11:21