IF ((SELECT 1 FROM MASTER.SYS.all_objects O WHERE O.name = 'fn_ifNeedFullBackup') IS NULL) BEGIN use master declare @fnsql nvarchar(max) = ' CREATE FUNCTION [fn_ifNeedFullBackup] ( @DBName sysname) RETURNS BIT AS BEGIN DECLARE @NeedFull BIT; DECLARE @LastLogBackupLSN NUMERIC (25,0); DECLARE @RecoveryModel TINYINT; SELECT @LastLogBackupLSN = [last_log_backup_lsn] FROM sys.database_recovery_status WHERE [database_id] = DB_ID (@DBName); SELECT @RecoveryModel = [recovery_model] FROM sys.databases WHERE [database_id] = DB_ID (@DBName); IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL) SELECT @NeedFull = 0 ELSE SELECT @NeedFull = 1; RETURN (@NeedFull); END;' exec (@fnsql) end EXEC SP_MSFOREACHDB 'USE [?]; IF (DB_NAME() IN (''IP24162_TEST'', ''GA24190_Test'', ''UCP_JDP_Test'', ''OS20042_Test'', ''YM24164_TEST'')) BEGIN IF ( SELECT 1 FROM sys.indexes WHERE NAME = ''JOB__PROC_QUEUE'') IS NULL BEGIN BEGIN TRY print ''[?] : JOB__PROC_QUEUE'' DECLARE @SQL NVARCHAR(MAX) = '' CREATE NONCLUSTERED INDEX [JOB__PROC_QUEUE] ON [dbo].[job] ( [process_id] ASC, [queue] ASC, [status] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'' EXEC (@SQL) END TRY BEGIN CATCH DECLARE @email varchar(500) = ''AYEH@digitalriver.com'', @profile_name varchar(100), @message varchar(8000) SELECT TOP 1 @profile_name = name FROM msdb.dbo.sysmail_profile WHERE name IN (''DRSQLSVC'', ''Commerce5 Admin profile'') SELECT @message = ''JOB__PROC_QUEUE FAILED'' EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @profile_name, @recipients = @email, @body = @message, @subject = ''PLEASE GO TO THK TST TO SEE INDEX [JOB__PROC_QUEUE] IN [?]'' END CATCH END END' EXEC SP_MSFOREACHDB 'USE [?]; IF (DB_NAME() IN (''IP24162_TEST'', ''GA24190_Test'')) BEGIN PRINT ''[?] : CHECK IN DB [?]'' DECLARE @CNT INT ;WITH A AS (SELECT DISTINCT obj.name TableName, si.name AS IndexName, sc.Name AS ColumnName, sic.Index_ID , sic.is_included_column [key/Include] FROM sys.indexes si INNER JOIN sys.index_columns sic ON sic.OBJECT_ID = si.OBJECT_ID AND sic.Index_ID = si.Index_ID INNER JOIN sys.columns sc ON si.OBJECT_ID = sc.OBJECT_ID AND sc.Column_ID = sic.Column_ID INNER JOIN sys.objects obj ON obj.object_id = si.object_id WHERE obj.name = ''JOB'' AND si.name = ''JOB__PROC_FK'') SELECT @CNT = COUNT([KEY/INCLUDE]) FROM A WHERE [KEY/INCLUDE] = 0 IF (((SELECT MASTER.DBO.fn_ifNeedFullBackup(''?'')) = 1) AND (@CNT <> 3)) BEGIN print ''[?] : JOB__PROC_FK'' BEGIN TRY DECLARE @SQL NVARCHAR(MAX) = '' DROP INDEX JOB__PROC_FK ON JOB CREATE NONCLUSTERED INDEX [JOB__PROC_FK] ON [dbo].[job] ([process_id] ASC,[status] ASC,[end_job_datetime] ASC) INCLUDE ( [job_id],[cutoff_date],[drop_date],[n_candidate_records],[n_selected_records],[start_date_time], [n_updated_records],[description],[job_priority],[job_description],[has_errors],[manual_review_fulfillment], [step_number],[step_name],[last_pulse],[queue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'' EXEC (@SQL) END TRY BEGIN CATCH DECLARE @email varchar(500) = ''AYEH@digitalriver.com'', @profile_name varchar(100), @message varchar(8000) SELECT TOP 1 @profile_name = name FROM msdb.dbo.sysmail_profile WHERE name IN (''DRSQLSVC'', ''Commerce5 Admin profile'') SELECT @message = (''JOB__PROC_FK FAILED '') EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @profile_name, @recipients = @email, @body = @message, @subject = ''PLEASE GO TO THK TST TO SEE INDEX [JOB__PROC_FK] IN [?]'' END CATCH END END' |
sql | SQL Server > sqlsrv | T-SQL >