• AutoMon

    New database user creation alert notification Automon DBA SPs – AM16

    This script will check the new database and user creation and notify an alert, if it is tempdb then service restarted. Table: use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL ) use dbadata_archive go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL, [Upload_date] [varchar](10) NULL )   SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_GETNEW_DB_AND_LOGIN] Script Date: 9/27/2013 7:44:10 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME]…

  • AutoMon

    Memory RAM high alert notification Automon DBA SPs – AM15

    This script will check the Memory RAM usage and notify an alert when the it reaches the threshold.   Table: use dbadata go drop table tbl_memory_usgae_2012_New go CREATE TABLE [dbo].[tbl_memory_usgae_2012_New]( [servername] [varchar](100) NULL, [Physical_RAM_mB][bigint] NULL, [Physical_RAM_Use_mB] [bigint] NULL, [Physical_RAM_Available_mB] [bigint] NULL, [Percentage use] [numeric](23, 10) NULL, [Locked_page_RAM_mB] [bigint] NULL, [Max_RAM] sql_variant NULL, [Min_RAM] sql_variant NULL, [PLE] [bigint] NULL, [Version] [varchar](10) NULL, [cntr_type] [int] NULL ) use dbadata_archive go drop table tbl_memory_usgae_2012_New go CREATE TABLE [dbo].[tbl_memory_usgae_2012_New]( [servername] [varchar](100) NULL, [Physical_RAM_mB][bigint] NULL, [Physical_RAM_Use_mB] [bigint] NULL, [Physical_RAM_Available_mB] [bigint] NULL, [Percentage use] [numeric](23, 10) NULL, [Locked_page_RAM_mB] [bigint] NULL, [Max_RAM] sql_variant NULL, [Min_RAM] sql_variant NULL, [PLE] [bigint] NULL, [Version] [varchar](10) NULL, [cntr_type] [int] NULL, [Upload_date]…

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