This script will check the last backup taken and notify an alert when there is no backup of given threshold.   Table: -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant,upload_date datetime )   SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_No_full_backup] Script Date: 7/6/2015 3:32:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days…

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

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

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…

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