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'