This script will check the always ON group and database status and notify an alert when the AG or database is not online and disconnected. Table: use DBAdata go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60) NULL, [operational_state_desc] [nvarchar](60) NULL, [recovery_health_desc] [nvarchar](60) NULL, [synchronization_health_desc] [nvarchar](60) NULL, Upload_date datetime ) use DBAdata_archive go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60) NULL, [operational_state_desc] [nvarchar](60) NULL, [recovery_health_desc] [nvarchar](60) NULL, [synchronization_health_desc] [nvarchar](60) NULL, Upload_date datetime ) SP: /* --drop table tbl_alwayson_monitoring select * from tbl_alwayson_monitoring use DBAdata go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60)…
-
-
AlwaysON Quorun down status alert notification Automon DBA SPs – AM11
This script will check the always ON quorum status and notify an alert when the quorum is not down and disconnected. Table: use dbadata go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status] [varchar](10) NOT NULL, [Vote] [int] NULL, [Upload_date] [datetime] NULL ) use DBAdata_Archive go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status] [varchar](10) NOT NULL, [Vote] [int] NULL, [Upload_date] [datetime] NULL ) SP: /* --drop table tbl_alwayson_Quorum select * from tbl_alwayson_Quorum use dbadata go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status]…
-
How to use server side trace to capture running query T SQL
Capturing queries by using server side trace In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it. Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data. The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server…
-
Database mirror status alert notification Automon DBA SPs – AM10
This script will check the database mirror status and notify an alert when the mirror is not working and disconnected. Table: use DBAdata go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name] [nvarchar](128) NULL, [mirroring_role_desc] [nvarchar](60) NULL, [mirroring_partner_name] [nvarchar](128) NULL, [mirroring_witness_name] [nvarchar](128) NULL, [mirroring_state_desc] [nvarchar](60) NULL, [mirroring_witness_state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) use DBAdata_archive go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name] [nvarchar](128) NULL, [mirroring_role_desc] [nvarchar](60) NULL, [mirroring_partner_name] [nvarchar](128) NULL, [mirroring_witness_name] [nvarchar](128) NULL, [mirroring_state_desc] [nvarchar](60) NULL, [mirroring_witness_state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) SP: /* use DBAdata go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name]…
-
Database online status alert notification Automon DBA SPs – AM09
This script will check the database status and notify an alert when the database is not online. Table: use DBAdata go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) select * from tbl_database_online_status use DBAdata_archive go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) SP: /* use DBAdata go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) select * from tbl_database_online_status use DBAdata_archive go drop table [tbl_database_online_status] CREATE TABLE…