AutoMon

Analysis database recovery model alert notification Automon DBA SPs – AM18

Advertisements

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
Description: Check the recovery mode for non prod

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorhty     Updated the 2012 functionality                   


*/
--with Encryption
as
begin

    DECLARE @server_name SYSNAME
      DECLARE @DESC SYSNAME
      DECLARE @sql varchar(max)
      DECLARE @minrow int
      DECLARE @maxrow int

TRUNCATE TABLE tbl_recovery_model_non_Prod

declare @Recovery table (id int  primary key identity, 
servername varchar(100),Description varchar(100)) 
 
insert into @Recovery

select Servername , Description   from dbadata.dbo.dba_all_servers 
--WHERE Description  not LIKE '%ip%' and Description  not LIKE '%KIL%'
WHERE Category  not in ('LIVE','PROD') and SVR_status ='running' and version <>'SQL2000'

 -- select Category  from dbadata.dbo.dba_all_servers group by Category
 -- select *  from dbadata.dbo.dba_all_servers where Category not in ('PROD','live')


 --select *  from dbadata.dbo.dba_all_servers
SELECT @minrow = MIN(id)FROM   @Recovery
SELECT @maxrow  = MAX(id) FROM   @Recovery
 
 while (@minrow <=@maxrow)
 begin
 BEGIN TRY
 
 select @Server_name=Servername ,
 @Desc=Description   from @Recovery where ID = @minrow 

set @sql=
'EXEC(''SELECT * from OPENQUERY(['+@server_name+'],
''''select '''''''''+@DESC+''''''''',name,recovery_model_desc from sys.databases 
where recovery_model_desc <>''''''''simple'''''''' and  database_id not in (1,2,3,4) 
'''')'')
'
insert into dbadata.dbo.tbl_recovery_model_non_Prod
exec(@sql)
--SELECT @sql
end try
BEGIN CATCH
SELECT @SERVER_NAME,ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
insert into tbl_Error_handling
 
SELECT @SERVER_NAME,'Recovery',[Error_Line] = ERROR_LINE(),[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),[Error_State] = ERROR_STATE(),
[Error_Message] = ERROR_MESSAGE(),GETDATE()


END CATCH
 
set @minrow =@minrow +1 
end

insert into DBAdata_Archive.dbo.tbl_recovery_model_non_Prod
select *,getdate() from tbl_recovery_model_non_Prod


END


SP2

USE [DBAdata]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
USE [DBAdata]
GO
drop table tbl_recovery_model
CREATE TABLE [dbo].[tbl_recovery_model](
  [SERVER_NAME] [sysname] NOT NULL,
  [DB_NAME] [sysname] NOT NULL,
  [Recovery] [varchar](10) NULL
  
  )

USE [DBAdata_archive]
GO

  CREATE TABLE [dbo].[tbl_recovery_model](
  [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
and name not in (''''''''ReportServer'''''''',''''''''ReportServerTempDB'''''''')
*/

--drop proc [USP_recovery_model]

alter proc [dbo].[USP_recovery_model_pord]
/*
Summary:     Check the recovery mode for prod
Contact:     Muthukkumaran Kaliyamoorthy SQL DBA
Description: Check the recovery mode for prod

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorhty     Updated the 2012 functionality                   


--*/
with Encryption
as
begin

    DECLARE @server_name SYSNAME
      DECLARE @DESC SYSNAME
      DECLARE @sql varchar(max)
      DECLARE @minrow int
      DECLARE @maxrow int

TRUNCATE TABLE tbl_recovery_model

declare @Recovery table (id int  primary key identity, 
servername varchar(100),Description varchar(100)) 
 
insert into @Recovery

select Servername , Description   from dbadata.dbo.dba_all_servers 
WHERE Category  in ('LIVE','PROD') and SVR_status ='running' and version <>'SQL2000'

 -- select Category  from dbadata.dbo.dba_all_servers group by Category
 -- select *  from dbadata.dbo.dba_all_servers where Category in ('PROD','live')

SELECT @minrow = MIN(id)FROM   @Recovery
SELECT @maxrow  = MAX(id) FROM   @Recovery
 
 while (@minrow <=@maxrow)
 begin
 BEGIN TRY
 
 select @Server_name=Servername ,
 @Desc=Description   from @Recovery where ID = @minrow 

 /*
set @sql=
'EXEC(''SELECT * from OPENQUERY(['+@server_name+'],
''''select '''''''''+@DESC+''''''''',name,DATABASEPROPERTYEX(Name,''''''''RECOVERY'''''''') as Recovery,
GETDATE() as [CREATE_DATE] from master.dbo.sysdatabases  
where DATABASEPROPERTYEX(Name,''''''''RECOVERY'''''''') =''''''''simple'''''''' and  dbid not in (1,2,3,4) 
'''')'')
'
*/

set @sql=
'EXEC(''SELECT * from OPENQUERY(['+@server_name+'],
''''select '''''''''+@DESC+''''''''',name,recovery_model_desc from sys.databases 
where database_id not in (1,2,3,4) -- and recovery_model_desc =''''''''simple''''''''
'''')'')
'

insert into dbadata.dbo.tbl_recovery_model
exec(@sql)
--SELECT (@sql)
end try
BEGIN CATCH
SELECT @SERVER_NAME,ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
insert into tbl_Error_handling
 
SELECT @SERVER_NAME,'Recovery',[Error_Line] = ERROR_LINE(),[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),[Error_State] = ERROR_STATE(),
[Error_Message] = ERROR_MESSAGE(),GETDATE()


END CATCH
 
set @minrow =@minrow +1 
end

insert into DBAdata_Archive.dbo.tbl_recovery_model
select *,getdate() from tbl_recovery_model


END


 

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: