TSQL Check Database Instance Online

Question

What is the best way to check if an instance of Microsoft SQL Server is up? I have a view that spans two instances of Microsoft SQL Server but before querying it in a stored procedure I would like to verify that the second instance of the server is up with a fallback option of just querying local data.

The second instance is a linked server.

Currently I'm considering a SQL CLR function that can attempt to open a connection with a shorter timeout but I'm wondering if it's something that can be done directly in Transact SQL.

Answer 1

Query sys.sysdatabases through the linked server (you need permissions), here are the values for the status column

1 = autoclose (ALTER DATABASE)

4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

16 = torn page detection (ALTER DATABASE)

32 = loading

64 = pre recovery

128 = recovering

256 = not recovered

512 = offline (ALTER DATABASE)

1024 = read only (ALTER DATABASE)

2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

4096 = single user (ALTER DATABASE)

32768 = emergency mode

4194304 = autoshrink (ALTER DATABASE)

1073741824 = cleanly shutdown

Answer 2

How about just validating that the database you are interested in connecting to is online?

select 
    name,
from sys.databases
where name = 'DatabaseName'
    and state = 0 --Database is online

For the full reference of available columns for the sys.databases catalog view see Books Online: sys.databases