October 19, 2010 at 1:38 pm
I have 10s of thousands of these messages daily in my SQL logs:
Message
The activated proc [dbo].[my_procedure_name] running on queue myqueuename output the following: ''
Note: my procedure and queue name have been changed. But notice the empty string at the end of the message. These all appear to be successful messages. The implementation is for a business requirement to basically handle a bunch of back end data processing in an async manner from the front end application. Is there a way to prevent these types of messages from flooding my SQL log?
The situation this puts me in is our database monitoring software scans the SQL log to pickup errors. This is now timing out because in a period of a day I have a few hundred thousand entries in the log. I already recycle the log file daily (and retain 90 copies). I can recycle the logs more frequently but that really doesn't address the issue. I'm hoping someone with a bit of service broker expertise can provide some insight into why these messages might be coming back and logged.
October 19, 2010 at 1:58 pm
- Is there a print statement in your SSB procedure ?
- Is there a select statement in your SSB procedure that doesn't return its result to a variable ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2010 at 2:27 pm
Hey AlzDBA, thanks for the quick response. In short, the answers are both 'no'. Those are the first things I looked for. I even had the developer implement the standard 'SET NOCOUNT ON' clause. So, am I correct in assuming this is not normal behavior for a service broker queue?
Anyway, here is the actual procedure (with some modifications to remove anything related to our environment :)).
CREATE PROCEDURE [dbo].[my_procedure_name]
AS
SET NOCOUNT ON;
DECLARE @ErrorSeverity INT, @ErrorState INT, @ReturnCode INT, @BeginTranCount INT;
DECLARE @StackTrace NVARCHAR(MAX);
SET @BeginTranCount = @@TranCount
BEGIN TRY
DECLARE @ConversationGroup UNIQUEIDENTIFIER
DECLARE @Dialog UNIQUEIDENTIFIER
DECLARE @MessageType VARCHAR(MAX)
DECLARE @MessageBody XML
DECLARE @TableName NVARCHAR(200)
WHILE 1 = 1 BEGIN
WAITFOR
(
GET CONVERSATION GROUP @ConversationGroup
FROM [my_db].[dbo].[my_queue_name]
), TIMEOUT 10000
-- break out once all the work is done
IF @ConversationGroup IS NULL BEGIN
BREAK
END
SET @Dialog = NULL
WAITFOR
(
RECEIVE TOP (1)
@Dialog = conversation_handle,
@MessageBody = CONVERT(XML, message_body),
@MessageType = message_type_name
FROM [my_db].[dbo].[my_queue_name]
WHERE conversation_group_id = @ConversationGroup
), TIMEOUT 10000
-- break out if the queue is empty
IF @@ROWCOUNT = 0 OR @Dialog IS NULL BEGIN
BREAK
END
IF (@BeginTranCount = 0) BEGIN TRAN
IF @MessageType = 'my_queue_name'
BEGIN
SELECT @TableName = CAST(@MessageBody.query('/my_xml_path/Header/Table').value('.', 'varchar(max)') AS VARCHAR(MAX))
-- dynamically create the insert statement based on the columns inside the xml rows
DECLARE @InsertQuery1 NVARCHAR(MAX), @InsertQuery2 NVARCHAR(MAX), @InsertQueryFinal NVARCHAR(MAX)
SELECT @InsertQuery1 = '', @InsertQuery2 = ''
;WITH ExtraColumns AS
(
SELECT DISTINCT
RowNodes.item.query('local-name(.)').value('.', 'VARCHAR(200)') AS ColumnName
FROM @MessageBody.nodes('my_xml_path/Rows/Row/child::*') AS RowNodes(item)
WHERE RowNodes.item.query('local-name(.)').value('.', 'VARCHAR(200)') NOT IN ('RowID', 'ColumnID', 'DateChanged', 'FieldName', 'PreviousValue', 'NewValue')
)
SELECT
@InsertQuery1 = @InsertQuery1 + '[' + ColumnName + '], ',
@InsertQuery2 = @InsertQuery2 + ' RowNodes.item.value(''(' + ColumnName + ')[1]'', ''UNIQUEIDENTIFIER''),' + CHAR(13)
FROM ExtraColumns
SET @InsertQueryFinal = N'
DECLARE @UserAdded NVARCHAR(30)
DECLARE @DateAdded DATETIME
DECLARE @HostAdded NVARCHAR(30)
SELECT
@UserAdded = Header.item.value(''(UserAdded)[1]'', ''NVARCHAR(30)''),
@DateAdded = Header.item.value(''(DateAdded)[1]'', ''DATETIME''),
@HostAdded = Header.item.value(''(HostAdded)[1]'', ''NVARCHAR(30)'')
FROM @MessageBody.nodes(''my_xml_path/Header'') AS Header(item)
INSERT INTO [my_db].[dbo].' + @TableName + N'Transaction (' + @InsertQuery1 + N'DateChanged, FieldName, PreviousValue, NewValue, UserAdded, DateAdded, HostAdded)
SELECT
' + @InsertQuery2 + N' RowNodes.item.value(''(DateChanged)[1]'', ''DATETIME''),
[my_db].[dbo].[FriendlyName2](RowNodes.item.value(''(FieldName)[1]'', ''VARCHAR(200)'')),
CASE WHEN Right(RowNodes.item.value(''(FieldName)[1]'', ''VARCHAR(200)''), 4) = ''GUID''
THEN [my_db].[dbo].[LookupColumn](RowNodes.item.value(''(FieldName)[1]'', ''VARCHAR(200)''), RowNodes.item.value(''(PreviousValue)[1]'', ''VARCHAR(200)''))
ELSE RowNodes.item.value(''(PreviousValue)[1]'', ''VARCHAR(200)'')
END,
CASE WHEN Right(RowNodes.item.value(''(FieldName)[1]'', ''VARCHAR(200)''), 4) = ''GUID''
THEN [my_db].[dbo].[LookupColumn](RowNodes.item.value(''(FieldName)[1]'', ''VARCHAR(200)''), RowNodes.item.value(''(NewValue)[1]'', ''VARCHAR(200)''))
ELSE RowNodes.item.value(''(NewValue)[1]'', ''VARCHAR(200)'')
END,
@UserAdded,
@DateAdded,
@HostAdded
FROM @MessageBody.nodes(''my_xml_path/Rows/Row'') AS RowNodes(item)
'
EXEC sp_executesql @InsertQueryFinal, N'@MessageBody XML', @MessageBody
END
IF (@@TRANCOUNT > @BeginTranCount) COMMIT TRAN
END
END TRY
BEGIN CATCH
-- Rollback any necessary transaction
IF (@@TRANCOUNT > @BeginTranCount) ROLLBACK TRANSACTION;
-- Fetch details of the Error
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @StackTrace =
'Msg ' + CAST(ERROR_NUMBER() AS VARCHAR)
+ ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR)
+ ', State ' + CAST(ERROR_STATE() AS VARCHAR)
+ ', Procedure ' + ERROR_PROCEDURE()
+ ', Line ' + CAST(ERROR_LINE() AS VARCHAR)
+ '.' + CHAR(13) + CHAR(10) + ERROR_MESSAGE();
RAISERROR(@StackTrace, @ErrorSeverity, @ErrorState);
END CATCH
October 20, 2010 at 12:21 am
Does the SQL errorlog mention any changes to the sproc have been detected after the queue start ?
That's how it indicates it isn't using the latest version of your sproc.
Could it be your dynamic query is running a bit large ?
Capture this dynamic sql in a logging table and examine the generated statements.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 20, 2010 at 11:42 am
Thanks for the lead. Our developer found that if he commented out the execution of the dynamic query, the messages stopped. I have a few follow up questions if you do not mind. You mentioned this is how the service broker indicates it's not using the latest version of the sproc. Are you referring to the actual procedure or whatever execution plan is cached? If the latter I can see how this would cause this. The procedure is designed to be the activiation procedure for several tables (via triggers). They want to do their own auditing over the data, thus the dynamic SQL.
I'm thinking about having the developer move the dynamic portion of the code to another stored procedure to be executed. I'm thinking that if it's related to the execution plan, that should resolve this.
October 20, 2010 at 2:48 pm
If an activation sproc is modified, and ssb notices that, it will put a notification in sqlservers errorlog.
Off couse, this does not cover for the generated dynamic sql.
This message will vanish once the queue is stopped and reactivated.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 21, 2010 at 8:57 am
Ok, I have confirmed that the create date for the procedure is after the create date for the queue. If we start and stop the queue you're thinking this message will disappear? If that's the case I wish MS would have put something more useful in the message instead of an empty string, lol. I'll pass this to the developer and if it works I'll also have them add code to their procedure scripts to stop and restart queues.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply