DB Availability

Question

DB Availability

The_SQL_DBA

SSChampion

Points: 10494
More actions
December 23, 2008 at 7:40 am

#125297

How does one check the availability of a DB on a regular basis, with that I intend to ask how should one check the availability of a DB on a day to day basis..can it be achieved as a job?
Checking error logs for error msgs with severity 21 or 23 would be an option I believe..like setting up an alert to email if such severity msgs were to occur..any other options?
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Define availability? You want to know if you can connect to it, if you can query it? Does blocking or excessive locking decrease availability?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 2

I guess I am referring to the literal meaning..like database not in suspect state or offline...as long as it is not in these states I would be fine saying the database is available..

The thing is I googled this stuff before I posted it but didnt find anything for SQL server, so I thought this can be brought up here to find a more precise answer..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You can query the sys.databases view to get the status of the database:

select name, state_desc

from sys.databases

Now exactly how you go about this depends on what you are after. You could check to see if a database is not online and raise an error that is logged and have a severity level alert that notifies you.

IF EXISTS

(select 1

from sys.databases

WHERE state_desc != 'ONLINE')

-- Raiserror here.

You can find reference to the state_desc column in the BOL:

http://technet.microsoft.com/en-us/library/ms178534(SQL.90).aspx