sql: daily check

Script

----This is a temporary procedure, used to print information in Messages box-----------
----In each session, it could only be executed one time
create procedure #DailyCheckPrint
	@Casecnt int,
	@DailyCheck_title nvarchar(100),
	@DailyCheck_Mode nvarchar(100),
	@OperationSteps nvarchar(4000),
	@CheckResult nvarchar(100),
	@ResultCnt nvarchar(10) = 0,
	@PassFlag int = 0
as
begin
	print '---------------------------------------------------------------------------------------'
	set @DailyCheck_title = ' Daily Check '+ substring(convert(nvarchar(5),(@Casecnt)),2,4) +' : ' + @DailyCheck_title
	print @DailyCheck_title

	print '     Check Mode : ' + @DailyCheck_Mode
		
if @DailyCheck_Mode = 'Manual'
	begin
		set @OperationSteps = 'Operation Steps : ' + @OperationSteps
		print @OperationSteps

		set @CheckResult = '   Check Result : (PASS or your comment)'
		print @CheckResult
	end
else if @DailyCheck_Mode = 'SQL'
	begin							 
		print '   Result Count : ' + @ResultCnt

		if @PassFlag = 1 
			print '   Check Result : PASS'
		else
			begin
				print '   Check Result : Not Pass, Need further operations'

				set @OperationSteps = 'Operation Steps : ' + @OperationSteps
				print @OperationSteps
			end 
		print 'Further comment : null'
	end
end
go


---------------- daily check initialization ----------------
declare @DailyCheck_title nvarchar(300)
declare @DailyCheck_Mode nvarchar(100)
declare @ResultCnt nvarchar(6) = 0
declare @DateTime datetime
declare @ExecuteSQL nvarchar(4000)
declare @OperationSteps nvarchar(4000)
declare @Executor nvarchar(20) 
declare @CheckResult nvarchar(100) = '   Check Result : (PASS or your comment)'
declare @Casecnt int = 100
declare @PassFlag int = 0


---------------- print general information ----------------
select @Executor = SESSION_USER
select @DateTime = getdate()
print convert(varchar(30),getdate(),114) + ': Today is ' + convert(varchar(30),@DateTime,104)
print convert(varchar(30),getdate(),114) + ': The daily check is done by ' + @Executor
print convert(varchar(30),getdate(),114) + ': Now daily check script starts'


---------------- daily check items -----------------------

----------------------------------------------------------------------------------------------------
set @DailyCheck_title = 'Manual-SIAM'							----add or modify the title
set @DailyCheck_Mode  = 'Manual'						----add or modify the mode, it could only use "SQL" or "Manual"
set @OperationSteps   = 'Check the SIAM Incident Queue for High prio tasks
				See if any prio 1 or prio 2 incidents are in our queue'	----add simple operation steps

--Do not modify this command-------------
set @Casecnt += 1
exec #DailyCheckPrint @Casecnt, @DailyCheck_title, @DailyCheck_Mode, @OperationSteps, @CheckResult


----------------------------------------------------------------------------------------------------
set @DailyCheck_title = 'PoolMBDuplicateApproverOrOwner'	----add or modify the title
set @DailyCheck_Mode  = 'SQL'				----Don't change it

select @ResultCnt = count(1) from (
-------------------------------------------start of checking sql 
SELECT UID_JOB,BasisObjectKey FROM JobQueue WHERE
ready2exe = 'frozen'
AND (JobChainName = 'MR_PoolMailbox_Insert' or JobChainName = 'MR_DistributionGroup_Insert')
AND ErrorMessages like '%Error during execution of statement: insert into PersonInOrg%'

-------------------------------------------end of checking sql 
) cmd

-----------judge the executing result
if (@ResultCnt = 0)  
	set @PassFlag = 1 
else
begin
	set @PassFlag = 0
	set @OperationSteps = 'Please check the workaround from TA841839'
