Monday, 18 November 2013

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)