Autoridade_SQL_Server_implanta_GerenciaBD_Monitora_Bloqueio_script_unico.txt

sp_configure 'show advanced options',1
go
RECONFIGURE with override
go

sp_configure 'xp_cmdshell',1
go
RECONFIGURE with override
go
USE [msdb]
GO

/****** Object:  Table [dbo].[blocktable]    Script Date: 26/08/2016 14:39:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[blocktable](
	[blockedsid] [int] NULL,
	[blockingsid] [int] NULL,
	[eventinfo1] [varchar](300) NULL,
	[eventinfo2] [varchar](300) NULL,
	[waittime] [int] NULL,
	[hostname] [varchar](20) NULL,
	[dat_bloqueio] [datetime] NULL,
	[program_name1] [varchar](30) NULL,
	[program_name2] [varchar](30) NULL,
	[host_blocking] [char](20) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[trace_dia_geral]    Script Date: 26/08/2016 14:39:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[trace_dia_geral](
	[cod_servidor] [varchar](255) NULL,
	[TextData] [ntext] NULL,
	[DatabaseID] [int] NULL,
	[NTUserName] [nvarchar](128) NULL,
	[HostName] [nvarchar](128) NULL,
	[ApplicationName] [nvarchar](128) NULL,
	[LoginName] [nvarchar](128) NULL,
	[Duration] [bigint] NULL,
	[StartTime] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



USE [msdb]
GO

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[spu_GerenciaBD]    Script Date: 12/18/2018 11:57:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[spu_GerenciaBD] as  
  
-- Evitando exibição de registros  
  
set nocount on  
  
-- Declarando variável para multi-servidor  
  
DECLARE @SERVIDOR_MONITORANDO VARCHAR(100)  
DECLARE @BANCO_MONITORANDO VARCHAR(100)  
DECLARE @TABELA_TABELA_MONITORANDO VARCHAR(100)  
DECLARE @SERVIDOR_MONITORADO nvarchar(128)  
  
-- Atribuindo variáveis para configuração do GerenciaBD GACO  
  
SET  @SERVIDOR_MONITORANDO = (select @@servername)  
SET  @BANCO_MONITORANDO = 'msdb'  
SET  @TABELA_TABELA_MONITORANDO = 'TRACE_DIA_GERAL'  


DECLARE 
		@ChkSrvName varchar(128)					/*Stores Server Name*/
		,@PhysicalSrvName VARCHAR(128)				/*Stores physical name*/
		,@TrueSrvName varchar(128)					/*Stores Full Name*/

SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128)) 
set @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) 

IF @ChkSrvName IS NULL								/*Detect default or named instance*/
	BEGIN 
		SET @TrueSrvName = @PhysicalSrvName
	END 
ELSE
	BEGIN
		SET @TrueSrvName =  @PhysicalSrvName +'\' + @ChkSrvName
		
	END 

print @TrueSrvName 
   
SET  @SERVIDOR_MONITORADO = @TrueSrvName
  
-- Coletando caminho do SQL Server para gravar trace file otimizado, via server trace  
  
declare @rc2 int, @dir nvarchar(4000)    
   
exec @rc2 = master.dbo.xp_instance_regread   
      N'HKEY_LOCAL_MACHINE',   
      N'Software\Microsoft\MSSQLServer\Setup',   
      N'SQLPath',    
      @dir output, 'no_output'   
  
--select @dir AS InstallationDirectory  
--PRINT @dir + '\indicador_performance_dia'  
  
-- Localizando e finalizando server trace anterior somente do GerenciaBD  
  
declare @traceid2 int  
declare @caminho varchar(1000)  
declare @caminho2 varchar(1000)  
  
set @caminho =  'c:\scripts' + '\indicador_performance_dia'  
set @caminho2 =  'c:\scripts' + '\indicador_performance_dia.trc'  
  
SELECT top 1 @traceid2 = traceid FROM :: fn_trace_getinfo(default) where value = @caminho  
  
if @traceid2 is null   
 SELECT top 1 @traceid2 = traceid FROM :: fn_trace_getinfo(default) where value = @caminho2  
  
select @traceid2  
  
if @traceid2 is not null   
begin  
 EXEC sp_trace_setstatus @traceid2, 0   
 EXEC sp_trace_setstatus @traceid2, 2  
  
  
  
