• AutoMon

    CPU high alert notification Automon DBA SPs – AM14

    This script will check the CPU usage and notify an alert when the it reaches the threshold. Table: use dbadata go DROP TABLE tbl_CPU_usgae CREATE TABLE [dbo].[tbl_CPU_usgae]( [servername] [varchar](100) NULL, [SQL_CPU_utilization] [int] NULL, [Idel] [int] NULL, [other_process] [int] NULL, [rundate] [datetime] NULL --,[Upload_date] [datetime] NULL ) use dbadata_archive go DROP TABLE tbl_CPU_usgae CREATE TABLE [dbo].[tbl_CPU_usgae]( [servername] [varchar](100) NULL, [SQL_CPU_utilization] [int] NULL, [Idel] [int] NULL, [other_process] [int] NULL, [rundate] [datetime] NULL, [Upload_date] [datetime] NULL )   SP: /* use dbadata go DROP TABLE tbl_CPU_usgae CREATE TABLE [dbo].[tbl_CPU_usgae]( [servername] [varchar](100) NULL, [SQL_CPU_utilization] [int] NULL, [Idel] [int] NULL, [other_process] [int] NULL, [rundate] [datetime] NULL --,[Upload_date] [datetime] NULL ) use dbadata_archive go DROP TABLE…

  • AutoMon

    AlwaysON availability group status alert notification Automon DBA SPs – AM13

    This script are used to collect SpServerDiagnostics results on target server, when AG is not working. Additional SP: Sp1 USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics_new] Script Date: 15-04-2017 14:29:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* CREATE TABLE [dbo].[SpServerDiagnostics]( [create_time] [datetime] NULL, [component_type] [sysname] NOT NULL, [component_name] [sysname] NOT NULL, [state] [int] NULL, [state_desc] [sysname] NOT NULL, [data] [nvarchar](max) NULL ) */ --DROP PROC [dbo].[usp_SpServerDiagnostics] alter proc [dbo].[usp_SpServerDiagnostics_new] (@Server_name varchar(50)) --with Encryption as begin EXEC('Exec ['+@Server_name+'].MASTER.DBO.usp_SpServerDiagnostics') END GO Sp2 USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics] Script Date: 15-04-2017 14:29:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* CREATE TABLE [dbo].[SpServerDiagnostics]( [create_time] [datetime] NULL,…

  • AutoMon

    AlwaysON availability group status alert notification Automon DBA SPs – AM12

    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)…

  • AutoMon

    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]…

  • AutoMon

    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]…