| MsgBox Server | plan_handle | total_worker_time | dbid | objectid | number | encrypted | text |
| MYTESTSERVER | 601001422252051841294326000000000000 | 2725109 | NULL | NULL | NULL | False | SELECT audin.msgin,audin.icin,audin.del_not FROM audin,msgs1 WHERE audin.mstscd=5 AND audin.resetyn<=1 AND audin.mcvr=msgs1.mcvr AND ((msgs1.una > 0 AND ABS(DATEDIFF(ss, audin.tmbdd, getdate()))>msgs1.una) OR ((msgs1.una IS NULL OR msgs1.una=0) and ABS(DATEDIFF(ss, audin.tmbdd, getdate()))>10)) |
| MYTESTSERVER | 504051251128331841294264000000000000 | 2867393 | 4 | 562101043 | 1 | False | CREATE PROCEDURE sp_help_jobhistory @job_id UNIQUEIDENTIFIER = NULL, @job_name sysname = NULL, @step_id INT = NULL, @sql_message_id INT = NULL, @sql_severity INT = NULL, @start_run_date INT = NULL, -- YYYYMMDD @end_run_date INT = NULL, -- YYYYMMDD @start_run_time INT = NULL, -- HHMMSS @end_run_time INT = NULL, -- HHMMSS @minimum_run_duration INT = NULL, -- HHMMSS @run_status INT = NULL, -- SQLAGENT_EXEC_X code @minimum_retries INT = NULL, @oldest_first INT = 0, -- Or 1 @server sysname = NULL, @mode VARCHAR(7) = 'SUMMARY' -- Or 'FULL' or 'SEM'ASBEGIN DECLARE @retval INT DECLARE @order_by INT -- Must be INT since it can be -1 SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @server = LTRIM(RTRIM(@server)) SELECT @mode = LTRIM(RTRIM(@mode)) -- Turn [nullable] empty string parameters into NULLs IF (@server = N'') SELECT @server = NULL -- Check job id/name (if supplied) IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL)) BEGIN EXECUTE @retval = sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT IF (@retval <> 0) RETURN(1) -- Failure END -- Check @start_run_date IF (@start_run_date IS NOT NULL) BEGIN EXECUTE @retval = sp_verify_job_date @start_run_date, '@start_run_date' IF (@retval <> 0) RETURN(1) -- Failure END -- Check @end_run_date IF (@end_run_date IS NOT NULL) BEGIN EXECUTE @retval = sp_verify_job_date @end_run_date, '@end_run_date' IF (@retval <> 0) RETURN(1) -- Failure END -- Check @start_run_time EXECUTE @retval = sp_verify_job_time @start_run_time, '@start_run_time' IF (@retval <> 0) RETURN(1) -- Failure -- Check @end_run_time EXECUTE @retval = sp_verify_job_time @end_run_time, '@end_run_time' IF (@retval <> 0) RETURN(1) -- Failure -- Check @run_status IF ((@run_status < 0) OR (@run_status > 5)) BEGIN RAISERROR(14198, -1, -1, '@run_status', '0..5') RETURN(1) -- Failure END -- Check mode SELECT @mode = UPPER(@mode collate SQL_Latin1_General_CP1_CS_AS) IF (@mode NOT IN ('SUMMARY', 'FULL', 'SEM')) BEGIN RAISERROR(14266, -1, -1, '@mode', 'SUMMARY, FULL, SEM') RETURN(1) -- Failure END SELECT @order_by = -1 IF (@oldest_first = 1) SELECT @order_by = 1 DECLARE @distributed_job_history BIT SET @distributed_job_history = 0 IF (@job_id IS NOT NULL) AND ( EXISTS (SELECT * FROM msdb.dbo.sysjobs sj, msdb.dbo.sysjobservers sjs WHERE (sj.job_id = sjs.job_id) AND (sj.job_id = @job_id) AND (sjs.server_id <> 0))) SET @distributed_job_history = 1 -- Return history information filtered by the supplied parameters. -- NOTE: SQLDMO relies on the 'FULL' format; ** DO NOT CHANGE IT ** IF (@mode = 'FULL') BEGIN IF(@distributed_job_history = 1) BEGIN SELECT null as instance_id, sj.job_id, job_name = sj.name, null as step_id, null as step_name, null as sql_message_id, null as sql_severity, sjh.last_outcome_message as message, sjh.last_run_outcome as run_status, sjh.last_run_date as run_date, sjh.last_run_time as run_time, sjh.last_run_duration as run_duration, null as operator_emailed, null as operator_netsentname, null as operator_paged, null as retries_attempted, sts.server_name as server FROM msdb.dbo.sysjobservers sjh JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id) JOIN msdb.dbo.sysjobs_view sj ON(sj.job_id = sjh.job_id) WHERE (@job_id = sjh.job_id) AND ((@start_run_date IS NULL) OR (sjh.last_run_date >= @start_run_date)) AND ((@end_run_date IS NULL) OR (sjh.last_run_date <= @end_run_date)) AND ((@start_run_time IS NULL) OR (sjh.last_run_time >= @start_run_time)) AND ((@minimum_run_duration IS NULL) OR (sjh.last_run_duration >= @minimum_run_duration)) AND ((@run_status IS NULL) OR (@run_status = sjh.last_run_outcome)) AND ((@server IS NULL) OR (sts.server_name = @server)) END ELSE BEGIN SELECT sjh.instance_id, -- This is included just for ordering purposes sj.job_id, job_name = sj.name, sjh.step_id, sjh.step_name, sjh.sql_message_id, sjh.sql_severity, sjh.message, sjh.run_status, sjh.run_date, sjh.run_time, sjh.run_duration, operator_emailed = so1.name, operator_netsent = so2.name, operator_paged = so3.name, sjh.retries_attempted, sjh.server FROM msdb.dbo.sysjobhistory sjh LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id), msdb.dbo.sysjobs_view sj WHERE (sj.job_id = sjh.job_id) AND ((@job_id IS NULL) OR (@job_id = sjh.job_id)) AND ((@step_id IS NULL) OR (@step_id = sjh.step_id)) AND ((@sql_message_id IS NULL) OR (@sql_message_id = sjh.sql_message_id)) AND ((@sql_severity IS NULL) OR (@sql_severity = sjh.sql_severity)) AND ((@start_run_date IS NULL) OR (sjh.run_date >= @start_run_date)) AND ((@end_run_date IS NULL) OR (sjh.run_date <= @end_run_date)) AND ((@start_run_time IS NULL) OR (sjh.run_time >= @start_run_time)) AND ((@end_run_time IS NULL) OR (sjh.run_time <= @end_run_time)) AND ((@minimum_run_duration IS NULL) OR (sjh.run_duration >= @minimum_run_duration)) AND ((@run_status IS NULL) OR (@run_status = sjh.run_status)) AND ((@minimum_retries IS NULL) OR (sjh.retries_attempted >= @minimum_retries)) AND ((@server IS NULL) OR (sjh.server = @server)) ORDER BY (sjh.instance_id * @order_by) END END ELSE IF (@mode = 'SUMMARY') BEGIN -- Summary format: same WHERE clause just a different SELECT list IF(@distributed_job_history = 1) BEGIN SELECT sj.job_id, job_name = sj.name, sjh.last_run_outcome as run_status, sjh.last_run_date as run_date, sjh.last_run_time as run_time, sjh.last_run_duration as run_duration, null as operator_emailed, null as operator_netsentname, null as operator_paged, null as retries_attempted, sts.server_name as server FROM msdb.dbo.sysjobservers sjh JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id) JOIN msdb.dbo.sysjobs_view sj ON(sj.job_id = sjh.job_id) WHERE (@job_id = sjh.job_id) AND ((@start_run_date IS NULL) OR (sjh.last_run_date >= @start_run_date)) AND ((@end_run_date IS NULL) OR (sjh.last_run_date <= @end_run_date)) AND ((@start_run_time IS NULL) OR (sjh.last_run_time >= @start_run_time)) AND ((@minimum_run_duration IS NULL) OR (sjh.last_run_duration >= @minimum_run_duration)) AND ((@run_status IS NULL) OR (@run_status = sjh.last_run_outcome)) AND ((@server IS NULL) OR (sts.server_name = @server)) END ELSE BEGIN SELECT sj.job_id, job_name = sj.name, sjh.run_status, sjh.run_date, sjh.run_time, sjh.run_duration, operator_emailed = substring(so1.name, 1, 20), operator_netsent = substring(so2.name, 1, 20), operator_paged = substring(so3.name, 1, 20), sjh.retries_attempted, sjh.server FROM msdb.dbo.sysjobhistory sjh LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id), msdb.dbo.sysjobs_view sj WHERE (sj.job_id = sjh.job_id) AND ((@job_id IS NULL) OR (@job_id = sjh.job_id)) AND ((@step_id IS NULL) OR (@step_id = sjh.step_id)) AND ((@sql_message_id IS NULL) OR (@sql_message_id = sjh.sql_message_id)) AND ((@sql_severity IS NULL) OR (@sql_severity = sjh.sql_severity)) AND ((@start_run_date IS NULL) OR (sjh.run_date >= @start_run_date)) AND ((@end_run_date IS NULL) OR (sjh.run_date <= @end_run_date)) AND ((@start_run_time IS NULL) OR (sjh.run_time >= @start_run_time)) AND ((@end_run_time IS NULL) OR (sjh.run_time <= @end_run_time)) AND ((@minimum_run_duration IS NULL) OR (sjh.run_duration >= @minimum_run_duration)) AND ((@run_status IS NULL) OR (@run_status = sjh.run_status)) AND ((@minimum_retries IS NULL) OR (sjh.retries_attempted >= @minimum_retries)) AND ((@server IS NULL) OR (sjh.server = @server)) ORDER BY (sjh.instance_id * @order_by) END END ELSE IF (@mode = 'SEM') BEGIN -- SQL Enterprise Manager format IF(@distributed_job_history = 1) BEGIN SELECT sj.job_id, null as step_name, sjh.last_outcome_message as message, sjh.last_run_outcome as run_status, sjh.last_run_date as run_date, sjh.last_run_time as run_time, sjh.last_run_duration as run_duration, null as operator_emailed, null as operator_netsentname, null as operator_paged FROM msdb.dbo.sysjobservers sjh JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id) JOIN msdb.dbo.sysjobs_view sj ON(sj.job_id = sjh.job_id) WHERE (@job_id = sjh.job_id) END ELSE BEGIN SELECT sjh.step_id, sjh.step_name, sjh.message, sjh.run_status, sjh.run_date, sjh.run_time, sjh.run_duration, operator_emailed = so1.name, operator_netsent = so2.name, operator_paged = so3.name FROM msdb.dbo.sysjobhistory sjh LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id), msdb.dbo.sysjobs_view sj WHERE (sj.job_id = sjh.job_id) AND (@job_id = sjh.job_id) ORDER BY (sjh.instance_id * @order_by) END END RETURN(0) -- SuccessEND |
| MYTESTSERVER | 5040224243135531841292762000000000000 | 2913340 | 4 | 898102240 | 1 | False | CREATE PROCEDURE sp_help_jobstep @job_id UNIQUEIDENTIFIER = NULL, -- Must provide either this or job_name @job_name sysname = NULL, -- Must provide either this or job_id @step_id INT = NULL, @step_name sysname = NULL, @suffix BIT = 0 -- A flag to control how the result set is formattedASBEGIN DECLARE @retval INT DECLARE @max_step_id INT DECLARE @valid_range VARCHAR(50) SET NOCOUNT ON EXECUTE @retval = sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT, 'NO_TEST' IF (@retval <> 0) RETURN(1) -- Failure -- The suffix flag must be either 0 (ie. no suffix) or 1 (ie. add suffix). 0 is the default. IF (@suffix <> 0) SELECT @suffix = 1 -- Check step id (if supplied) IF (@step_id IS NOT NULL) BEGIN -- Get current maximum step id SELECT @max_step_id = ISNULL(MAX(step_id), 0) FROM msdb.dbo.sysjobsteps WHERE job_id = @job_id IF @max_step_id = 0 BEGIN RAISERROR(14528, -1, -1, @job_name) RETURN(1) -- Failure END ELSE IF (@step_id < 1) OR (@step_id > @max_step_id) BEGIN SELECT @valid_range = '1..' + CONVERT(VARCHAR, @max_step_id) RAISERROR(14266, -1, -1, '@step_id', @valid_range) RETURN(1) -- Failure END END -- Check step name (if supplied) -- NOTE: A supplied step id overrides a supplied step name IF ((@step_id IS NULL) AND (@step_name IS NOT NULL)) BEGIN SELECT @step_id = step_id FROM msdb.dbo.sysjobsteps WHERE (step_name = @step_name) AND (job_id = @job_id) IF (@step_id IS NULL) BEGIN RAISERROR(14262, -1, -1, '@step_name', @step_name) RETURN(1) -- Failure END END -- Return the job steps for this job (or just return the specific step) IF (@suffix = 0) BEGIN SELECT step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND ((@step_id IS NULL) OR (step_id = @step_id)) END ELSE BEGIN SELECT step_id, step_name, subsystem, command, 'flags' = CONVERT(NVARCHAR, flags) + N' (' + ISNULL(CASE WHEN (flags = 0) THEN FORMATMESSAGE(14561) END, '') + ISNULL(CASE WHEN (flags & 1) = 1 THEN FORMATMESSAGE(14558) + ISNULL(CASE WHEN (flags > 1) THEN N', ' END, '') END, '') + ISNULL(CASE WHEN (flags & 2) = 2 THEN FORMATMESSAGE(14559) + ISNULL(CASE WHEN (flags > 3) THEN N', ' END, '') END, '') + ISNULL(CASE WHEN (flags & 4) = 4 THEN FORMATMESSAGE(14560) END, '') + N')', cmdexec_success_code, 'on_success_action' = CASE on_success_action WHEN 1 THEN CONVERT(NVARCHAR, on_success_action) + N' ' + FORMATMESSAGE(14562) WHEN 2 THEN CONVERT(NVARCHAR, on_success_action) + N' ' + FORMATMESSAGE(14563) WHEN 3 THEN CONVERT(NVARCHAR, on_success_action) + N' ' + FORMATMESSAGE(14564) WHEN 4 THEN CONVERT(NVARCHAR, on_success_action) + N' ' + FORMATMESSAGE(14565) ELSE CONVERT(NVARCHAR, on_success_action) + N' ' + FORMATMESSAGE(14205) END, on_success_step_id, 'on_fail_action' = CASE on_fail_action WHEN 1 THEN CONVERT(NVARCHAR, on_fail_action) + N' ' + FORMATMESSAGE(14562) WHEN 2 THEN CONVERT(NVARCHAR, on_fail_action) + N' ' + FORMATMESSAGE(14563) WHEN 3 THEN CONVERT(NVARCHAR, on_fail_action) + N' ' + FORMATMESSAGE(14564) WHEN 4 THEN CONVERT(NVARCHAR, on_fail_action) + N' ' + FORMATMESSAGE(14565) ELSE CONVERT(NVARCHAR, on_fail_action) + N' ' + FORMATMESSAGE(14205) END, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, 'os_run_priority' = CASE os_run_priority WHEN -15 THEN CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14566) WHEN -1 THEN CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14567) WHEN 0 THEN CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14561) WHEN 1 THEN CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14568) WHEN 15 THEN CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14569) ELSE CONVERT(NVARCHAR, os_run_priority) + N' ' + FORMATMESSAGE(14205) END, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND ((@step_id IS NULL) OR (step_id = @step_id)) END RETURN(@@error) -- 0 means successEND |
| MYTESTSERVER | 504019986248318412922943000000000000 | 3005744 | 4 | 1394104007 | 1 | False | CREATE PROCEDURE sp_sqlagent_log_jobhistory @job_id UNIQUEIDENTIFIER, @step_id INT, @sql_message_id INT = 0, @sql_severity INT = 0, @message NVARCHAR(1024) = NULL, @run_status INT, -- SQLAGENT_EXEC_X code @run_date INT, @run_time INT, @run_duration INT, @operator_id_emailed INT = 0, @operator_id_netsent INT = 0, @operator_id_paged INT = 0, @retries_attempted INT, @server sysname = NULL, @session_id INT = 0ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) DECLARE @step_id_as_char VARCHAR(10) DECLARE @operator_id_as_char VARCHAR(10) DECLARE @step_name sysname DECLARE @error_severity INT SET NOCOUNT ON IF (@server IS NULL) OR (UPPER(@server collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)') SELECT @server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) -- Check authority (only SQLServerAgent can add a history entry for a job) EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%' IF (@retval <> 0) RETURN(@retval) -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching -- the operation (if it fails) since if the operation will never run successfully we -- don't want it to hang around in the operation cache. SELECT @error_severity = 0 -- Check job_id IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id))) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char) RETURN(1) -- Failure END -- Check step id IF (@step_id <> 0) -- 0 means 'for the whole job' BEGIN SELECT @step_name = step_name FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND (step_id = @step_id) IF (@step_name IS NULL) BEGIN SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id) RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char) RETURN(1) -- Failure END END ELSE SELECT @step_name = FORMATMESSAGE(14570) -- Check run_status IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code BEGIN RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5') RETURN(1) -- Failure END -- Check run_date EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check run_time EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check operator_id_emailed IF (@operator_id_emailed <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_emailed))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed) RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_netsent IF (@operator_id_netsent <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_netsent))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent) RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_paged IF (@operator_id_paged <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_paged))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged) RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char) RETURN(1) -- Failure END END -- Insert the history row INSERT INTO msdb.dbo.sysjobhistory (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server) VALUES (@job_id, @step_id, @step_name, @sql_message_id, @sql_severity, @message, @run_status, @run_date, @run_time, @run_duration, @operator_id_emailed, @operator_id_netsent, @operator_id_paged, @retries_attempted, @server) -- Update sysjobactivity table IF (@step_id = 0) --only update for job, not for each step BEGIN UPDATE msdb.dbo.sysjobactivity SET stop_execution_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), job_history_id = SCOPE_IDENTITY() WHERE session_id = @session_id AND job_id = @job_id END -- Special handling of replication jobs DECLARE @job_name sysname DECLARE @category_id int SELECT @job_name = name, @category_id = category_id from msdb.dbo.sysjobs WHERE job_id = @job_id -- If replicatio agents (snapshot, logreader, distribution, merge, and queuereader -- and the step has been canceled and if we are at the distributor. IF @category_id in (10,13,14,15,19) and @run_status = 3 and object_id('MSdistributiondbs') is not null BEGIN -- Get the database DECLARE @database sysname SELECT @database = database_name from sysjobsteps where job_id = @job_id and lower(subsystem) in (N'distribution', N'logreader','snapshot',N'merge', N'queuereader') -- If the database is a distribution database IF EXISTS (select * from MSdistributiondbs where name = @database) BEGIN DECLARE @proc nvarchar(500) SELECT @proc = quotename(@database) + N'.dbo.sp_MSlog_agent_cancel' EXEC @proc @job_id = @job_id, @category_id = @category_id, @message = @message END END -- Delete any history rows that are over the registry-defined limits EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id RETURN(@@error) -- 0 means successEND |
| MYTESTSERVER | 6040238247207551846518520000000000000 | 3008392 | NULL | NULL | NULL | False | DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT 1SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory WHERE (job_id = 'C04A8881-A30D-432C-86DA-4BBC574FD807') ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (job_id = 'C04A8881-A30D-432C-86DA-4BBC574FD807') AND (instance_id <= @new_oldest_id) |
| MYTESTSERVER | 506018658204751843311628000000000000 | 3012759 | 6 | 1271675578 | 1 | False | CREATE PROCEDURE [dbo].[MsgBoxPerfCounters_GetMsgBoxesAndServers] AS
DECLARE @retVal smallint
EXEC @retVal = sp_getapplock @Resource = 'MsgBoxPerfCounters_adm_MessageBoxTable', @LockMode = 'Shared', @LockOwner = 'Session'
IF (@retVal = 0 ) -- Lock granted
BEGIN
SELECT DBServerName, DBName FROM adm_MessageBox
EXEC sp_releaseapplock @Resource = 'MsgBoxPerfCounters_adm_MessageBoxTable', @LockOwner = 'Session'
END
-- Else there was a problem getting lock and nothing is returned. Means that things will be considered to be 'deleted' from counters updating point of view. |
| MYTESTSERVER | 504019986248318412922943000000000000 | 3031917 | 4 | 1394104007 | 1 | False | CREATE PROCEDURE sp_sqlagent_log_jobhistory @job_id UNIQUEIDENTIFIER, @step_id INT, @sql_message_id INT = 0, @sql_severity INT = 0, @message NVARCHAR(1024) = NULL, @run_status INT, -- SQLAGENT_EXEC_X code @run_date INT, @run_time INT, @run_duration INT, @operator_id_emailed INT = 0, @operator_id_netsent INT = 0, @operator_id_paged INT = 0, @retries_attempted INT, @server sysname = NULL, @session_id INT = 0ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) DECLARE @step_id_as_char VARCHAR(10) DECLARE @operator_id_as_char VARCHAR(10) DECLARE @step_name sysname DECLARE @error_severity INT SET NOCOUNT ON IF (@server IS NULL) OR (UPPER(@server collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)') SELECT @server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) -- Check authority (only SQLServerAgent can add a history entry for a job) EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%' IF (@retval <> 0) RETURN(@retval) -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching -- the operation (if it fails) since if the operation will never run successfully we -- don't want it to hang around in the operation cache. SELECT @error_severity = 0 -- Check job_id IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id))) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char) RETURN(1) -- Failure END -- Check step id IF (@step_id <> 0) -- 0 means 'for the whole job' BEGIN SELECT @step_name = step_name FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND (step_id = @step_id) IF (@step_name IS NULL) BEGIN SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id) RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char) RETURN(1) -- Failure END END ELSE SELECT @step_name = FORMATMESSAGE(14570) -- Check run_status IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code BEGIN RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5') RETURN(1) -- Failure END -- Check run_date EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check run_time EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check operator_id_emailed IF (@operator_id_emailed <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_emailed))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed) RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_netsent IF (@operator_id_netsent <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_netsent))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent) RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_paged IF (@operator_id_paged <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_paged))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged) RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char) RETURN(1) -- Failure END END -- Insert the history row INSERT INTO msdb.dbo.sysjobhistory (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server) VALUES (@job_id, @step_id, @step_name, @sql_message_id, @sql_severity, @message, @run_status, @run_date, @run_time, @run_duration, @operator_id_emailed, @operator_id_netsent, @operator_id_paged, @retries_attempted, @server) -- Update sysjobactivity table IF (@step_id = 0) --only update for job, not for each step BEGIN UPDATE msdb.dbo.sysjobactivity SET stop_execution_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), job_history_id = SCOPE_IDENTITY() WHERE session_id = @session_id AND job_id = @job_id END -- Special handling of replication jobs DECLARE @job_name sysname DECLARE @category_id int SELECT @job_name = name, @category_id = category_id from msdb.dbo.sysjobs WHERE job_id = @job_id -- If replicatio agents (snapshot, logreader, distribution, merge, and queuereader -- and the step has been canceled and if we are at the distributor. IF @category_id in (10,13,14,15,19) and @run_status = 3 and object_id('MSdistributiondbs') is not null BEGIN -- Get the database DECLARE @database sysname SELECT @database = database_name from sysjobsteps where job_id = @job_id and lower(subsystem) in (N'distribution', N'logreader','snapshot',N'merge', N'queuereader') -- If the database is a distribution database IF EXISTS (select * from MSdistributiondbs where name = @database) BEGIN DECLARE @proc nvarchar(500) SELECT @proc = quotename(@database) + N'.dbo.sp_MSlog_agent_cancel' EXEC @proc @job_id = @job_id, @category_id = @category_id, @message = @message END END -- Delete any history rows that are over the registry-defined limits EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id RETURN(@@error) -- 0 means successEND |
| MYTESTSERVER | 60401901563214184113727000000000000 | 3048962 | NULL | NULL | NULL | False | DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT 1SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory WHERE (job_id = '77FFBDBE-6D84-43BE-8D78-F9E5C75D74FB') ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (job_id = '77FFBDBE-6D84-43BE-8D78-F9E5C75D74FB') AND (instance_id <= @new_oldest_id) |
| MYTESTSERVER | 60100125177254101841295336000000000000 | 3170665 | NULL | NULL | NULL | False | SELECT audout.msgout,audout.icout,audout.seqno,audout.del_not FROM audout,msgs1 WHERE audout.mstscd=5 AND audout.resetyn<=1 AND audout.mcvr=msgs1.mcvr AND ((msgs1.una > 0 AND ABS(DATEDIFF(ss, audout.tmcs, getdate()))>msgs1.una) OR ((msgs1.una IS NULL OR msgs1.una=0) and ABS(DATEDIFF(ss, audout.tmcs, getdate()))>10)) |
| MYTESTSERVER | 502551273914023321841615642000000000000 | 3419582 | 32767 | 48860199 | 1 | False | create procedure sys.sp_spaceused --- 2003/05/19 14:00@objname nvarchar(776) = null, -- The object we want size on.@updateusage varchar(5) = false -- Param. for specifying that -- usage info. should be updated.asdeclare @id int -- The object id that takes up space ,@type character(2) -- The object type. ,@pages bigint -- Working variable for size calc. ,@dbname sysname ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,@usedpages bigint ,@rowCount bigint/*** Check to see if user wants usages updated.*/if @updateusage is not null begin select @updateusage=lower(@updateusage) if @updateusage not in ('true','false') begin raiserror(15143,-1,-1,@updateusage) return(1) end end/*** Check to see that the objname is local.*/if @objname IS NOT NULLbegin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end if @dbname is null select @dbname = db_name() /* ** Try to find the object. */ SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) -- Translate @id to internal-table for queue IF @type = 'SQ' SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue /* ** Does the object exist? */ if @id is null begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- Is it a table, view or queue? IF @type NOT IN ('U ','S ','V ','SQ','IT') begin raiserror(15234,-1,-1) return (1) endend/*** Update usages if user specified to do so.*/if @updateusage = 'true' begin if @objname is null dbcc updateusage(0) with no_infomsgs else dbcc updateusage(0,@objname) with no_infomsgs print ' ' endset nocount on/*** If @id is null, then we want summary data.*/if @id is nullbegin select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size" When it.internal_type IN (202,204) Then 0 When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id /* unallocated space could not be negative */ select database_name = db_name(), database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'), 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) + ' MB') /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) ** data: sum(data_pages) + sum(text_used) ** index: sum(used) where indid in (0, 1, 255) - data ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ select reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')end/*** We want a particular object.*/elsebegin /* ** Now calculate the summary data. ** Regarding rowcount: We want to add row count from all partitions, but ** note that a row in sys.partitions may join with multiple rows in ** sys.allocation_units as it may have data, SLOB and LOb allocation units. */ select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ), @rowCount = sum( CASE When (p.index_id < 2) and (a.type = 1) Then p.rows Else 0 END ) from sys.partitions p, sys.allocation_units a where p.partition_id = a.container_id and p.object_id = @id /* ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table */ if (select count(*) from sys.internal_tables where parent_id = @id and internal_type IN (202,204)) > 0 begin /* ** Now calculate the summary data. Row counts in these internal tables don't ** contribute towards row count of original table. */ select @reservedpages = @reservedpages + sum(a.total_pages), @usedpages = @usedpages + sum(a.used_pages) from sys.partitions p, sys.allocation_units a, sys.internal_tables it where p.partition_id = a.container_id and p.object_id = it.object_id and it.internal_type IN (202,204) and it.parent_id = @id; end select name = object_name(object_id(@objname)), rows = convert(char(11), @rowCount), reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')endreturn (0) -- sp_spaceused |
| MYTESTSERVER | 5060129222167418416113552000000000000 | 3486617 | 6 | 1255675521 | 1 | False | CREATE PROCEDURE [dbo].[MsgBoxPerfCounters_GetMACacheRefreshInterval] AS
DECLARE @retVal smallint
EXEC @retVal = sp_getapplock @Resource = 'MsgBoxPerfCounters_adm_GroupTable', @LockMode = 'Shared', @LockOwner = 'Session'
IF (@retVal = 0 ) -- Lock granted
BEGIN
SELECT ConfigurationCacheRefreshInterval FROM adm_Group
EXEC sp_releaseapplock @Resource = 'MsgBoxPerfCounters_adm_GroupTable', @LockOwner = 'Session'
END
ELSE
-- Else there was a problem getting lock - return the default value of 60 in this case
BEGIN
SELECT 60
END |
| MYTESTSERVER | 50255127391402332184658559000000000000 | 3890035 | 32767 | 48860199 | 1 | False | create procedure sys.sp_spaceused --- 2003/05/19 14:00@objname nvarchar(776) = null, -- The object we want size on.@updateusage varchar(5) = false -- Param. for specifying that -- usage info. should be updated.asdeclare @id int -- The object id that takes up space ,@type character(2) -- The object type. ,@pages bigint -- Working variable for size calc. ,@dbname sysname ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,@usedpages bigint ,@rowCount bigint/*** Check to see if user wants usages updated.*/if @updateusage is not null begin select @updateusage=lower(@updateusage) if @updateusage not in ('true','false') begin raiserror(15143,-1,-1,@updateusage) return(1) end end/*** Check to see that the objname is local.*/if @objname IS NOT NULLbegin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end if @dbname is null select @dbname = db_name() /* ** Try to find the object. */ SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) -- Translate @id to internal-table for queue IF @type = 'SQ' SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue /* ** Does the object exist? */ if @id is null begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- Is it a table, view or queue? IF @type NOT IN ('U ','S ','V ','SQ','IT') begin raiserror(15234,-1,-1) return (1) endend/*** Update usages if user specified to do so.*/if @updateusage = 'true' begin if @objname is null dbcc updateusage(0) with no_infomsgs else dbcc updateusage(0,@objname) with no_infomsgs print ' ' endset nocount on/*** If @id is null, then we want summary data.*/if @id is nullbegin select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size" When it.internal_type IN (202,204) Then 0 When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id /* unallocated space could not be negative */ select database_name = db_name(), database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'), 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) + ' MB') /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) ** data: sum(data_pages) + sum(text_used) ** index: sum(used) where indid in (0, 1, 255) - data ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ select reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')end/*** We want a particular object.*/elsebegin /* ** Now calculate the summary data. ** Regarding rowcount: We want to add row count from all partitions, but ** note that a row in sys.partitions may join with multiple rows in ** sys.allocation_units as it may have data, SLOB and LOb allocation units. */ select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ), @rowCount = sum( CASE When (p.index_id < 2) and (a.type = 1) Then p.rows Else 0 END ) from sys.partitions p, sys.allocation_units a where p.partition_id = a.container_id and p.object_id = @id /* ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table */ if (select count(*) from sys.internal_tables where parent_id = @id and internal_type IN (202,204)) > 0 begin /* ** Now calculate the summary data. Row counts in these internal tables don't ** contribute towards row count of original table. */ select @reservedpages = @reservedpages + sum(a.total_pages), @usedpages = @usedpages + sum(a.used_pages) from sys.partitions p, sys.allocation_units a, sys.internal_tables it where p.partition_id = a.container_id and p.object_id = it.object_id and it.internal_type IN (202,204) and it.parent_id = @id; end select name = object_name(object_id(@objname)), rows = convert(char(11), @rowCount), reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')endreturn (0) -- sp_spaceused |
| MYTESTSERVER | 504010473201849713028000000000000 | 4049378 | 4 | 2099048 | 1 | False | CREATE PROCEDURE sp_verify_jobproc_caller @job_id UNIQUEIDENTIFIER, @program_name sysnameASBEGIN SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @program_name = LTRIM(RTRIM(@program_name)) IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) AND (master_server = 1) )) -- master_server = 1 filters on MSX jobs in this TSX server AND (PROGRAM_NAME() NOT LIKE @program_name) BEGIN RAISERROR(14274, -1, -1) RETURN(1) -- Failure END RETURN(0)END |
| MYTESTSERVER | 6040187214923918416114743000000000000 | 4142477 | NULL | NULL | NULL | False | (@1 int,@2 int,@3 int,@4 int,@5 int,@6 varbinary(8000),@7 tinyint)UPDATE [msdb].[dbo].[sysjobsteps] set [last_run_outcome] = @1,[last_run_duration] = @2,[last_run_retries] = @3,[last_run_date] = @4,[last_run_time] = @5 WHERE [job_id]=@6 AND [step_id]=@7 |
| MYTESTSERVER | 60100158229231618419317816000000000000 | 5353689 | NULL | NULL | NULL | False | FETCH API_CURSOR00000000000000C2 |
| MYTESTSERVER | 601006715020934184975313000000000000 | 5434796 | NULL | NULL | NULL | False | FETCH API_CURSOR00000000000000C1 |
| MYTESTSERVER | 604021411360018412912563000000000000 | 5545065 | NULL | NULL | NULL | False | (@1 int,@2 int,@3 int,@4 nvarchar(4000),@5 int,@6 varbinary(8000),@7 tinyint)UPDATE [msdb].[dbo].[sysjobservers] set [last_run_date] = @1,[last_run_time] = @2,[last_run_outcome] = @3,[last_outcome_message] = @4,[last_run_duration] = @5 WHERE [job_id]=@6 AND [server_id]=@7 |
| MYTESTSERVER | 60100232701395318419316954000000000000 | 5682554 | NULL | NULL | NULL | False | FETCH API_CURSOR00000000000000C0 |
| MYTESTSERVER | 50402461905512618412912820000000000000 | 6205886 | 4 | 2117582582 | 1 | False | CREATE PROCEDURE sp_verify_job_identifiers @name_of_name_parameter VARCHAR(60), -- Eg. '@job_name' @name_of_id_parameter VARCHAR(60), -- Eg. '@job_id' @job_name sysname OUTPUT, -- Eg. 'My Job' @job_id UNIQUEIDENTIFIER OUTPUT, @sqlagent_starting_test VARCHAR(7) = 'TEST', -- By default we DO want to test if SQLServerAgent is running (caller should specify 'NO_TEST' if not desired) @owner_sid VARBINARY(85) = NULL OUTPUT ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @name_of_name_parameter = LTRIM(RTRIM(@name_of_name_parameter)) SELECT @name_of_id_parameter = LTRIM(RTRIM(@name_of_id_parameter)) SELECT @job_name = LTRIM(RTRIM(@job_name)) IF (@job_name = N'') SELECT @job_name = NULL IF ((@job_name IS NULL) AND (@job_id IS NULL)) OR ((@job_name IS NOT NULL) AND (@job_id IS NOT NULL)) BEGIN RAISERROR(14294, -1, -1, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- Check job id IF (@job_id IS NOT NULL) BEGIN SELECT @job_name = name, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) -- the view would take care of all the permissions issues. IF (@job_name IS NULL) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- Failure END END ELSE -- Check job name IF (@job_name IS NOT NULL) BEGIN -- Check if the job name is ambiguous IF ((SELECT COUNT(*) FROM msdb.dbo.sysjobs_view WHERE (name = @job_name)) > 1) BEGIN RAISERROR(14293, -1, -1, @job_name, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- The name is not ambiguous, so get the corresponding job_id (if the job exists) SELECT @job_id = job_id, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (name = @job_name) -- the view would take care of all the permissions issues. IF (@job_id IS NULL) BEGIN RAISERROR(14262, -1, -1, '@job_name', @job_name) RETURN(1) -- Failure END END IF (@sqlagent_starting_test = 'TEST') BEGIN -- Finally, check if SQLServerAgent is in the process of starting and if so prevent the -- calling SP from running EXECUTE @retval = msdb.dbo.sp_is_sqlagent_starting IF (@retval <> 0) RETURN(1) -- Failure END RETURN(0) -- SuccessEND |
| MYTESTSERVER | 504019986248318412922943000000000000 | 6245771 | 4 | 1394104007 | 1 | False | CREATE PROCEDURE sp_sqlagent_log_jobhistory @job_id UNIQUEIDENTIFIER, @step_id INT, @sql_message_id INT = 0, @sql_severity INT = 0, @message NVARCHAR(1024) = NULL, @run_status INT, -- SQLAGENT_EXEC_X code @run_date INT, @run_time INT, @run_duration INT, @operator_id_emailed INT = 0, @operator_id_netsent INT = 0, @operator_id_paged INT = 0, @retries_attempted INT, @server sysname = NULL, @session_id INT = 0ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) DECLARE @step_id_as_char VARCHAR(10) DECLARE @operator_id_as_char VARCHAR(10) DECLARE @step_name sysname DECLARE @error_severity INT SET NOCOUNT ON IF (@server IS NULL) OR (UPPER(@server collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)') SELECT @server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) -- Check authority (only SQLServerAgent can add a history entry for a job) EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%' IF (@retval <> 0) RETURN(@retval) -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching -- the operation (if it fails) since if the operation will never run successfully we -- don't want it to hang around in the operation cache. SELECT @error_severity = 0 -- Check job_id IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id))) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char) RETURN(1) -- Failure END -- Check step id IF (@step_id <> 0) -- 0 means 'for the whole job' BEGIN SELECT @step_name = step_name FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND (step_id = @step_id) IF (@step_name IS NULL) BEGIN SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id) RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char) RETURN(1) -- Failure END END ELSE SELECT @step_name = FORMATMESSAGE(14570) -- Check run_status IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code BEGIN RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5') RETURN(1) -- Failure END -- Check run_date EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check run_time EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check operator_id_emailed IF (@operator_id_emailed <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_emailed))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed) RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_netsent IF (@operator_id_netsent <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_netsent))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent) RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_paged IF (@operator_id_paged <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_paged))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged) RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char) RETURN(1) -- Failure END END -- Insert the history row INSERT INTO msdb.dbo.sysjobhistory (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server) VALUES (@job_id, @step_id, @step_name, @sql_message_id, @sql_severity, @message, @run_status, @run_date, @run_time, @run_duration, @operator_id_emailed, @operator_id_netsent, @operator_id_paged, @retries_attempted, @server) -- Update sysjobactivity table IF (@step_id = 0) --only update for job, not for each step BEGIN UPDATE msdb.dbo.sysjobactivity SET stop_execution_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), job_history_id = SCOPE_IDENTITY() WHERE session_id = @session_id AND job_id = @job_id END -- Special handling of replication jobs DECLARE @job_name sysname DECLARE @category_id int SELECT @job_name = name, @category_id = category_id from msdb.dbo.sysjobs WHERE job_id = @job_id -- If replicatio agents (snapshot, logreader, distribution, merge, and queuereader -- and the step has been canceled and if we are at the distributor. IF @category_id in (10,13,14,15,19) and @run_status = 3 and object_id('MSdistributiondbs') is not null BEGIN -- Get the database DECLARE @database sysname SELECT @database = database_name from sysjobsteps where job_id = @job_id and lower(subsystem) in (N'distribution', N'logreader','snapshot',N'merge', N'queuereader') -- If the database is a distribution database IF EXISTS (select * from MSdistributiondbs where name = @database) BEGIN DECLARE @proc nvarchar(500) SELECT @proc = quotename(@database) + N'.dbo.sp_MSlog_agent_cancel' EXEC @proc @job_id = @job_id, @category_id = @category_id, @message = @message END END -- Delete any history rows that are over the registry-defined limits EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id RETURN(@@error) -- 0 means successEND |
| MYTESTSERVER | 50401425036821841939646000000000000 | 6428806 | 4 | 1378103950 | 1 | False | CREATE PROCEDURE sp_jobhistory_row_limiter @job_id UNIQUEIDENTIFIERASBEGIN DECLARE @max_total_rows INT -- This value comes from the registry (MaxJobHistoryTableRows) DECLARE @max_rows_per_job INT -- This value comes from the registry (MaxJobHistoryRows) DECLARE @rows_to_delete INT DECLARE @rows_to_delete_as_char VARCHAR(10) DECLARE @current_rows INT DECLARE @current_rows_per_job INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) -- Get max-job-history-rows from the registry EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', @max_total_rows OUTPUT, N'no_output' -- Check if we are limiting sysjobhistory rows IF (ISNULL(@max_total_rows, -1) = -1) RETURN(0) -- Check that max_total_rows is more than 1 IF (ISNULL(@max_total_rows, 0) < 2) BEGIN -- It isn't, so set the default to 1000 rows SELECT @max_total_rows = 1000 EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', N'REG_DWORD', @max_total_rows END -- Get the per-job maximum number of rows to keep SELECT @max_rows_per_job = 0 EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', @max_rows_per_job OUTPUT, N'no_output' -- Check that max_rows_per_job is <= max_total_rows IF ((@max_rows_per_job > @max_total_rows) OR (@max_rows_per_job < 1)) BEGIN -- It isn't, so default the rows_per_job to max_total_rows SELECT @max_rows_per_job = @max_total_rows EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', N'REG_DWORD', @max_rows_per_job END BEGIN TRANSACTION SELECT @current_rows_per_job = COUNT(*) FROM msdb.dbo.sysjobhistory with (TABLOCKX) WHERE (job_id = @job_id) -- Delete the oldest history row(s) for the job being inserted if the new row has -- pushed us over the per-job row limit (MaxJobHistoryRows) SELECT @rows_to_delete = @current_rows_per_job - @max_rows_per_job SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete) IF (@rows_to_delete > 0) BEGIN EXECUTE ('DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT ' + @rows_to_delete_as_char + 'SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory WHERE (job_id = ''' + @job_id_as_char + ''') ' + 'ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (job_id = ''' + @job_id_as_char + ''')' + ' AND (instance_id <= @new_oldest_id)') END -- Delete the oldest history row(s) if inserting the new row has pushed us over the -- global MaxJobHistoryTableRows limit. SELECT @current_rows = COUNT(*) FROM msdb.dbo.sysjobhistory SELECT @rows_to_delete = @current_rows - @max_total_rows SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete) IF (@rows_to_delete > 0) BEGIN EXECUTE ('DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT ' + @rows_to_delete_as_char + 'SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (instance_id <= @new_oldest_id)') END IF (@@trancount > 0) COMMIT TRANSACTION RETURN(0) -- SuccessEND |
| MYTESTSERVER | 5080710133161849716944000000000000 | 6714242 | 8 | 270624007 | 1 | False | CREATE PROCEDURE [dbo].[MsgBoxPerfCounters_GetTrackingDataSize] AS
DECLARE @retVal smallint
EXEC @retVal = sp_getapplock @Resource = 'MsgBoxPerfCounters_TrackingDataTable', @LockMode = 'Shared', @LockOwner = 'Session'
IF (@retVal = 0 ) -- Lock granted
BEGIN
SELECT count (*) FROM TrackingData WITH (NOLOCK)
EXEC sp_releaseapplock @Resource = 'MsgBoxPerfCounters_TrackingDataTable', @LockOwner = 'Session'
END
ELSE -- Problem getting lock
BEGIN
SELECT -1
END |
| MYTESTSERVER | 50402461905512618412912820000000000000 | 6789720 | 4 | 2117582582 | 1 | False | CREATE PROCEDURE sp_verify_job_identifiers @name_of_name_parameter VARCHAR(60), -- Eg. '@job_name' @name_of_id_parameter VARCHAR(60), -- Eg. '@job_id' @job_name sysname OUTPUT, -- Eg. 'My Job' @job_id UNIQUEIDENTIFIER OUTPUT, @sqlagent_starting_test VARCHAR(7) = 'TEST', -- By default we DO want to test if SQLServerAgent is running (caller should specify 'NO_TEST' if not desired) @owner_sid VARBINARY(85) = NULL OUTPUT ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @name_of_name_parameter = LTRIM(RTRIM(@name_of_name_parameter)) SELECT @name_of_id_parameter = LTRIM(RTRIM(@name_of_id_parameter)) SELECT @job_name = LTRIM(RTRIM(@job_name)) IF (@job_name = N'') SELECT @job_name = NULL IF ((@job_name IS NULL) AND (@job_id IS NULL)) OR ((@job_name IS NOT NULL) AND (@job_id IS NOT NULL)) BEGIN RAISERROR(14294, -1, -1, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- Check job id IF (@job_id IS NOT NULL) BEGIN SELECT @job_name = name, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) -- the view would take care of all the permissions issues. IF (@job_name IS NULL) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- Failure END END ELSE -- Check job name IF (@job_name IS NOT NULL) BEGIN -- Check if the job name is ambiguous IF ((SELECT COUNT(*) FROM msdb.dbo.sysjobs_view WHERE (name = @job_name)) > 1) BEGIN RAISERROR(14293, -1, -1, @job_name, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- The name is not ambiguous, so get the corresponding job_id (if the job exists) SELECT @job_id = job_id, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (name = @job_name) -- the view would take care of all the permissions issues. IF (@job_id IS NULL) BEGIN RAISERROR(14262, -1, -1, '@job_name', @job_name) RETURN(1) -- Failure END END IF (@sqlagent_starting_test = 'TEST') BEGIN -- Finally, check if SQLServerAgent is in the process of starting and if so prevent the -- calling SP from running EXECUTE @retval = msdb.dbo.sp_is_sqlagent_starting IF (@retval <> 0) RETURN(1) -- Failure END RETURN(0) -- SuccessEND |
| MYTESTSERVER | 50402461905512618412912820000000000000 | 6996500 | 4 | 2117582582 | 1 | False | CREATE PROCEDURE sp_verify_job_identifiers @name_of_name_parameter VARCHAR(60), -- Eg. '@job_name' @name_of_id_parameter VARCHAR(60), -- Eg. '@job_id' @job_name sysname OUTPUT, -- Eg. 'My Job' @job_id UNIQUEIDENTIFIER OUTPUT, @sqlagent_starting_test VARCHAR(7) = 'TEST', -- By default we DO want to test if SQLServerAgent is running (caller should specify 'NO_TEST' if not desired) @owner_sid VARBINARY(85) = NULL OUTPUT ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @name_of_name_parameter = LTRIM(RTRIM(@name_of_name_parameter)) SELECT @name_of_id_parameter = LTRIM(RTRIM(@name_of_id_parameter)) SELECT @job_name = LTRIM(RTRIM(@job_name)) IF (@job_name = N'') SELECT @job_name = NULL IF ((@job_name IS NULL) AND (@job_id IS NULL)) OR ((@job_name IS NOT NULL) AND (@job_id IS NOT NULL)) BEGIN RAISERROR(14294, -1, -1, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- Check job id IF (@job_id IS NOT NULL) BEGIN SELECT @job_name = name, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) -- the view would take care of all the permissions issues. IF (@job_name IS NULL) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- Failure END END ELSE -- Check job name IF (@job_name IS NOT NULL) BEGIN -- Check if the job name is ambiguous IF ((SELECT COUNT(*) FROM msdb.dbo.sysjobs_view WHERE (name = @job_name)) > 1) BEGIN RAISERROR(14293, -1, -1, @job_name, @name_of_id_parameter, @name_of_name_parameter) RETURN(1) -- Failure END -- The name is not ambiguous, so get the corresponding job_id (if the job exists) SELECT @job_id = job_id, @owner_sid = owner_sid FROM msdb.dbo.sysjobs_view WHERE (name = @job_name) -- the view would take care of all the permissions issues. IF (@job_id IS NULL) BEGIN RAISERROR(14262, -1, -1, '@job_name', @job_name) RETURN(1) -- Failure END END IF (@sqlagent_starting_test = 'TEST') BEGIN -- Finally, check if SQLServerAgent is in the process of starting and if so prevent the -- calling SP from running EXECUTE @retval = msdb.dbo.sp_is_sqlagent_starting IF (@retval <> 0) RETURN(1) -- Failure END RETURN(0) -- SuccessEND |
| MYTESTSERVER | 5080217661511091842255221000000000000 | 9416941 | 8 | 1838629593 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_BizTalkServerApplication]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_BizTalkServerApplication] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [BizTalkServerApplicationQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [BizTalkServerApplication_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [BizTalkServerApplicationQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [BizTalkServerApplication_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 508024818817011918416121329000000000000 | 9418016 | 8 | 2007678200 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_test]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_test] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [testQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [test_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [testQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [test_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 504019986248318412922943000000000000 | 9447249 | 4 | 1394104007 | 1 | False | CREATE PROCEDURE sp_sqlagent_log_jobhistory @job_id UNIQUEIDENTIFIER, @step_id INT, @sql_message_id INT = 0, @sql_severity INT = 0, @message NVARCHAR(1024) = NULL, @run_status INT, -- SQLAGENT_EXEC_X code @run_date INT, @run_time INT, @run_duration INT, @operator_id_emailed INT = 0, @operator_id_netsent INT = 0, @operator_id_paged INT = 0, @retries_attempted INT, @server sysname = NULL, @session_id INT = 0ASBEGIN DECLARE @retval INT DECLARE @job_id_as_char VARCHAR(36) DECLARE @step_id_as_char VARCHAR(10) DECLARE @operator_id_as_char VARCHAR(10) DECLARE @step_name sysname DECLARE @error_severity INT SET NOCOUNT ON IF (@server IS NULL) OR (UPPER(@server collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)') SELECT @server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) -- Check authority (only SQLServerAgent can add a history entry for a job) EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%' IF (@retval <> 0) RETURN(@retval) -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching -- the operation (if it fails) since if the operation will never run successfully we -- don't want it to hang around in the operation cache. SELECT @error_severity = 0 -- Check job_id IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id))) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char) RETURN(1) -- Failure END -- Check step id IF (@step_id <> 0) -- 0 means 'for the whole job' BEGIN SELECT @step_name = step_name FROM msdb.dbo.sysjobsteps WHERE (job_id = @job_id) AND (step_id = @step_id) IF (@step_name IS NULL) BEGIN SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id) RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char) RETURN(1) -- Failure END END ELSE SELECT @step_name = FORMATMESSAGE(14570) -- Check run_status IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code BEGIN RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5') RETURN(1) -- Failure END -- Check run_date EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check run_time EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10 IF (@retval <> 0) RETURN(1) -- Failure -- Check operator_id_emailed IF (@operator_id_emailed <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_emailed))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed) RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_netsent IF (@operator_id_netsent <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_netsent))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent) RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char) RETURN(1) -- Failure END END -- Check operator_id_paged IF (@operator_id_paged <> 0) BEGIN IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysoperators WHERE (id = @operator_id_paged))) BEGIN SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged) RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char) RETURN(1) -- Failure END END -- Insert the history row INSERT INTO msdb.dbo.sysjobhistory (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server) VALUES (@job_id, @step_id, @step_name, @sql_message_id, @sql_severity, @message, @run_status, @run_date, @run_time, @run_duration, @operator_id_emailed, @operator_id_netsent, @operator_id_paged, @retries_attempted, @server) -- Update sysjobactivity table IF (@step_id = 0) --only update for job, not for each step BEGIN UPDATE msdb.dbo.sysjobactivity SET stop_execution_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), job_history_id = SCOPE_IDENTITY() WHERE session_id = @session_id AND job_id = @job_id END -- Special handling of replication jobs DECLARE @job_name sysname DECLARE @category_id int SELECT @job_name = name, @category_id = category_id from msdb.dbo.sysjobs WHERE job_id = @job_id -- If replicatio agents (snapshot, logreader, distribution, merge, and queuereader -- and the step has been canceled and if we are at the distributor. IF @category_id in (10,13,14,15,19) and @run_status = 3 and object_id('MSdistributiondbs') is not null BEGIN -- Get the database DECLARE @database sysname SELECT @database = database_name from sysjobsteps where job_id = @job_id and lower(subsystem) in (N'distribution', N'logreader','snapshot',N'merge', N'queuereader') -- If the database is a distribution database IF EXISTS (select * from MSdistributiondbs where name = @database) BEGIN DECLARE @proc nvarchar(500) SELECT @proc = quotename(@database) + N'.dbo.sp_MSlog_agent_cancel' EXEC @proc @job_id = @job_id, @category_id = @category_id, @message = @message END END -- Delete any history rows that are over the registry-defined limits EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id RETURN(@@error) -- 0 means successEND |
| MYTESTSERVER | 508067148249301841611967000000000000 | 9804904 | 8 | 519672899 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_toto]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_toto] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [totoQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [toto_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [totoQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [toto_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 601002055616491846511743000000000000 | 10515867 | NULL | NULL | NULL | False | (@1 varchar(8000))SELECT [servername],[uuid] FROM [relrcpt] WITH(updlock,rowlock,readpast) WHERE [servername]=@1 |
| MYTESTSERVER | 5080217661511091842255221000000000000 | 13359490 | 8 | 1838629593 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_BizTalkServerApplication]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_BizTalkServerApplication] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [BizTalkServerApplicationQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [BizTalkServerApplication_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [BizTalkServerApplicationQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [BizTalkServerApplication_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 60100244192175218412925235000000000000 | 13363987 | NULL | NULL | NULL | False | SELECT TOP 1 icin,msgin,dtte,tmte,dute,dtcs,tmcs,ducs,bytcnt,mstscd,pc,dc,msrvcd,sid,rid,refpas,ti,icr,mrn,mcvr,ref,ownpc,owndc,msgout,icout,sidcdq,ridcdq,fcvr,msgttk,routad,del_not,resetyn,resetdate,resettime,resetby,hashtot,dutems,ducsms,msrvcdout,grpin,gcr,grpout,sroutad,servername,messagedigest,origfilename,setname FROM audin WITH (UPDLOCK, READPAST, ROWLOCK) WHERE mstscd=4 AND (servername='' OR servername IS NULL) AND resetyn<=1 AND (msrvcdout=2) |
| MYTESTSERVER | 508024818817011918416121329000000000000 | 13547098 | 8 | 2007678200 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_test]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_test] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [testQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [test_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [testQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [test_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 60100244491324718416124063000000000000 | 13928796 | NULL | NULL | NULL | False | SELECT TOP 1 msgout,icout,dtbdd,tmbdd,dubdd,dtte,tmte,dute,dtcs,tmcs,ducs,pdtcs,ptmcs,bytcnt,mstscd,pc,dc,msrvcd,sid,rid,refpas,ti,mcvr,ref,uname,prog,del_not,ownpc,owndc,icin,msgin,sidcdq,ridcdq,fcvr,msgttk,routad,clientid,resetyn,resetdate,resettime,resetby,hashtot,dttpli,tmtpli,dtfpli,tmfpli,prio,dubddms,dutems,ducsms,seqno,bloffs,msrvcdout,icr,grpout,grpin,sroutad,servername,messagedigest,setname FROM audout WITH (UPDLOCK, READPAST, ROWLOCK) WHERE mstscd=3 AND resetyn<=1 AND msrvcdout=1 AND (pdtcs IS NULL OR ptmcs<GETDATE()) |
| MYTESTSERVER | 508067148249301841611967000000000000 | 14040015 | 8 | 519672899 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_toto]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_toto] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [totoQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [toto_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [totoQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [toto_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 50401425036821841939646000000000000 | 17305026 | 4 | 1378103950 | 1 | False | CREATE PROCEDURE sp_jobhistory_row_limiter @job_id UNIQUEIDENTIFIERASBEGIN DECLARE @max_total_rows INT -- This value comes from the registry (MaxJobHistoryTableRows) DECLARE @max_rows_per_job INT -- This value comes from the registry (MaxJobHistoryRows) DECLARE @rows_to_delete INT DECLARE @rows_to_delete_as_char VARCHAR(10) DECLARE @current_rows INT DECLARE @current_rows_per_job INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) -- Get max-job-history-rows from the registry EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', @max_total_rows OUTPUT, N'no_output' -- Check if we are limiting sysjobhistory rows IF (ISNULL(@max_total_rows, -1) = -1) RETURN(0) -- Check that max_total_rows is more than 1 IF (ISNULL(@max_total_rows, 0) < 2) BEGIN -- It isn't, so set the default to 1000 rows SELECT @max_total_rows = 1000 EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', N'REG_DWORD', @max_total_rows END -- Get the per-job maximum number of rows to keep SELECT @max_rows_per_job = 0 EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', @max_rows_per_job OUTPUT, N'no_output' -- Check that max_rows_per_job is <= max_total_rows IF ((@max_rows_per_job > @max_total_rows) OR (@max_rows_per_job < 1)) BEGIN -- It isn't, so default the rows_per_job to max_total_rows SELECT @max_rows_per_job = @max_total_rows EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', N'REG_DWORD', @max_rows_per_job END BEGIN TRANSACTION SELECT @current_rows_per_job = COUNT(*) FROM msdb.dbo.sysjobhistory with (TABLOCKX) WHERE (job_id = @job_id) -- Delete the oldest history row(s) for the job being inserted if the new row has -- pushed us over the per-job row limit (MaxJobHistoryRows) SELECT @rows_to_delete = @current_rows_per_job - @max_rows_per_job SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete) IF (@rows_to_delete > 0) BEGIN EXECUTE ('DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT ' + @rows_to_delete_as_char + 'SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory WHERE (job_id = ''' + @job_id_as_char + ''') ' + 'ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (job_id = ''' + @job_id_as_char + ''')' + ' AND (instance_id <= @new_oldest_id)') END -- Delete the oldest history row(s) if inserting the new row has pushed us over the -- global MaxJobHistoryTableRows limit. SELECT @current_rows = COUNT(*) FROM msdb.dbo.sysjobhistory SELECT @rows_to_delete = @current_rows - @max_total_rows SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete) IF (@rows_to_delete > 0) BEGIN EXECUTE ('DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT ' + @rows_to_delete_as_char + 'SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (instance_id <= @new_oldest_id)') END IF (@@trancount > 0) COMMIT TRANSACTION RETURN(0) -- SuccessEND |
| MYTESTSERVER | 508024818817011918416121329000000000000 | 20730999 | 8 | 2007678200 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_test]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_test] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [testQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [test_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [testQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [test_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 508067148249301841611967000000000000 | 20939039 | 8 | 519672899 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_toto]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_toto] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [totoQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [toto_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [totoQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [toto_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 5080217661511091842255221000000000000 | 21335175 | 8 | 1838629593 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_BizTalkServerApplication]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_BizTalkServerApplication] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [BizTalkServerApplicationQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [BizTalkServerApplication_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [BizTalkServerApplicationQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [BizTalkServerApplication_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 504025423822566184654423000000000000 | 30807801 | 4 | 1122103038 | 1 | False | CREATE PROCEDURE sp_get_composite_job_info @job_id UNIQUEIDENTIFIER = NULL, @job_type VARCHAR(12) = NULL, -- LOCAL or MULTI-SERVER @owner_login_name sysname = NULL, @subsystem NVARCHAR(40) = NULL, @category_id INT = NULL, @enabled TINYINT = NULL, @execution_status INT = NULL, -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions @date_comparator CHAR(1) = NULL, -- >, < or = @date_created DATETIME = NULL, @date_last_modified DATETIME = NULL, @description NVARCHAR(512) = NULL, -- We do a LIKE on this so it can include wildcards @schedule_id INT = NULL -- if supplied only return the jobs that use this scheduleASBEGIN DECLARE @is_sysadmin INT DECLARE @job_owner sysname SET NOCOUNT ON -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data. -- This proc should only ever be called by sp_help_job, so we don't verify the -- parameters (sp_help_job has already done this). -- Step 1: Create intermediate work tables DECLARE @job_execution_state TABLE (job_id UNIQUEIDENTIFIER NOT NULL, date_started INT NOT NULL, time_started INT NOT NULL, execution_job_status INT NOT NULL, execution_step_id INT NULL, execution_step_name sysname COLLATE database_default NULL, execution_retry_attempt INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL) DECLARE @filtered_jobs TABLE (job_id UNIQUEIDENTIFIER NOT NULL, date_created DATETIME NOT NULL, date_last_modified DATETIME NOT NULL, current_execution_status INT NULL, current_execution_step sysname COLLATE database_default NULL, current_retry_attempt INT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, last_run_outcome INT NOT NULL, next_run_date INT NULL, next_run_time INT NULL, next_run_schedule_id INT NULL, type INT NOT NULL) DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches) SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) SELECT @job_owner = SUSER_SNAME() IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id ELSE INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner INSERT INTO @job_execution_state SELECT xpr.job_id, xpr.last_run_date, xpr.last_run_time, xpr.job_state, sjs.step_id, sjs.step_name, xpr.current_retry_attempt, xpr.next_run_date, xpr.next_run_time, xpr.next_run_schedule_id FROM @xp_results xpr LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)), msdb.dbo.sysjobs_view sjv WHERE (sjv.job_id = xpr.job_id) -- Step 3: Filter on everything but dates and job_type IF ((@subsystem IS NULL) AND (@owner_login_name IS NULL) AND (@enabled IS NULL) AND (@category_id IS NULL) AND (@execution_status IS NULL) AND (@description IS NULL) AND (@job_id IS NULL)) BEGIN -- Optimize for the frequently used case... INSERT INTO @filtered_jobs SELECT sjv.job_id, sjv.date_created, sjv.date_modified, ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE) CASE ISNULL(jes.execution_step_id, 0) WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in @job_execution_state ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')' END, jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in @job_execution_state 0, -- last_run_date placeholder (we'll fix it up in step 3.3) 0, -- last_run_time placeholder (we'll fix it up in step 3.3) 5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job) jes.next_run_date, -- Will be NULL if the job is non-local or is not in @job_execution_state jes.next_run_time, -- Will be NULL if the job is non-local or is not in @job_execution_state jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in @job_execution_state 0 -- type placeholder (we'll fix it up in step 3.4) FROM msdb.dbo.sysjobs_view sjv LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id) WHERE ((@schedule_id IS NULL) OR (EXISTS(SELECT * FROM sysjobschedules as js WHERE (sjv.job_id = js.job_id) AND (js.schedule_id = @schedule_id)))) END ELSE BEGIN INSERT INTO @filtered_jobs SELECT DISTINCT sjv.job_id, sjv.date_created, sjv.date_modified, ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE) CASE ISNULL(jes.execution_step_id, 0) WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in @job_execution_state ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')' END, jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in @job_execution_state 0, -- last_run_date placeholder (we'll fix it up in step 3.3) 0, -- last_run_time placeholder (we'll fix it up in step 3.3) 5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job) jes.next_run_date, -- Will be NULL if the job is non-local or is not in @job_execution_state jes.next_run_time, -- Will be NULL if the job is non-local or is not in @job_execution_state jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in @job_execution_state 0 -- type placeholder (we'll fix it up in step 3.4) FROM msdb.dbo.sysjobs_view sjv LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id) LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id) WHERE ((@subsystem IS NULL) OR (sjs.subsystem = @subsystem)) AND ((@owner_login_name IS NULL) OR (sjv.owner_sid = dbo.SQLAGENT_SUSER_SID(@owner_login_name)))--force case insensitive comparation for NT users AND ((@enabled IS NULL) OR (sjv.enabled = @enabled)) AND ((@category_id IS NULL) OR (sjv.category_id = @category_id)) AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status)) OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5))) AND ((@description IS NULL) OR (sjv.description LIKE @description)) AND ((@job_id IS NULL) OR (sjv.job_id = @job_id)) AND ((@schedule_id IS NULL) OR (EXISTS(SELECT * FROM sysjobschedules as js WHERE (sjv.job_id = js.job_id) AND (js.schedule_id = @schedule_id)))) END -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown' UPDATE @filtered_jobs SET current_execution_status = NULL WHERE (current_execution_status = 4) AND (job_id IN (SELECT job_id FROM msdb.dbo.sysjobservers WHERE (server_id <> 0))) -- Step 3.2: Check that if the user asked to see idle jobs that we still have some. -- If we don't have any then the query should return no rows. IF (@execution_status = 4) AND (NOT EXISTS (SELECT * FROM @filtered_jobs WHERE (current_execution_status = 4))) BEGIN DELETE FROM @filtered_jobs END -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for -- multi-server jobs there are multiple last run details in sysjobservers, so -- we simply choose the most recent]. IF (EXISTS (SELECT * FROM msdb.dbo.systargetservers)) BEGIN UPDATE @filtered_jobs SET last_run_date = sjs.last_run_date, last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time = (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time) FROM msdb.dbo.sysjobservers WHERE (job_id = sjs.job_id)) AND (fj.job_id = sjs.job_id) END ELSE BEGIN UPDATE @filtered_jobs SET last_run_date = sjs.last_run_date, last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id) END -- Step 3.4 : Set the type of the job to local (1) or multi-server (2) -- NOTE: If the job has no jobservers then it wil have a type of 0 meaning -- unknown. This is marginally inconsistent with the behaviour of -- defaulting the category of a new job to [Uncategorized (Local)], but -- prevents incompletely defined jobs from erroneously showing up as valid -- local jobs. UPDATE @filtered_jobs SET type = 1 -- LOCAL FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id) AND (server_id = 0) UPDATE @filtered_jobs SET type = 2 -- MULTI-SERVER FROM @filtered_jobs fj, msdb.dbo.sysjobservers sjs WHERE (fj.job_id = sjs.job_id) AND (server_id <> 0) -- Step 4: Filter on job_type IF (@job_type IS NOT NULL) BEGIN IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'LOCAL') DELETE FROM @filtered_jobs WHERE (type <> 1) -- IE. Delete all the non-local jobs IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'MULTI-SERVER') DELETE FROM @filtered_jobs WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs END -- Step 5: Filter on dates IF (@date_comparator IS NOT NULL) BEGIN IF (@date_created IS NOT NULL) BEGIN IF (@date_comparator = '=') DELETE FROM @filtered_jobs WHERE (date_created <> @date_created) IF (@date_comparator = '>') DELETE FROM @filtered_jobs WHERE (date_created <= @date_created) IF (@date_comparator = '<') DELETE FROM @filtered_jobs WHERE (date_created >= @date_created) END IF (@date_last_modified IS NOT NULL) BEGIN IF (@date_comparator = '=') DELETE FROM @filtered_jobs WHERE (date_last_modified <> @date_last_modified) IF (@date_comparator = '>') DELETE FROM @filtered_jobs WHERE (date_last_modified <= @date_last_modified) IF (@date_comparator = '<') DELETE FROM @filtered_jobs WHERE (date_last_modified >= @date_last_modified) END END -- Return the result set (NOTE: No filtering occurs here) SELECT sjv.job_id, originating_server, sjv.name, sjv.enabled, sjv.description, sjv.start_step_id, category = ISNULL(sc.name, FORMATMESSAGE(14205)), owner = dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid), sjv.notify_level_eventlog, sjv.notify_level_email, sjv.notify_level_netsend, sjv.notify_level_page, notify_email_operator = ISNULL(so1.name, FORMATMESSAGE(14205)), notify_netsend_operator = ISNULL(so2.name, FORMATMESSAGE(14205)), notify_page_operator = ISNULL(so3.name, FORMATMESSAGE(14205)), sjv.delete_level, sjv.date_created, sjv.date_modified, sjv.version_number, fj.last_run_date, fj.last_run_time, fj.last_run_outcome, next_run_date = ISNULL(fj.next_run_date, 0), -- This column will be NULL if the job is non-local next_run_time = ISNULL(fj.next_run_time, 0), -- This column will be NULL if the job is non-local next_run_schedule_id = ISNULL(fj.next_run_schedule_id, 0), -- This column will be NULL if the job is non-local current_execution_status = ISNULL(fj.current_execution_status, 0), -- This column will be NULL if the job is non-local current_execution_step = ISNULL(fj.current_execution_step, N'0 ' + FORMATMESSAGE(14205)), -- This column will be NULL if the job is non-local current_retry_attempt = ISNULL(fj.current_retry_attempt, 0), -- This column will be NULL if the job is non-local has_step = (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps sjst WHERE (sjst.job_id = sjv.job_id)), has_schedule = (SELECT COUNT(*) FROM msdb.dbo.sysjobschedules sjsch WHERE (sjsch.job_id = sjv.job_id)), has_target = (SELECT COUNT(*) FROM msdb.dbo.sysjobservers sjs WHERE (sjs.job_id = sjv.job_id)), type = fj.type FROM @filtered_jobs fj LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id) LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id) LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id) ORDER BY sjv.job_idEND |
| MYTESTSERVER | 501803118825110518412919547000000000000 | 31874751 | 18 | 1778105375 | 1 | False | CREATE PROCEDURE [dbo].[int_IsAgentJobRunning]
@jobName sysname
AS
DECLARE @jobId uniqueidentifier
,@ReturnCode BIT
IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results
CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL )
set @ReturnCode = 0
If @jobName IS NOT NULL
BEGIN
select TOP 1 @jobId=job_id from msdb.dbo.sysjobs where name = @jobName
if ( (@@ROWCOUNT = 1) AND (@jobId IS NOT NULL) )
BEGIN
--job_owner parameter is ignored if the first param is 1. Only care about job_id
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin=1, @job_owner='sa' , @job_id = @jobId
--SELECT * FROM #xp_results
--states 4 and 5 are the idle states. Everything else is considered still executing
IF NOT EXISTS (SELECT 1 FROM #xp_results WHERE job_state in (4,5))
BEGIN
set @ReturnCode = 1
END
END
END
DROP TABLE #xp_results
return @ReturnCode |
| MYTESTSERVER | 50805943431021841938356000000000000 | 32238530 | 8 | 1714105147 | 1 | False | CREATE PROCEDURE [dbo].[int_IsAgentJobRunning]
@jobName sysname
AS
DECLARE @jobId uniqueidentifier
,@ReturnCode BIT
IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results
CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL )
set @ReturnCode = 0
If @jobName IS NOT NULL
BEGIN
select TOP 1 @jobId=job_id from msdb.dbo.sysjobs where name = @jobName
if ( (@@ROWCOUNT = 1) AND (@jobId IS NOT NULL) )
BEGIN
--job_owner parameter is ignored if the first param is 1. Only care about job_id
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin=1, @job_owner='sa' , @job_id = @jobId
--SELECT * FROM #xp_results
--states 4 and 5 are the idle states. Everything else is considered still executing
IF NOT EXISTS (SELECT 1 FROM #xp_results WHERE job_state in (4,5))
BEGIN
set @ReturnCode = 1
END
END
END
DROP TABLE #xp_results
return @ReturnCode |
| MYTESTSERVER | 50801792221103918411461000000000000 | 37787446 | 8 | 661577395 | 1 | False | CREATE PROCEDURE [dbo].[TDDS_GetTrackingData]
@DestinationID tinyint,
@PartitionID tinyint,
@LastReadSeqNum bigint,
@nMaxSeqNum bigint output
AS
declare @key tinyint
set @key = [dbo].[TDDS_fnGetTrackingDataKey](@DestinationID, @PartitionID)
IF (@DestinationID = 0)
BEGIN
SELECT TOP 30 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
ELSE
BEGIN
SELECT TOP 100 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
declare @trackingTableName nvarchar(128)
set @trackingTableName = N'TrackingData_' + CAST(@DestinationID as nchar(1)) + N'_' + CAST(@PartitionID as nchar(1))
select @nMaxSeqNum= ident_current(@trackingTableName) |
| MYTESTSERVER | 50801792221103918411461000000000000 | 43150696 | 8 | 661577395 | 1 | False | CREATE PROCEDURE [dbo].[TDDS_GetTrackingData]
@DestinationID tinyint,
@PartitionID tinyint,
@LastReadSeqNum bigint,
@nMaxSeqNum bigint output
AS
declare @key tinyint
set @key = [dbo].[TDDS_fnGetTrackingDataKey](@DestinationID, @PartitionID)
IF (@DestinationID = 0)
BEGIN
SELECT TOP 30 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
ELSE
BEGIN
SELECT TOP 100 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
declare @trackingTableName nvarchar(128)
set @trackingTableName = N'TrackingData_' + CAST(@DestinationID as nchar(1)) + N'_' + CAST(@PartitionID as nchar(1))
select @nMaxSeqNum= ident_current(@trackingTableName) |
| MYTESTSERVER | 50801792221103918411461000000000000 | 44102734 | 8 | 661577395 | 1 | False | CREATE PROCEDURE [dbo].[TDDS_GetTrackingData]
@DestinationID tinyint,
@PartitionID tinyint,
@LastReadSeqNum bigint,
@nMaxSeqNum bigint output
AS
declare @key tinyint
set @key = [dbo].[TDDS_fnGetTrackingDataKey](@DestinationID, @PartitionID)
IF (@DestinationID = 0)
BEGIN
SELECT TOP 30 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
ELSE
BEGIN
SELECT TOP 100 [SeqNum], [uidServiceID], [StreamID], [dtTimeStamp], [strDBServer], [strDBName], [strServerName], [nSource], [uidAgentSrvID], [uidServiceClassID], [imgBlob], [FormatID], [DestinationID], [PartitionID]
FROM [dbo].[TrackingData]
WHERE partKey = @key AND SeqNum > @LastReadSeqNum
ORDER BY [SeqNum] ASC -- Don't change this. It is very important for the TDDS Writer that the result set is sorted by [SeqNum].
END
declare @trackingTableName nvarchar(128)
set @trackingTableName = N'TrackingData_' + CAST(@DestinationID as nchar(1)) + N'_' + CAST(@PartitionID as nchar(1))
select @nMaxSeqNum= ident_current(@trackingTableName) |
| MYTESTSERVER | 508024818817011918416121329000000000000 | 90781603 | 8 | 2007678200 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_test]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [testQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'7CEFF61A-D95E-4790-BC23-70F034605BA7')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_test] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [testQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [test_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [testQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [test_DequeueBatches] b
INNER LOOP JOIN [testQ] AS w WITH (ROWLOCK INDEX([IX_testQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [test_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 508067148249301841611967000000000000 | 92155826 | 8 | 519672899 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_toto]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [totoQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'6677F362-2FFD-4929-875A-EC8623C6481E')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_toto] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [totoQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [toto_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [totoQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [toto_DequeueBatches] b
INNER LOOP JOIN [totoQ] AS w WITH (ROWLOCK INDEX([IX_totoQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [toto_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 5080217661511091842255221000000000000 | 92576229 | 8 | 1838629593 | 1 | False | CREATE PROCEDURE [dbo].[bts_DeQueueMessages_BizTalkServerApplication]
@uidClassID uniqueidentifier,
@uidAppInstanceID uniqueidentifier,
@nBatchCount int,
@dwCommand int
AS
set nocount on
set transaction isolation level read committed
set deadlock_priority low
declare @dtNow datetime,
@dtUTCNow datetime
set @dtNow = GetDate() --we stored everything in local for service windows
set @dtUTCNow = GetUTCDate()
declare @uidInstanceID uniqueidentifier,
@uidMessageID uniqueidentifier,
@uidWorkID uniqueidentifier,
@uidBatchID uniqueidentifier,
@snPartRetrieval smallint,
@fOrderedDelivery int,
@nCount int,
@fQueueEmpty int,
@retVal int,
@temp uniqueidentifier,
@fFirstMessage int,
@fBodyPartMsgExists int,
@fNoPartMsgExists int,
@fAllPartsMsgExists int,
@uidProcessID uniqueidentifier,
@uidActivationID uniqueidentifier,
@fDequeueCurrentMsg int,
@fInstanceLocked int,
@fOrderedDeliveryExists int,
@nState int,
@dtLastTouched datetime,
@uidTempProcessID uniqueidentifier,
@fSkipActivation int,
@fSkipCorrelation int,
@fSkipOrderedDelivery int,
@fOptimize smallint,
@snPriority smallint,
@nID int
declare @fDequeueOrderedMessages bit,
@fFoundOrderedMessages bit
set @uidProcessID = APP_NAME()
set @uidBatchID = NewID()
set @fFoundOrderedMessages = 0
set @nCount = 0
if ( (@dwCommand & 4) > 0 )
set @fDequeueOrderedMessages = 0
ELSE
set @fDequeueOrderedMessages = 1
START_DEQUEUE:
set @fQueueEmpty = 1
set @fOptimize = 1
if (@fDequeueOrderedMessages = 0)
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK READPAST)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK READPAST) ON ( w.uidInstanceID = i.uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 0 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
DECLARE btscurse CURSOR FAST_FORWARD FOR
SELECT TOP 20 w.uidWorkID, w.uidInstanceID, w.uidMessageID, w.snPartRetrieval, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID, i.uidProcessID, i.uidActivationID, i.nState
FROM [BizTalkServerApplicationQ] AS w WITH (UPDLOCK ROWLOCK)
INNER LOOP JOIN Instances AS i WITH (UPDLOCK ROWLOCK) ON ( w.uidInstanceID = i.uidInstanceID AND i.dtValidTime <= @dtUTCNow AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925') AND ( i.uidProcessID IS NULL OR i.uidProcessID = @uidProcessID )
WHERE
--This determines if we already dequeued this Item
( w.uidProcessID IS NULL ) AND
--This determines if we should take Ordered Delivery Items or not
-- (Ordered Delivery can be DQed by the Primary DQ thread only)
w.fOrderedDelivery = 1 AND
--This determines if it is past the valid time
( @dtUTCNow >= w.dtValid ) AND
-- This determines if it is in the window
1 = [dbo].[bts_fnInWindow](@dtNow, w.dtStartWindow, w.dtEndWindow) AND
-- This makes sure we Dequeue for the given Service Class ID
(
w.uidClassID = @uidClassID
) AND
-- This makes sure we dequeue for the given Application Process
(
( w.uidAppInstanceID IS NULL )
OR ( @uidAppInstanceID IS NULL )
OR ( w.uidAppInstanceID = @uidAppInstanceID )
)
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
OPEN btscurse
WHILE ( (@nCount < @nBatchCount) )
BEGIN
set @fFirstMessage = 0
FETCH NEXT FROM btscurse INTO @uidWorkID, @uidInstanceID, @uidMessageID, @snPartRetrieval, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID, @uidTempProcessID, @uidActivationID, @nState
-- If the Row is missing, skip to the next Row
IF ( @@FETCH_STATUS = -2 )
BEGIN
CONTINUE -- Try the Next Row
END
ELSE IF ( @@FETCH_STATUS <> 0 ) -- Other Failure. Break Out
BEGIN
BREAK
END
-- Make sure that no other parallel DQ thread is contending on this Row. Take a transaction AppLock on the Work ID
exec @retVal = sp_getapplock @uidWorkID, 'Exclusive', 'Transaction', 0
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Work-item. Continue with the next one
BEGIN
CONTINUE
END
-- If this is an Acivation, verify that this Instance is still available, i.e. no parallel DQ thread of another process
-- has taken the Instance and Lock the Instance
-- Re-fetch the Instance record with a REPEATABLE READ lock on the Instance so that no one is able to update the Instance
-- while we are still considering taking it
set @uidTempProcessID = NULL
SELECT @uidTempProcessID = uidProcessID, @uidActivationID = uidActivationID, @nState = nState, @fOptimize = fOptimize FROM Instances WITH (READPAST ROWLOCK REPEATABLEREAD)
WHERE (uidInstanceID = @uidInstanceID AND uidAppOwnerID = N'C0DF040D-3B53-47E9-AF8E-72E41EC1F925')
OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT = 0) -- Locked, bypass the row
BEGIN
CONTINUE
END
if ( @uidTempProcessID IS NULL )
BEGIN
set @uidActivationID = @uidBatchID
exec [dbo].[int_GetInstanceLock_BizTalkServerApplication] @uidInstanceID, @uidActivationID, 2, @retVal OUTPUT
if (@retVal <> 0 ) -- Not Granted. Some other DQ thread is trying to get this Instance
BEGIN
CONTINUE
END
--we are activating this instance (it was ready-to-run or it was scheduled)
if ( (@nState = 1) OR (@nState = 256) )
set @fFirstMessage = 1
END
else if ( @uidTempProcessID <> @uidProcessID ) -- This Instance is already dequeued by someone else in between our cursor read and repeatable read
BEGIN
CONTINUE
END
-- Update the Queue Item. We add the first message flag since it would also include convoy information when appropriate
UPDATE [BizTalkServerApplicationQ] SET uidProcessID = @uidProcessID, fOptimize = @fOptimize, fFirstMessage = fFirstMessage + @fFirstMessage, uidActivationID = @uidActivationID
WHERE uidClassID = @uidClassID AND fOrderedDelivery = @fOrderedDelivery AND snPriority = @snPriority AND dtLastTouched = @dtLastTouched AND nID = @nID AND uidProcessID IS NULL
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
set @nCount = @nCount + 1
INSERT INTO [BizTalkServerApplication_DequeueBatches] (uidBatchID, uidClassID, fOrderedDelivery, snPriority, dtLastTouched, nID) VALUES (@uidBatchID, @uidClassID, @fOrderedDelivery, @snPriority, @dtLastTouched, @nID)
END
END
IF ( @@FETCH_STATUS = 0 )
BEGIN
set @fQueueEmpty = 0 -- Indicate that there are Stuff to be processed in the Queue by Other Sessions
END
CLOSE btscurse
DEALLOCATE btscurse
if ( (@nCount > 0) AND (@fDequeueOrderedMessages > 0) )
BEGIN
set @fFoundOrderedMessages = 1
END
if ( (@nCount < @nBatchCount) AND (@fDequeueOrderedMessages > 0) )
BEGIN
--this was an ordered delivery dequeue attempt which returned zero rows. Instead of returning 0, lets run through again and pretend we are not ordered delivery
set @fDequeueOrderedMessages = 0
set @nBatchCount = @nBatchCount - @nCount --only get however many more we need
GOTO START_DEQUEUE
END
IF ( @fQueueEmpty > 0 )
BEGIN
--we use the nolock hint here because this is purely an optimization and we dont even want to hold shared locks because of deadlock scenarios
SELECT TOP 1 @uidInstanceID = uidInstanceID FROM [BizTalkServerApplicationQ] WITH (NOLOCK) WHERE uidClassID = @uidClassID AND uidProcessID IS NULL OPTION (KEEPFIXED PLAN)
if ( @@ROWCOUNT > 0 ) set @fQueueEmpty = 0
END
SELECT @nCount, @fQueueEmpty
IF ( @nCount = 0 )
BEGIN
return
END
if (@fFoundOrderedMessages = 0)
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
END
ELSE
BEGIN
SELECT TOP 20 w.uidMessageID, s.dtTimeStamp, s.dtExpiration, s.nNumParts, s.OriginatorSID, s.OriginatorPID,
w.uidClassID, w.uidServiceID, w.uidInstanceID, w.uidSubscriptionID, w.uidPortID, w.uidWorkID,
NULL, NULL, s.imgContext, w.nRetryCount, w.nReserved, w.fFirstMessage, NULL, w.uidActivationID, w.fOptimize
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN Spool AS s WITH (ROWLOCK INDEX(IX_Spool)) ON (w.uidMessageID = s.uidMessageID)
WHERE ( b.uidBatchID = @uidBatchID )
ORDER BY w.uidClassID, w.fOrderedDelivery, w.snPriority, w.dtLastTouched, w.nID
OPTION(KEEPFIXED PLAN)
END
SELECT TOP 80 w.uidMessageID,
mp.nvcPartName, mp.uidPartID, mp.nBodyPart,
p.nPartSize, p.nNumFragments,p.imgPropBag, p.imgPart, w.uidWorkID
FROM [BizTalkServerApplication_DequeueBatches] b
INNER LOOP JOIN [BizTalkServerApplicationQ] AS w WITH (ROWLOCK INDEX([IX_BizTalkServerApplicationQ])) ON b.uidClassID = w.uidClassID AND b.fOrderedDelivery = w.fOrderedDelivery AND b.snPriority = w.snPriority AND b.dtLastTouched = w.dtLastTouched AND b.nID = w.nID
INNER LOOP JOIN MessageParts AS mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON (w.uidMessageID = mp.uidMessageID)
INNER LOOP JOIN Parts AS p WITH (ROWLOCK INDEX(IX_Parts)) ON (mp.uidPartID = p.uidPartID)
WHERE ( b.uidBatchID = @uidBatchID )
OPTION(KEEPFIXED PLAN)
DELETE FROM [BizTalkServerApplication_DequeueBatches] WHERE uidBatchID = @uidBatchID
return |
| MYTESTSERVER | 501607325584941846517650000000000000 | 292849783 | 16 | 1582628681 | 1 | False | CREATE PROC [dbo].[NSScheduleQuantum1] @DisableQuantumLimits TINYINT = 0ASBEGIN DECLARE @QuantumIntervalInSeconds INT DECLARE @MaxChronicleQuantumIntervals INT DECLARE @MaxSubscriptionQuantumIntervals INT DECLARE @TimedSubscriptionBias INT DECLARE @LastStatus TINYINT DECLARE @LastQuantumId INT DECLARE @QuantumId INT DECLARE @Skipped INT DECLARE @QuantumsPassed INT DECLARE @EndRealClockTime DATETIME DECLARE @LastQuantumEndTime DATETIME DECLARE @StartTime DATETIME DECLARE @EndTime DATETIME DECLARE @SecondsPassed INT DECLARE @EventChronicleRulesFired TINYINT DECLARE @SubscriptionRulesFired TINYINT DECLARE @MaxQuantumIntervals INT DECLARE @NewQuantumScheduled TINYINT SET NOCOUNT ON BEGIN TRANSACTION SELECT @QuantumIntervalInSeconds = 60 IF (0 = @DisableQuantumLimits) BEGIN SELECT @MaxChronicleQuantumIntervals = 1440 SELECT @MaxSubscriptionQuantumIntervals = 30 END ELSE BEGIN SELECT @MaxChronicleQuantumIntervals = 0 SELECT @MaxSubscriptionQuantumIntervals = 0 END SELECT @EndRealClockTime = GETUTCDATE() IF ((SELECT COUNT(*) FROM [dbo].[NSQuantum1] q WITH (READUNCOMMITTED) WHERE q.QuantumStatusCode IN (1, 3, 7, 135)) > 1) BEGIN RAISERROR (N'4011: There are too many incomplete quanta in the NSQuantum1 table.', 16, 1) ROLLBACK TRAN RETURN 1 END DECLARE @res bit IF EXISTS(SELECT q.QuantumId FROM [dbo].[NSQuantum1] q WITH (READUNCOMMITTED) WHERE q.QuantumStatusCode NOT IN (1, 3, 7, 135) AND @EndRealClockTime >= q.StartTime AND @EndRealClockTime < q.EndTime) BEGIN SET @res=1 END ELSE BEGIN SET @res=0 END IF 1=@res BEGIN RAISERROR (N'4012: There is a completed quantum which includes the current real time.', 16, 1) ROLLBACK TRAN RETURN 1 END SELECT @LastQuantumId = MAX(q.QuantumId) FROM [dbo].[NSQuantum1] q WITH (READUNCOMMITTED) SELECT @Skipped = 0 IF (@LastQuantumId IS NOT NULL) BEGIN IF ((0 = @MaxChronicleQuantumIntervals AND EXISTS(SELECT r.RuleId FROM [dbo].[NSRules] r WITH (READUNCOMMITTED) WHERE r.RuleType = 0)) OR (0 = @MaxSubscriptionQuantumIntervals AND EXISTS(SELECT r.RuleId FROM [dbo].[NSRules] r WITH (READUNCOMMITTED) WHERE r.RuleType > 0))) BEGIN SELECT @MaxQuantumIntervals = 0 END ELSE BEGIN IF (@MaxChronicleQuantumIntervals > @MaxSubscriptionQuantumIntervals) BEGIN SELECT @MaxQuantumIntervals = @MaxChronicleQuantumIntervals END ELSE BEGIN SELECT @MaxQuantumIntervals = @MaxSubscriptionQuantumIntervals END END SELECT @LastQuantumEndTime = q.EndTime, @LastStatus = q.QuantumStatusCode FROM [dbo].[NSQuantum1] q WHERE q.QuantumId = @LastQuantumId IF ((1 != @LastStatus AND 3 != @LastStatus AND 7 != @LastStatus AND 135 != @LastStatus) AND EXISTS(SELECT q.QuantumId FROM [dbo].[NSQuantum1] q WITH (READUNCOMMITTED) WHERE q.QuantumStatusCode IN (1, 3, 7, 135))) BEGIN RAISERROR (N'4013: There are quanta scheduled after an incomplete quantum.', 16, 1) ROLLBACK TRAN RETURN 1 END SELECT @SecondsPassed = DATEDIFF(SECOND, @LastQuantumEndTime, @EndRealClockTime) IF (DATEPART(MILLISECOND, @LastQuantumEndTime) > DATEPART(MILLISECOND, @EndRealClockTime)) BEGIN SELECT @SecondsPassed = @SecondsPassed - 1 END SELECT @QuantumsPassed = @SecondsPassed / @QuantumIntervalInSeconds IF (0 != @MaxQuantumIntervals AND @QuantumsPassed > @MaxQuantumIntervals) BEGIN SELECT @Skipped = @QuantumsPassed - @MaxQuantumIntervals SELECT @QuantumsPassed = @MaxQuantumIntervals SELECT @StartTime = DATEADD(SECOND, (@Skipped * @QuantumIntervalInSeconds), @LastQuantumEndTime) SELECT @EndTime = DATEADD(SECOND, @QuantumIntervalInSeconds, @StartTime) IF (1 = @LastStatus OR 3 = @LastStatus OR 7 = @LastStatus OR 135 = @LastStatus) BEGIN SELECT @Skipped = @Skipped - 1 UPDATE [dbo].[NSQuantum1] SET QuantumStatusCode = QuantumStatusCode | 64 | 128, RtcEndTime = GETUTCDATE() WHERE QuantumId = @LastQuantumId SELECT @LastStatus = NULL END END ELSE IF ((1 != @LastStatus AND 3 != @LastStatus AND 7 != @LastStatus AND 135 != @LastStatus) AND 0 != @QuantumsPassed) BEGIN SELECT @StartTime = @LastQuantumEndTime SELECT @EndTime = DATEADD(SECOND, @QuantumIntervalInSeconds, @StartTime) END END ELSE BEGIN SELECT @QuantumsPassed = 1 SELECT @StartTime = DATEADD(SECOND, -@QuantumIntervalInSeconds, @EndRealClockTime) SELECT @EndTime = @EndRealClockTime END IF (((1 != @LastStatus AND 3 != @LastStatus AND 7 != @LastStatus AND 135 != @LastStatus) OR @LastStatus IS NULL) AND 0 != @QuantumsPassed) BEGIN IF (@EndTime < @StartTime) BEGIN RAISERROR (N'4015: The quantum end time is less than the quantum start time.', 16, 1) ROLLBACK TRAN RETURN 1 END IF (@EndTime > @EndRealClockTime) BEGIN RAISERROR (N'4016: The quantum end time is later than the current real clock time. Notification Services cannot process events that occur in the future.', 16, 1) ROLLBACK TRAN RETURN 1 END IF (0 = @MaxChronicleQuantumIntervals OR @QuantumsPassed <= @MaxChronicleQuantumIntervals) BEGIN SELECT @EventChronicleRulesFired = 1 END ELSE BEGIN SELECT @EventChronicleRulesFired = 0 END IF (0 = @MaxSubscriptionQuantumIntervals OR @QuantumsPassed <= @MaxSubscriptionQuantumIntervals) BEGIN SELECT @SubscriptionRulesFired = 1 END ELSE BEGIN SELECT @SubscriptionRulesFired = 0 END IF (@EventChronicleRulesFired = 0 AND @SubscriptionRulesFired = 0) BEGIN RAISERROR (N'4017: Either subscription rules or event chronicle rules must be enabled in a quantum.', 16, 1) ROLLBACK TRAN RETURN 1 END INSERT INTO [dbo].[NSQuantum1] (StartTime, EndTime, QuantumStatusCode, SkippedCount, EventChronicleRulesFired, SubscriptionRulesFired, RtcStartTime) VALUES(@StartTime, @EndTime, 1, @Skipped, @EventChronicleRulesFired, @SubscriptionRulesFired, GETUTCDATE()) SELECT @QuantumId = @@Identity SELECT @QuantumsPassed = @QuantumsPassed - 1 SELECT @NewQuantumScheduled = 1 END ELSE BEGIN SELECT @NewQuantumScheduled = 0 END SELECT q.QuantumId AS [QuantumId], q.StartTime AS [StartTime], q.EndTime AS [EndTime], q.SkippedCount AS [SkippedCount], q.QuantumStatusCode AS [QuantumStatusCode], q.EventChronicleRulesFired AS [EventChronicleRulesFired], q.SubscriptionRulesFired AS [SubscriptionRulesFired], @NewQuantumScheduled AS [NewQuantumScheduled], @QuantumsPassed AS [QuantumsBehind] FROM [dbo].[NSQuantum1] q WITH (READUNCOMMITTED) WHERE q.QuantumStatusCode IN (1, 3, 7, 135) COMMIT TRANSACTIONEND |
| MYTESTSERVER | 50801211739181841292753000000000000 | 299765770 | 8 | 302624121 | 1 | False | CREATE PROCEDURE [dbo].[MsgBoxPerfCounters_GetPurgeJobInfo]
@nvcDbServer nvarchar(80),
@nvcDbName nvarchar(128)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
CREATE TABLE #Temp (JobID uniqueidentifier,
JobName sysname,
Status int,
DateStarted int,
TimeStarted int,
Duration int,
OpEmailed nvarchar(20),
OpNetSent nvarchar(20),
OpPaged nvarchar(20),
Retries int,
Server nvarchar(80)
)
CREATE TABLE #Final ( JobName sysname,
Duration int
)
CREATE TABLE #TempHistory (JobID uniqueidentifier,
JobName sysname,
Status int,
DateStarted int,
TimeStarted int,
Duration int,
OpEmailed nvarchar(20),
OpNetSent nvarchar(20),
OpPaged nvarchar(20),
Retries int,
Server nvarchar(80)
)
declare @fullJobName sysname
INSERT INTO #TempHistory
exec ('[msdb]..[sp_help_jobhistory]')
declare MyCursor cursor FAST_FORWARD FOR
SELECT tj.JobName FROM #TempHistory tj
WHERE (tj.JobName LIKE (N'MessageBox_Parts_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'MessageBox_DeadProcesses_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'MessageBox_Message_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'PurgeSubscriptionsJob_' + @nvcDbName) OR tj.JobName LIKE (N'TrackedMessages_Copy_' + @nvcDbName) OR tj.JobName LIKE (N'TrackingSpool_Cleanup_' + @nvcDbName))
AND tj.Server = @nvcDbServer
GROUP BY tj.JobName
open MyCursor
FETCH NEXT FROM MyCursor INTO @fullJobName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ' + @fullJobName)
INSERT INTO #Final
SELECT TOP 1 JobName, Duration FROM #Temp WHERE Server = @nvcDbServer
TRUNCATE TABLE #Temp
FETCH NEXT FROM MyCursor INTO @fullJobName
END
SELECT * FROM #Final
close MyCursor
deallocate MyCursor
drop table #Temp
drop table #Final
drop table #TempHistory |
| MYTESTSERVER | 50801211739181841292753000000000000 | 406263166 | 8 | 302624121 | 1 | False | CREATE PROCEDURE [dbo].[MsgBoxPerfCounters_GetPurgeJobInfo]
@nvcDbServer nvarchar(80),
@nvcDbName nvarchar(128)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
CREATE TABLE #Temp (JobID uniqueidentifier,
JobName sysname,
Status int,
DateStarted int,
TimeStarted int,
Duration int,
OpEmailed nvarchar(20),
OpNetSent nvarchar(20),
OpPaged nvarchar(20),
Retries int,
Server nvarchar(80)
)
CREATE TABLE #Final ( JobName sysname,
Duration int
)
CREATE TABLE #TempHistory (JobID uniqueidentifier,
JobName sysname,
Status int,
DateStarted int,
TimeStarted int,
Duration int,
OpEmailed nvarchar(20),
OpNetSent nvarchar(20),
OpPaged nvarchar(20),
Retries int,
Server nvarchar(80)
)
declare @fullJobName sysname
INSERT INTO #TempHistory
exec ('[msdb]..[sp_help_jobhistory]')
declare MyCursor cursor FAST_FORWARD FOR
SELECT tj.JobName FROM #TempHistory tj
WHERE (tj.JobName LIKE (N'MessageBox_Parts_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'MessageBox_DeadProcesses_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'MessageBox_Message_Cleanup_' + @nvcDbName) OR tj.JobName LIKE (N'PurgeSubscriptionsJob_' + @nvcDbName) OR tj.JobName LIKE (N'TrackedMessages_Copy_' + @nvcDbName) OR tj.JobName LIKE (N'TrackingSpool_Cleanup_' + @nvcDbName))
AND tj.Server = @nvcDbServer
GROUP BY tj.JobName
open MyCursor
FETCH NEXT FROM MyCursor INTO @fullJobName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ' + @fullJobName)
INSERT INTO #Final
SELECT TOP 1 JobName, Duration FROM #Temp WHERE Server = @nvcDbServer
TRUNCATE TABLE #Temp
FETCH NEXT FROM MyCursor INTO @fullJobName
END
SELECT * FROM #Final
close MyCursor
deallocate MyCursor
drop table #Temp
drop table #Final
drop table #TempHistory |
| | | | | | | | |