Query to monitor SQL Server Database Status

Monitoring database status is the primary task for DBA’s. Instead of connecting & checking, let us schedule the below mentioned simple query and run through task scheduler or SQL Server Agent on specific frequency and mention the specific Email to reach out in case of any issues. This makes DBA’s life easier.

SELECT

                                    name AS [SuspectDB],

                                    DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],

                                    DATABASEPROPERTY(name, N'IsOffline') AS [Offline],

                                    DATABASEPROPERTY(name, N'IsEmergencyMode')    AS  [Emergency],

                                    has_dbaccess(name) AS [HasDBAccess]

FROM

                                    sysdatabases

WHERE

                                    (DATABASEPROPERTY(name, N'IsSuspect') = 1)

                                    OR (DATABASEPROPERTY(name, N'IsOffline') = 1)

                                    OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)

                                    OR (has_dbaccess(name) = 0)