Menu
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.
960
A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys. databases catalog view or the Status property in the DATABASEPROPERTYEX function.
Bringing Database Online Using SSMSLogin to SQL Server Management Studio. In the Object Explorer, right-click the offline database. In the right-click menu select Tasks >> Bring Online. On selecting Bring Online, a popup window will open and display the status of the action.
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
EDIT: Fix SQL query
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