end
-----------Below no need to modify 
set @Casecnt += 1
exec #DailyCheckPrint @Casecnt, @DailyCheck_title, @DailyCheck_Mode, @OperationSteps, @CheckResult, @ResultCnt, @PassFlag

Readme

INTRODUCTION

The script is used to assist to do the daily check, consisting of 4 parts.
The first part is temporary procedure #DailyCheckPrint, which is used to print daily check information and sql check result.
The second part is ‘daily check initialization’, which is used to declare the parameters.
The third part is ‘print general information’, which is used to print some general information
The fourth part is ‘daily check items’, which contains the all the checking items.

EXECUTE

Open SQL Server Management Studio, and choose the prod database server, select the iamdb, create one query page.
Copy all the daily check script to SQL Server Management Studio query page.
Click Execute button to run the script, simply check the output messages, if there is something in red.
Copy the Message result to one notepad and save it to a txt file.
Follow the message content to do the daily check, You can add your checking result in this file.

ADD

There are now 2 template modes, manual mode and SQL mode.

Manual template

If you are adding one manual daily check item, please copy the template to the script, change the value of DailyCheck_title, OperationSteps

SQL template

If you are adding one SQL daily check item, please copy the template to the script, change the value of DailyCheck_title, add the check sql, change the value of OperationSteps

Attention

1. if the checking sql have “Order by”, please comment it.
2. If there are single quotation marks in OperationSteps, it would cause ambiguity. replace one single quatation mark to two marks.
   e.g. CHANGE ready2exe=‘frozen’ To ready2exe=’‘frozen’’
3. Before adding the new item script to the general script, please don’t forget to test it.

MODIFY

You can follow the steps of {ADD}

DELETE

If you want to delete one daily check, just comment or delete the item from line “set @DailyCheck_title” to line “exec #DailyCheckPrint”.

TEST

It is suggested that each modification of daily check script should be tested.

Test steps

• Open a new session window in SQL Server Management Studio, select correct database.
• Copy the first part of the script to the query window, execute it, delete it. (Script parts is introduced in the head of this document)
• Copy the second part of the script to the query window.
• Copy your daily check item code to the query window, execute all the script
• Check the execution result. If the execution reuslt is fine, you can add your code to the main script. If there is something wrong, check and fix it.

Template

Manual template

----Manual template
--If you are now adding one Manual daily check, please copy the template to the script and only change the value of DailyCheck_title, OperationSteps
----------------------------------------------------------------------------------------------------
set @DailyCheck_title = ''							----add or modify the title
set @DailyCheck_Mode  = 'Manual'						----Don't change it
set @OperationSteps   = 'Please refer operation manual'	----add simple operation steps

--Do not modify this command-------------
set @Casecnt += 1
exec #DailyCheckPrint @Casecnt, @DailyCheck_title, @DailyCheck_Mode, @OperationSteps, @CheckResult

SQL template

----SQL template
--If you are now adding one SQL daily check, please copy the template to the script, 
--change the value of DailyCheck_title, add the check sql, change the value of OperationSteps

--Attention:
--1. if the checking sql have "Order by", please comment it.
--2. If there are single quotation marks in OperationSteps, it would cause ambiguity. replace one single quatation mark to two marks.
---- e.g. CHANGE ready2exe='frozen' To ready2exe=''frozen''
--3. Before adding the new item script to the general script, please don't forget to test it.

----------------------------------------------------------------------------------------------------
set @DailyCheck_title = ''					----add or modify the title
set @DailyCheck_Mode  = 'SQL'				----Don't change it

select @ResultCnt = count(1) from (
-------------------------------------------start of checking sql 

-------------------------------------------end of checking sql 
) cmd

-----------judge the executing result
if (@ResultCnt = 0)  
	set @PassFlag = 1 
else
begin
	set @PassFlag = 0
	set @OperationSteps = 'Please check operations document to implement'
end
-----------Below no need to modify 
set @Casecnt += 1
exec #DailyCheckPrint @Casecnt, @DailyCheck_title, @DailyCheck_Mode, @OperationSteps, @CheckResult, @ResultCnt, @PassFlag