[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Code to check for database availability

Posted on 2005-04-20
7
Medium Priority
?
198 Views
Last Modified: 2010-03-19
Hello-

I am looking for code that will allow me to check the availabiltiy or status of a database.

I have a job that needs to wait and check for this database availabilty before executing.

Does anything exist?

Thanks
0
Comment
Question by:DougR_MS
7 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13826786
You can use the sp_helpdb command.

EXEC sp_helpdb

Without parameters, it will show all the database and their status.
0
 
LVL 5

Expert Comment

by:obahat
ID: 13826907
How do you define availability? Do you need to check that the DB is up and running?
Just select top 1 of anything.
Please provide more info.
0
 

Author Comment

by:DougR_MS
ID: 13826948
I was making this too hard on myself.

SELECT will return an error if it can't select/connect, I just need to trap that error and continue to try until no longer the error.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13827159
Select top 1 does an actual data read....use select @@servername or select getdate() instead....
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13827245
Try this:

DECLARE @status VARCHAR(20)

SET @status = CAST(DATABASEPROPERTYEX('SecurityDb', 'STATUS') AS VARCHAR(20))

IF @status <> 'ONLINE'
   ...


Naturally please see Books Online for more info on DATABASEPROPERTYEX, its parameters and return values.
0
 
LVL 5

Expert Comment

by:obahat
ID: 13827248
LOL. I was only making the point that the question is fundumentally bogus.

Good comment though :)

LOL.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 13827254
You can of course also test it directly, without using a variable:


IF CAST(DATABASEPROPERTYEX('databaseName', 'STATUS') AS VARCHAR(20)) <> 'ONLINE'
    ...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question