This is a question one of my friends asked me today at work and l decided to write it out and share with anyone who might need it. How do you ping a database on a server to see if its in a healthy state. Assuming your writing an application and you want to test connection to the database before actually connecting to it or send out some message when the database is not connecting. So the response I have is to run select 1 on the database in question and if it fails then it means there are some issues querying on the database. In my typical situation we are trying to run some monitoring scripts on bunch of servers to lightly check all databases on the server are accepting connections or fail if they are not so l wrote below script to simple run he query select 1 on all the databases on a given server.
IF OBJECT_ID('tempdb..#PingStatus') IS NOT NULL
DROP TABLE #PingStatus
CREATE TABLE #PingStatus(dbname VARCHAR(1000),Result INT)
BEGIN TRY
DECLARE @command VARCHAR(1000)
SET @command = 'USE ?
INSERT INTO #PingStatus(dbname,Result)
SELECT ''?'', 1'
EXEC sp_MSforeachdb @command
SELECT 'Success' AS PingStatus,'Success' AS msg
END TRY
BEGIN CATCH SELECT 'Failed' AS PingStatus,ERROR_MESSAGE() AS msg END CATCH
Note we are not using cursors to iterator sysdatabases but rather we are using the inbuilt procedure sp_msforeachdb which iterate and returns list of all databases on the server. The question ? refers to the database name return in each iteration and we use the sql query ‘USE ?’ to connect to that specific database and run the query SELECT 1 and insert the outcome into the #pingstatus table. When we are able to run the query on all the databases, we just return success as the ping status in the try block. On the other hand, if there is issue query any of the returned databases, an error will be thrown and the catch block will be executed with failed as the ping status. The error message encountered is returned with the ERROR_MESSAGE() inbuilt sql server function.
This operation is very light and will have almost no impact on any system so can be used to quickly monitor or ping the databases on many servers to determine if they are accepting connections or not.
How do you check if a database is connectable in your applications?
