December 21, 2008 at 12:00 pm
Hi,
We have biztalk databases in SQL Server 2005. In this instance one job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb is running for ever.Actually it is scheduled to run every minute.I started the job manually and stoped the job and verified the job history, here it says [000] Request to run job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb (from User abc\DBadmin) refused because the job is already running from a request by User abc\DBadmin.
and also in the job history it says the job invoked by the user abc\sqlservice but actually the job owner is abc\BTSAdmin.
Then I changed the shecule to every 2 hrs,now also its running for ever.
Could you plz tell me what should I need to invistegate to resolve this issue?
December 21, 2008 at 12:19 pm
Step 1: what command is it executing?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 12:28 pm
It executing below procedure
USE [BizTalkMsgBoxDb]
GO
/****** Object: StoredProcedure [dbo].[bts_ManageMessageRefCountLog] Script Date: 12/21/2008 11:26:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[bts_ManageMessageRefCountLog]
AS
set transaction isolation level read committed
set nocount on
set deadlock_priority low
set xact_abort on
declare @continue int,
@tnActiveTable tinyint,
@fTruncate tinyint,
@cnt int,
@nMessagesFound int, @retVal int,
@fPurgeJobIsRunning int,
@nJobNotStartedLoopCount int
exec sp_getapplock 'ManageMessageRefCountLogJobIsRunning', 'Exclusive', 'Session'
DELETE FROM dbo.ActiveRefCountLog WHERE fType = 7
set @nJobNotStartedLoopCount = 0
WHILE (1 = 1)
BEGIN
set @continue = 1
set @nMessagesFound = 0
set @fPurgeJobIsRunning = 0
--lets check the inactive table and make sure there is nothing there. If we crash while running, we might not have
--completed handling this table
SELECT TOP 1 @tnActiveTable = tnActiveTable FROM dbo.ActiveRefCountLog WITH (ROWLOCK) WHERE fType = 1 OPTION (KEEPFIXED PLAN)
SELECT TOP 1 @fTruncate = fTruncate FROM dbo.TruncateRefCountLog WITH (ROWLOCK) WHERE fType = 1 OPTION (KEEPFIXED PLAN)
if (@fTruncate = 0)
BEGIN
if (@tnActiveTable = 1)
exec dbo.int_PurgeMessageRefCountLog 2, @nMessagesFound OUTPUT
else
exec dbo.int_PurgeMessageRefCountLog 1, @nMessagesFound OUTPUT
END
ELSE
BEGIN
if (@tnActiveTable = 1)
TRUNCATE TABLE MessageRefCountLog2
else
TRUNCATE TABLE MessageRefCountLog1
END
--lets take a lock for checking the refcount log. We use an applock so that we can explicitly release the lock
--normal locks would require us to wait for the transaction to complete, but we don't really need to wait that long
exec @retVal = sp_getapplock 'MessageRefCountLog', 'Exclusive', 'Session'
IF (@retVal < 0 ) -- Lock Not granted
BEGIN
RAISERROR('Unable to acquire applock on MessageRefCountLog', 16, 1)
return
END
--let flip tables
BEGIN TRANSACTION
if (@tnActiveTable = 1)
BEGIN
UPDATE dbo.ActiveRefCountLog SET tnActiveTable = 2 WHERE fType = 1 OPTION (KEEPFIXED PLAN)
UPDATE dbo.TruncateRefCountLog SET fTruncate = 0 WHERE fType = 1
END
else
BEGIN
UPDATE dbo.ActiveRefCountLog SET tnActiveTable = 1 WHERE fType = 1 OPTION (KEEPFIXED PLAN)
UPDATE dbo.TruncateRefCountLog SET fTruncate = 0 WHERE fType = 1
END
COMMIT TRANSACTION
exec sp_releaseapplock 'MessageRefCountLog', 'Session'
--now lets process this table
exec dbo.int_PurgeMessageRefCountLog @tnActiveTable, @nMessagesFound OUTPUT
declare @nvcJobName nvarchar(256)
set @nvcJobName = N'MessageBox_Message_Cleanup_' + db_name()
--in some races it is possible that we will have requested the job start but it hasnt yet so
--we need to check if this is the case
if not exists (SELECT TOP 1 1 FROM dbo.ActiveRefCountLog WHERE fType = 7)
BEGIN
exec @fPurgeJobIsRunning = dbo.int_IsAgentJobRunning @nvcJobName
IF (@fPurgeJobIsRunning = 0)
BEGIN
INSERT INTO dbo.ActiveRefCountLog (tnActiveTable, fType) VALUES (1, 7)
set @nJobNotStartedLoopCount = 0
exec msdb.dbo.sp_start_job @job_name = @nvcJobName
END
END
ELSE
BEGIN
set @nJobNotStartedLoopCount = @nJobNotStartedLoopCount + 1
IF (@nJobNotStartedLoopCount > 6) --if the job hasn't started in one minute then something is off so lets just try it ... we might fail but I dont have much choice
BEGIN
set @nJobNotStartedLoopCount = 0
exec msdb.dbo.sp_start_job @job_name = @nvcJobName
END
END
WAITFOR DELAY '0:00:10' --delay 10 seconds
END
exec sp_releaseapplock 'ManageMessageRefCountLogJobIsRunning', 'Session'
Thank You
December 21, 2008 at 1:53 pm
Then you want to determine which statement is currently running while it appears to be hanging. You also want to see if any blocking is occurring.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 2:11 pm
[font="Courier New"]WHILE (1 = 1)
BEGIN
...
...
...
WAITFOR DELAY '0:00:10' --delay 10 seconds
END[/font]
Perhaps it's just me and maybe I'm not getting it, but it looks like this job was designed to run forever.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2008 at 2:16 pm
Heh, you're right Jeff, the only exit is a RaisError/Return pair. Dang, and I meant to check for that too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 2:34 pm
Run profiler and see exactly where in job the code is stalling.
MJ
December 21, 2008 at 6:56 pm
I would bet anything that the job is stalling on the WAITFOR command. 😛
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2008 at 7:05 pm
Your logic isn't exiting anywhere. I'd run those selects and see what they're returning.
December 21, 2008 at 7:19 pm
Thank you
The databases, tables, stored procedures,jobs and everything were created when BIZTaLK application was installed and configured.
December 22, 2008 at 9:40 am
Hi,
We have BizTalk and I also have this same behavior for this particular job.
According to this MSDN article, the behavior is by design.
<< This job manages the reference count logs for messages and determines when a message is no longer referenced by any subscriber.
Even though this SQL Server Agent job is scheduled to run once per minute, the stored procedure that is called by this job contains logic to ensure that the stored procedure runs continually. This is by design behavior and should not be modified. >>
http://msdn.microsoft.com/en-us/library/aa561960.aspx
Hope this helps!
TW
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply