• AutoMon

    Automon server report alert notification Automon DBA SPs – AM21

    This script will check the automon server status report and notify an alert.   SP: alter procedure USP_DBA_AutoMon_server_Report /* Summary: Send a server which are not monitored by tool Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Send a server which are not monitored by tool ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON --Send an email to DBA team ----------------------------------------------------------------- DECLARE @minid INT DECLARE @maxid INT DECLARE @servername varchar(100) DECLARE @SVR_status varchar(100) -- select * from dbadata.dbo.tbl_get_datafiles_size if exists (select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running') begin DECLARE Svr_running_CUR CURSOR FOR select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running' OPEN Svr_running_CUR FETCH NEXT…

  • AutoMon

    Backup share filer space alert notification Automon DBA SPs – AM20

    This script will check the backup share filer free space and notify an alert.   Table: use dbadata go drop table Backup_filers_name CREATE TABLE [dbo].[Backup_filers_name]( [filer_no] [int] IDENTITY(1,1) NOT NULL, [filer_name] [varchar](100) NOT NULL, [filer_name_original] [varchar](100) NULL, [filer_Satus] [varchar](20) NULL, [access_from_local] [varchar](100) NULL, PRIMARY KEY CLUSTERED ( [filer_name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) drop table Backup_filer_space_check create table Backup_filer_space_check (Filer_name varchar(500),output varchar(1000),date datetime) use DBAdata_Archive go drop table Backup_filer_space_check_final create table Backup_filer_space_check_final (Filer_name varchar(500),Free_space_GB varchar (1000),date datetime) */ /* update Backup_filers_name set filer_satus = 'Not in use' where filer_name in( '\\share\spbackup\', )   SP: /*…

  • AutoMon

    Larger transaction log file sync with recovery model alert notification Automon DBA SPs – AM19

    This script will check the recovery model and larger log files and notify an alert, when it does not sync.   Table: USE [DBAdata] GO drop table tbl_Large_logfile_sync_with_recovery_model_check CREATE TABLE [dbo].[tbl_Large_logfile_sync_with_recovery_model_check]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL, [log_reuse_wait_desc] [sysname] NOT NULL ) USE [DBAdata_archive] GO drop table tbl_Large_logfile_sync_with_recovery_model_check CREATE TABLE [dbo].[tbl_Large_logfile_sync_with_recovery_model_check]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL, [log_reuse_wait_desc] [sysname] NOT NULL, upload_date datetime )   SP: USE [DBAdata] GO SET ANSI_NULLS ON GO SET…

  • AutoMon

    Analysis database recovery model alert notification Automon DBA SPs – AM18

    This script will check the recovery model of databases based on production and non-production policy and notify an alert. SP: SP1 USE [DBAdata] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* USE [DBAdata] GO drop table tbl_recovery_model_non_Prod CREATE TABLE [dbo].[tbl_recovery_model_non_Prod]( [SERVER_NAME] [sysname] NOT NULL, [DB_NAME] [sysname] NOT NULL, [Recovery] [varchar](10) NULL ) USE [DBAdata_archive] GO CREATE TABLE [dbo].[tbl_recovery_model_non_Prod]( [SERVER_NAME] [sysname] NOT NULL, [DB_NAME] [sysname] NOT NULL, [Recovery] [varchar](10) NULL, [CREATE_DATE] [datetime] NULL ) select * from tbl_Error_handling order by Upload_Date desc select * from tbl_recovery_model_non_Prod and name not in (''''''''ReportServer'''''''',''''''''ReportServerTempDB'''''''') */ alter proc [dbo].[USP_recovery_model_Non_prod] /* Summary: Check the recovery mode for non prod Contact: Muthukkumaran Kaliyamoorthy SQL DBA…

  • AutoMon

    No backup for database alert notification Automon DBA SPs – AM17

    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…