-> 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.
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.