Nice way to kill your SQL Server
Are you getting enormous amount of
The activated proc '...' running on queue '...' output the following: 'The service queue "..." is currently disabled.
errors from SQL Server? Keep on reading!
Yesterday, when I came back from my lunch, I got a heads-up from Windows:
And it was literally out of space: 0 (zero) bytes available on C: drive. Wow…
I cleaned up the binary files from my developments, which gave me 3 GB of free space and some time to do the investigation. A short time, because disk space was counting down at about 300 MB per minute.
It appeared that the SQL Server error log files grew up to 53 GB and were still growing. Here is the default folder where you can find them:
To gain more time, I executed the command
is Management Studio. It renames current ERRORLOG file to ERRORLOG.1 and starts the new current. So now it was safe to delete all .1/.2/etc files and get those 53 GB back. Huh…
Now it’s time to figure out the actual issue. Go to Management Studio -> your server -> Management -> SQL Server Logs -> Current -> (right click) -> View SQL Server Log
I saw millions of the following messages there (let’s say my queue is named XQueue for simplicity):
The activated proc '[dbo].[XQueueProcessor]' running on queue 'dbo.XQueue' output the following: 'The service queue "XQueue" is currently disabled.
So, it looks like there is a problem with SQL Server Service Broker, which is constantly trying to read something from disabled queue. Isn’t it supposed to stop after 5 attempts (a.k.a. poison messages)? Ok, keep on troubleshooting.
First, let check if the queue is actually disabled
SELECT Name, is_receive_enabled, is_enqueue_enabled FROM sys.service_queues WHERE Name = 'XQueue'
Yep, it’s disabled:
Do we have anything queued?
SELECT TOP 1000 * FROM [XQueue] WITH(NOLOCK)
I got 9 rows back. This means we got some messages en-queued, and one of them failed to process, the message was considered as poison and the queue was disabled (which is good), but the processor still seems to be running in eternal loop.
So, let’s have a look at the processor. We had something like that:
CREATE PROCEDURE [dbo].[XQueueProcessor] AS BEGIN DECLARE @queuing_order BIGINT ,@conversation_handle UNIQUEIDENTIFIER ,@message_enqueue_time DATETIME ,@message_type_name VARCHAR(156) ,@message_body VARBINARY(MAX); WHILE(1=1) -- MAIN WHILE LOOP RECEIVING THE MESSAGE BATCHES BEGIN BEGIN TRANSACTION BEGIN TRY WAITFOR(RECEIVE TOP(1) @queuing_order = [queuing_order], @conversation_handle = [conversation_handle], @message_enqueue_time = message_enqueue_time, @message_type_name = message_type_name, @message_body = message_body FROM [dbo].[XQueue]), TIMEOUT 10 IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION BREAK END -- SOME CRUD OPERATIONS HAPPEN HERE COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @Error varchar(4000) = ERROR_MESSAGE() PRINT @Error ROLLBACK TRANSACTION END CATCH END END
A-ha! So, what happens when an error occurs during the message processing? Control goes to CATCH block, error message is printed out (when we are in Service Broker, print goes directly to ERRORLOG file which we saw already), the transaction is rolled back… But we still are inside eternal loop, no BREAK or RETURN statement there, so the processor will try again to get the same message from the queue. After 5th attempt, the message will be marked as poison, the queue will get disabled, but the loop will keep running! The exception will change to “The service queue is currently disabled” and voila, CPU is busy and error logs are floating the disk.
The fix is pretty trivial, just change the CATCH block to
BEGIN CATCH DECLARE @Error varchar(4000) = ERROR_MESSAGE() PRINT @Error ROLLBACK TRANSACTION RETURN END CATCH