-> I was working on a Service broker issue where conversations with State Disconnected_inbound was piling up in Sys.Conversation_Endpoint.
-> Each new conversation that is initiated ends up with a state Disconnected_inbound in Sys.Conversation_Endpoint.
-> DI Disconnected inbound. The remote side of the conversation has issued an END CONVERSATION. The conversation remains in this state until the local side of the conversation issues an END CONVERSATION. An application might still receive messages for the conversation. Because the remote side of the conversation has ended the conversation, an application cannot send messages on this conversation. When an application issues an END CONVERSATION, the conversation moves to the CD (Closed) state.
-> I confirmed with my Client that Service broker is indeed working and that the results are written to the required destination. It is just that these conversations are not getting closed properly.
-> I checked the stored procedures associated with Service broker on that database and was able to see below code,
SELECT TOP 1
@ConversationHandle = @ConversationHandle
FROM
@Messages;
-> There was a mistake in above code. Instead of ConversationHandle it was wrongly depicted as @ConversationHandle.
-> I changed the above code to below,
SELECT TOP 1
@ConversationHandle = ConversationHandle
FROM
@Messages;
-> Usage of @ConversationHandle instead of ConversationHandle always results in NULL for @ConversationHandle, which in turn doesn’t end the conversation properly.
-> This resolved the issue. It was a minor mistake, but had huge impact on the overall process.
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
-> Client attempted to reclaim what he believes to be significant unused space in a datafile by running DBCC SHRINKFILE.
-> Shrinkfile process was running for over 39 hours. Client was not sure if it was a matter of just waiting, or if it is in a state where it will never complete.
-> I thought that DBCC Shrinkfile is supposed to take lot of time and I was under the impression that it is just a matter of waiting for the operation to complete.
-> I tried checking the process and wanted to confirm if it was progressing and then advise client to wait or just cancel it, as shrink file is not a recommended operation. Client advised me that they purged a huge table and this is an one time operation and it released close to 92% free space. Now they wanted to shrink it just for one time which makes sense.
-> I executed below query to check the progress of shrink operation and it showed that it is 100% complete. But the SPID was still active,
SELECT start_time, percent_complete, DATEADD (ss,DATEDIFF
(ss,start_time,getdate()) / percent_complete * 100,start_time) AS Expected_end_time, st.text as SQL, command as Current_Command,session_id FROM sys.dm_exec_requests er CROSS APPLY
sys.dm_exec_sql_text(er.sql_handle) st
WHERE percent_complete != 0
-> Below was the screen grab of the shrink process,
-> DBCC Shrinkfile shows as 100% complete. But the process is blocked by SPID 48.
-> SPID 48 shows as NULL. DBCC Inputbuffer also returns NULL. Seems like this is a system process.
-> I stopped the shrinkfile and restarted it. It starts up without any issue. It progresses well, But as soon as it reaches 100% there is a blocking and is always blocked by this system process. This behavior is evident every time I start the Shrink operation on this data file.
-> More details on SPID 48 below,
-> Spid 48 seems to have a cmd type of “BRKR TASK” and wait type on that process is “BROKER_TRANSMISSION_TABLE”.
-> I did a guess that this system process should be related to Service broker. It seems like some operation related to Servcie Broker is not letting Shrink operation to complete.
-> Executed Sp_Lock and below was the details,
-> From the above screenshot it is clear that these 2 operations require access to object with objectID 73.
-> Object ID 73 is a system object sysdercv. This object exists in every database, contains a row for each receiving endpoint of a service broker conversation.
-> Object sys.conversation_endpoints has 197483543 rows. Out of which 169473219 rows are Started Outbound.
-> Started Outbound refers to SQL Server has processed a BEGIN Conversation, but no messages have yet been sent. Checking conversations in sys.conversation_endpoint for state STARTED_OUTBOUND. -> Notice the lifetime for these entries, these entries can be reclaimed only after the lifetime message.
-> Advised Client to add a lifetime to every conversation. Below code adds a 120 seconds lifetime for each conversation,
begin dialog conversation @h
from service [JB_SB_source]
to service N'JB_SB_target'
with encryption = off, lifetime = 120;
-> Also advised Client to add an END CONVERSATION in the code which handles the message.
-> Advised Client to execute below code to perform cleanup from sys.conversation_endpoints with state STARTED_OUTBOUND that has a lifetime value greater than 1 month from current date. Below code was copied from this article.
declare @h uniqueidentifier, @count int = 0;
begin transaction;
while (1=1)
begin
set @h = null;
select top(1) @h = conversation_handle
from sys.conversation_endpoints
where state_desc = N'STARTED_OUTBOUND'
and security_timestamp = '1900-01-01 00:00:00.000'
and lifetime > dateadd(month, 1, getutcdate())
if (@h is null)
begin
break
end
end conversation @h with cleanup;
set @count += 1;
if (@count > 1000)
begin
commit;
set @count = 0;
begin transaction;
end
end
commit
-> After these changes DBCC SHRINKFILE completed fine without any issues.
Thank You, Vivek Janakiraman
Disclaimer: The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