-- Definindo comando para contabilização de indicadores do GerenciaBD  
  
 DECLARE @COMANDO_INSERT_SERVIDOR_MONITORANDO VARCHAR(8000)  

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                                            
drop table prodver                    
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))                    
insert into prodver exec master.dbo.xp_msver 'ProductVersion'                    


 --if (select substring(Charcater_Value,1,1)from prodver)<= 8 -- Verificando a versão o server trace  
 
  IF 10 <= 8 
  
  
 SET @COMANDO_INSERT_SERVIDOR_MONITORANDO = 'insert into [' +@SERVIDOR_MONITORANDO + ']' + '.' + @BANCO_MONITORANDO + '.DBO.' + @TABELA_TABELA_MONITORANDO +  
         ' SELECT '+  '''' +@SERVIDOR_MONITORADO +''',textdata, databaseid, ntusername, hostname, applicationname, loginname, duration/1000, starttime   
      from ::fn_trace_gettable(''' + 'c:\scripts' + '\indicador_performance_dia.trc''' + ', default)'  
  
else  
  
 SET @COMANDO_INSERT_SERVIDOR_MONITORANDO = 'insert into [' +@SERVIDOR_MONITORANDO + ']' + '.' + @BANCO_MONITORANDO + '.DBO.' + @TABELA_TABELA_MONITORANDO +  
         ' SELECT '+  '''' +@SERVIDOR_MONITORADO +''',textdata, databaseid, ntusername, hostname, applicationname, loginname, duration/1000000, starttime   
      from ::fn_trace_gettable(''' + 'c:\scripts' + '\indicador_performance_dia.trc''' + ', default)'  

/*  
  USE [msdb]
GO

drop table trace_dia_geral
go

CREATE TABLE [dbo].[trace_dia_geral](
	[cod_servidor] [varchar](255) NULL,
	[TextData] [ntext] NULL,
	[DatabaseID] [int] NULL,
	[NTUserName] [nvarchar](128) NULL,
	[HostName] [nvarchar](128) NULL,
	[ApplicationName] [nvarchar](128) NULL,
	[LoginName] [nvarchar](128) NULL,
	[Duration] [bigint] NULL,
	[StartTime] [datetime] NULL)

	
	
		[CPU] [int] NULL,
	[Reads] [bigint] NULL,
	[Writes] [bigint] NULL,

	[ClientProcessID] [int] NULL,
	[SPID] [int] NULL,

	[EndTime] [datetime] NULL,
	[BinaryData] [image] NULL,

	[DatabaseName] [nvarchar](128) NULL,
	[Error] [int] NULL,
	[EventSequence] [bigint] NULL,
	[GroupID] [int] NULL,

	[IntegerData] [int] NULL,
	[IsSystem] [int] NULL,
	[LoginSid] [image] NULL,
	[NTDomainName] [nvarchar](128) NULL,
	[ObjectName] [nvarchar](128) NULL,
	[RequestID] [int] NULL,
	[RowCounts] [bigint] NULL,
	[SessionLoginName] [nvarchar](128) NULL,
	[TransactionID] [bigint] NULL,
	[XactSequence] [bigint] NULL,
PRIMARY KEY CLUSTERED 
(
	[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


*/
  
-- Executando comando para contabilização de indicadores do GerenciaBD  
  
 exec (@COMANDO_INSERT_SERVIDOR_MONITORANDO)  
  
end  
  
-- Definindo comando para deleção de arquivos de trace já contabilizado  
  
 DECLARE @COMANDO_DELECAO VARCHAR (1000)  
  
 SET @COMANDO_DELECAO = 'del "'+ 'c:\scripts' + '\indicador_performance_dia.trc"'  
  
 print @COMANDO_DELECAO  
  
-- Executando comando para deleção de arquivo de trace já contabilizado  
  
 exec master..xp_cmdshell @COMANDO_DELECAO  
  
  
-- Gerando server trace otimizado do GerenciaBD  
  
/****************************************************/  
/* Created by: SQL Profiler                         */  
/* Date: 27/04/2005  16:16:01         */  
/****************************************************/  
  
  
-- Create a Queue  
declare @rc int  
declare @TraceID int  
declare @maxfilesize bigint  
declare @datetime datetime  
--declare @caminho varchar(50)  
  
  
set @datetime = (select substring (convert (varchar(11),getdate(),120),1,11) + '23:59:00.000')  
set @maxfilesize = 10000   
  
--print @dir  
declare @arquivo_trace nvarchar(128)  
set @arquivo_trace = 'c:\scripts' + '\indicador_performance_dia'  
print @arquivo_trace  
-- Please replace the text InsertFileNameHere, with an appropriate  
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension  
-- will be appended to the filename automatically. If you are writing from  
-- remote server to local drive, please use UNC path and make sure server has  
-- write access to your network share  
  
--set @caminho = 'D:\SQLADMIN\MSSQL\LOG\' + convert (varchar(50),getdate(),112)  
--select @caminho  
exec @rc = sp_trace_create @TraceID output, 0, @arquivo_trace, @maxfilesize, @datetime  
if (@rc != 0) goto error  
  
  
  
-- Client side File and Table cannot be scripted  
  
-- Writing to a table is not supported through the SP's  
  
-- Set the events  
declare @on bit  
set @on = 1  
exec sp_trace_setevent @TraceID, 10, 1, @on  
exec sp_trace_setevent @TraceID, 10, 3, @on  
exec sp_trace_setevent @TraceID, 10, 6, @on  
exec sp_trace_setevent @TraceID, 10, 8, @on  
exec sp_trace_setevent @TraceID, 10, 9, @on  
exec sp_trace_setevent @TraceID, 10, 10, @on  
exec sp_trace_setevent @TraceID, 10, 11, @on  
exec sp_trace_setevent @TraceID, 10, 12, @on  
exec sp_trace_setevent @TraceID, 10, 13, @on  
exec sp_trace_setevent @TraceID, 10, 14, @on  
exec sp_trace_setevent @TraceID, 10, 16, @on  
exec sp_trace_setevent @TraceID, 10, 17, @on  
exec sp_trace_setevent @TraceID, 10, 18, @on  
exec sp_trace_setevent @TraceID, 12, 1, @on  
exec sp_trace_setevent @TraceID, 12, 3, @on  
exec sp_trace_setevent @TraceID, 12, 6, @on  
exec sp_trace_setevent @TraceID, 12, 8, @on  
exec sp_trace_setevent @TraceID, 12, 9, @on  
exec sp_trace_setevent @TraceID, 12, 10, @on  
exec sp_trace_setevent @TraceID, 12, 11, @on  
exec sp_trace_setevent @TraceID, 12, 12, @on  
exec sp_trace_setevent @TraceID, 12, 13, @on  
exec sp_trace_setevent @TraceID, 12, 14, @on  
exec sp_trace_setevent @TraceID, 12, 16, @on  
exec sp_trace_setevent @TraceID, 12, 17, @on  
exec sp_trace_setevent @TraceID, 12, 18, @on  
  
  
-- Set the Filters  
declare @intfilter int  
declare @bigintfilter bigint  
  
-- Verifica qual a versão do SQL Server para filtrar o server trace  
  
IF 10 <= 8 

--if (select substring(Charcater_Value,1,1) from prodver)<= 8  
	begin 
		set @bigintfilter = 3000   
	end
else 
	begin 
		set @bigintfilter = 3000000  
	end
	
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter  
exec sp_trace_setfilter @TraceID, 8, 0, 7, @SERVIDOR_MONITORADO  
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'  
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'  
set @intfilter = 100  
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter  
  
  
  
-- Set the trace status to start  
exec sp_trace_setstatus @TraceID, 1  
  
-- display trace id for future references  
select TraceID=@TraceID  
goto finish  
  
error:   
select ErrorCode=@rc  
  
finish:   
  
   
--EXEC sp_trace_setstatus 1, 0   
--EXEC sp_trace_setstatus 1, 2  
  
--EXEC sp_trace_setstatus 2, 0   
--EXEC sp_trace_setstatus 2, 2  
  
  
--SELECT * FROM :: fn_trace_getinfo(default)  
--select * into Trace_Table from ::fn_trace_gettable('c:\ind_perf\sicla_indicador_perform  
  
  
-- Contabilizando informações de Bloqueios coletadas pelo job schedulado Alerta - Monitora Processos Bloqueados  
  






GO

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[spu_checkblocking]    Script Date: 12/18/2018 11:57:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


create procedure [dbo].[spu_checkblocking]
as
	
	declare @spid int,@blocked int,@waittime int,@dbccstmt varchar(100)
	declare @eventtype1 varchar(300),@eventtype2 varchar(300), @hostname varchar(20), @program_name varchar(30), @program_name2 varchar(30), @host_blocking varchar(20)
	declare cur_sp cursor for select spid,blocked,waittime,hostname,program_name from master.dbo.sysprocesses (nolock) where blocked > 1

	create table #dbcc_output (
	eventtype varchar(30),
	parameters varchar(30),
	eventinfo varchar(300)
	)
   	open cur_sp
        fetch next from cur_sp into @spid,@blocked,@waittime,@hostname,@program_name
	while (@@fetch_status = 0)
	begin
                select @program_name2 = program_name from master.dbo.sysprocesses (nolock) where spid=@blocked		
                select @host_blocking = hostname from master.dbo.sysprocesses (nolock) where spid=@blocked

		set @dbccstmt = 'dbcc inputbuffer ('+convert(char(3),@spid)+')'
		print @dbccstmt
		insert  into #dbcc_output  exec (@dbccstmt)
		select @eventtype1 = eventinfo from #dbcc_output
		truncate table #dbcc_output
		set @dbccstmt = 'dbcc inputbuffer ('+convert(char(3),@blocked)+')'
		insert  into #dbcc_output  exec (@dbccstmt)
		select @eventtype2 = eventinfo from #dbcc_output
		truncate table #dbcc_output

		if @spid <> @blocked 
			insert into blocktable values (@spid,@blocked,@eventtype1,@eventtype2,@waittime,@hostname,getdate(),@program_name, @program_name2,@host_blocking)
                
/*
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "E0404019" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "E0404009" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "M2216" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "console" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "m2953" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "m2954" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "GILBERTR" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "uf104730" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - UTILIZE A SP_QUEM"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "uf025055" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - UTILIZE A SP_QUEM"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "uf015930" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - UTILIZE A SP_QUEM"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "uf100160" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - UTILIZE A SP_QUEM"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "uf100445" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - UTILIZE A SP_QUEM"'
                if @waittime > 600000 exec master.dbo.xp_cmdshell 'net send "m2953" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 10 MINUTOS - VERIFIQUE NO PROCESS INFO"'
                if @waittime > 90000 exec master.dbo.xp_cmdshell 'net send "m2954" "ACIARIA - PROCESSO BLOQUEADO A MAIS DE 2 MINUTOS - VERIFIQUE NO PROCESS INFO"'
*/

		fetch next from cur_sp into @spid,@blocked,@waittime,@hostname,@program_name
	end
	close cur_sp
	deallocate cur_sp
	drop table #dbcc_output






GO




GO


USE [msdb]
GO

/****** Object:  Job [GerenciaBD]    Script Date: 26/08/2016 14:42:11 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 26/08/2016 14:42:12 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'GerenciaBD', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [spu_GerenciaBD]    Script Date: 26/08/2016 14:42:12 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'spu_GerenciaBD', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=10, 
		@retry_interval=3, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'Exec spu_GerenciaBD', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'A cada 3 Minutos', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=3, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20100928, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'431ed25e-1597-45f5-a3c4-18a8896450f8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

/****** Object:  Job [Alerta - Monitora Processos Bloqueados]    Script Date: 26/08/2016 14:42:12 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [REPL-Checkup]    Script Date: 26/08/2016 14:42:12 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Checkup' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Checkup'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alerta - Monitora Processos Bloqueados', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Esse Job executa a stored procedure spu_checkblocking, que foi avaliada durante 30 dias. Como os resultados foram satisfatórios, foi implementada a funcionalidade de envio de mensagem para a Operação intervir em processos bloqueados a mais de 2 minutos.', 
		@category_name=N'REPL-Checkup', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [spu_checkblocking]    Script Date: 26/08/2016 14:42:12 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'spu_checkblocking', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=10, 
		@retry_interval=1, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET DEADLOCK_PRIORITY LOW 
exec spu_checkblocking', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'schedule 2', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20030429, 
		@active_end_date=99991231, 
		@active_start_time=30, 
		@active_end_time=235959, 
		@schedule_uid=N'edcbf2b4-61fa-4b8d-8cf1-97880d613bde'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO