locked
Not enough storage is available to process this command Error RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I have a MVC page that updates a database table that is intermittently throwing the following error:

    System.Data.SqlClient.SqlException (0x80131904): Failed to create AppDomain "master.sys[runtime].3". Could not load file or assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command.

    I have never experienced this error message before and really haven't found a good solution on Google.  Here is my query:

    CREATE PROCEDURE [dbo].[sp_Update_tbl_Log_Correspondence]
    	-- Add the parameters for the stored procedure here
    	 @ID	 					[int],
    	 @ReviewedDate 				[datetime],
    	 @ReviewedBy 				[varchar](50),
    	 @TestsPerformed 			[varchar](1000),
    	 @ActionStatus	 			[varchar](50),
    	 @ActionTaken 				[varchar](5000),
    	 @ERVisit		 			[varchar](50),
    	 @SendTextFollowup		 	[varchar](50),
    	 @ReferralID				[int]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	/*New*/
    	DECLARE @MRNumber					[varchar](50)
    	DECLARE @PreviousActionTaken		[varchar](5000)
    	DECLARE @CurrentActionTaken			[varchar](5000)
    	DECLARE	@SignatureStamp				[varchar](500)
    	DECLARE @PreveiousTestsFerformed	[varchar](500)
    	DECLARE @CurrentTestsFerformed		[varchar](500)
    
    	IF(@ActionTaken IS NULL)
    		BEGIN
    			SET @ActionTaken = ''
    		END
    
    	SET @ReviewedDate = GetDate()
    	SET @SignatureStamp = '<span class="datestamp">' + FORMAT(@ReviewedDate, 'MM/dd/yyyy hh:mm:ss tt') + '</span> - <span class="signature">' + @ReviewedBy + '</span>'
    	Select @PreviousActionTaken = ISNULL(ActionTaken, ''), @PreveiousTestsFerformed = ISNULL(TestsPerformed, ''), @MRNumber = MRNumber From tbl_Log_Correspondence Where ID = @ID
    	If (@PreviousActionTaken = '')
    		BEGIN
    			SET @CurrentActionTaken = @SignatureStamp + ' - ' + @ActionTaken
    		END
    	ELSE
    		BEGIN
    			SET @CurrentActionTaken = @PreviousActionTaken + '<br/><br/>' + @SignatureStamp + ' - ' + @ActionTaken
    		END
    	
    	SET @CurrentTestsFerformed = @PreveiousTestsFerformed + ' ' + @TestsPerformed
    
        -- Insert statements for procedure here
    	UPDATE [myDB].[dbo].[tbl_Log_Correspondence] 
    
    	SET  [ReviewedDate]	 = @ReviewedDate,
    		 [ReviewedBy]	 = @ReviewedBy,
    		 [TestsPerformed]	 = @CurrentTestsFerformed,
    		 [ActionStatus]	 = @ActionStatus,
    		 [ActionTaken]	 = @CurrentActionTaken,
    		 [ERVisit]		= @ERVisit,
    		 [SendTextFollowup] = @SendTextFollowup,
    		 [ReferralID] = @ReferralID
    
    	WHERE 
    		( [ID]	 = @ID)
    
    	If (@SendTextFollowup = 'Yes - Send ER Followup')
    		BEGIN
    			EXEC sp_InsertMessageToQueue @MRNumber, 'ER Followup'
    		END
    
    	If (@SendTextFollowup = 'Yes - Send Referral Followup')
    		BEGIN		
    			EXEC sp_InsertMessageToQueue @MRNumber, 'Specialist Report Followup'
    		END
    		/*
    	ELSE
    		BEGIN
    			SET @CurrentActionTaken = @PreviousActionTaken + '<br/><br/>' + @SignatureStamp + ' - ' + @ActionTaken
    		END
    		*/
    
    END
    

    Now I arguably have too much business logic within the query, but I really didn't think that would cause this problem and there is only one large column declared a varchar(5000) which I didn't think would be such a problem.  As I mentioned, the problem is intermittent, so I'm really not sure where to start debugging.  Any help would be appreciated.

    Friday, April 6, 2018 5:27 PM

Answers

  • User1122355199 posted

    After picking through the query, the line:

    SET @SignatureStamp = '<span class="datestamp">' + FORMAT(@ReviewedDate, 'MM/dd/yyyy hh:mm:ss tt') + '</span> - <span class="signature">' + @ReviewedBy + '</span>'
    	

    is causing the error and seems to be coming from the use of the Format statement.  I am running SQL Server 2012 on Windows Server 2012, so support shouldn't be a problem.  Changing the query to:

    SET @SignatureStamp = '<span class="datestamp">' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11)) + '</span> - <span class="signature">' + @ReviewedBy + '</span>'

    seems to remove the problem (at least right now).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 1:44 AM
  • User753101303 posted

    Ah good point. FORMAT uses .NET conventions and is likely implement as a CLR function.is likely implemented using .NET so I'm quite confident you found what causes the problem.

    According to https://stackoverflow.com/questions/14912693/how-to-fix-the-error-failed-to-create-appdomain-ssisdb-dboruntime-x it could be caused by pending .NET updates and/or restarting just the SQL Service might enough.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 9:20 AM
  • User753101303 posted

    I believe you have other features such as some types that are using the CLR behind the scene. Rather than to just drop FORMAT Iµ would try to understand what happens (seems uncommon this is the first time I heard about this problem).

    Try perhaps https://blogs.msdn.microsoft.com/dohollan/2012/04/20/sql-server-2012-sqlclr-net-framework-version/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 10:12 PM

All replies

  • User753101303 posted

    Hi,

    sp_InsertMessageToQueue is yours? Could it happen inside that SP?

    Friday, April 6, 2018 5:44 PM
  • User1122355199 posted

    Good question.  Yes, it is my query as well:

    CREATE PROCEDURE [dbo].[sp_InsertMessageToQueue]
    	-- Add the parameters for the stored procedure here
    	@MRNumber			varchar(50),
    	@MessageCategory	varchar(50)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    
    	DECLARE @MessageBody 		varchar(500)
    	DECLARE @toPhoneNumber		varchar(50)
    
        -- Insert statements for procedure here
    
    	DECLARE @PatientDOB			date
    	DECLARE @LanguagePreference	varchar(50)
    	Select @LanguagePreference = LanguagePreference, @PatientDOB = PatientDOB, @toPhoneNumber = PatientPhone from tblPtmstr1 where PatientID = @MRNumber
    
    	Select @MessageBody = MessageBody from tbl_Log_MessageStore Where Language = @LanguagePreference AND Category = @MessageCategory
    	
    
    	INSERT INTO tbl_Log_OutboundMessageQueue
                             (MRNumber, toPhoneNumber, MessageType, MessageCategory, Message)
    	VALUES        (@MRNumber, @toPhoneNumber, 'SMS', @MessageCategory, @MessageBody)
    END

    Friday, April 6, 2018 5:55 PM
  • User-1716253493 posted

    maybe the issue is related to disk space, cache size or the darabase it self

    Saturday, April 7, 2018 1:16 AM
  • User-158764254 posted

    Have you configured the Max Memory setting for SQL?
    A low memory condition might explain the intermittent nature of the error.

    SQL will eventually eat away at all available memory until there's no free memory left.  I generally set the SQL memory cap at 4GB less the server holds.  This leaves enough behind for the OS and any other processes that need to load.

    Also are you using any .NET CLR types (or .NET assemblies) from inside your database or does your database use only native SQL types and commands?

    Saturday, April 7, 2018 11:50 AM
  • User1122355199 posted

    Thanks for the response.  I have not configured the Max Memory setting.  Wouldn't that make the problem worse?  My database currently only uses native SQL taypes and commands, no CLR types or .Net assemblies.

    Saturday, April 7, 2018 4:03 PM
  • User1122355199 posted

    2.11 TB available on 6 TB total.  Database size is 7.5 GB.

    Saturday, April 7, 2018 4:13 PM
  • User753101303 posted

    I asked because as the error comes from System.Data.SqlClient.SqlException I though you were using CLR types somewhere.

    Edit: Where do you see this error message? In the SQL Server log ? Could it happens from something in the master database as the error message is about master.sys[runtime].3 !???

    A specialized SQL Server at the MSDN site could  be better.

    Saturday, April 7, 2018 5:52 PM
  • User1122355199 posted

    The MVC page sends me the error message, however, when I look in the SQL Server log, I see the following error:

    Could not load file or assembly 'System.Data<c/> Version=4.0.0.0<c/> Culture=neutral<c/> PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)

    This is a mission critical application, so I need to get this working.  This is the only query causing problems, so I think the idea of another server is really not the solution either.

    Saturday, April 7, 2018 6:13 PM
  • User1122355199 posted

    Let me rephrase my previous post with more information.  When this error occurs, other applications are running successfully without errors, so it would seem the problem is query specific, but I'm at a loss at to what is causing it.

    Saturday, April 7, 2018 6:22 PM
  • User1122355199 posted

    I tried removing:

    	If (@SendTextFollowup = 'Yes - Send ER Followup')
    		BEGIN
    			EXEC sp_InsertMessageToQueue @MRNumber, 'ER Followup'
    		END
    
    	If (@SendTextFollowup = 'Yes - Send Referral Followup')
    		BEGIN		
    			EXEC sp_InsertMessageToQueue @MRNumber, 'Specialist Report Followup'
    		END

    in order to rule out this portion and the error still persists.  I am currently testing when virtually all user traffic on the server is stopped and the error is now consistently happening with the one page that uses the stored procedure.  I suspect if I reboot, the problem will resolve, but will return.  Any ideas on how to isolate what is happening?

    Saturday, April 7, 2018 11:46 PM
  • User1122355199 posted

    After picking through the query, the line:

    SET @SignatureStamp = '<span class="datestamp">' + FORMAT(@ReviewedDate, 'MM/dd/yyyy hh:mm:ss tt') + '</span> - <span class="signature">' + @ReviewedBy + '</span>'
    	

    is causing the error and seems to be coming from the use of the Format statement.  I am running SQL Server 2012 on Windows Server 2012, so support shouldn't be a problem.  Changing the query to:

    SET @SignatureStamp = '<span class="datestamp">' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11)) + '</span> - <span class="signature">' + @ReviewedBy + '</span>'

    seems to remove the problem (at least right now).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 1:44 AM
  • User753101303 posted

    Ah good point. FORMAT uses .NET conventions and is likely implement as a CLR function.is likely implemented using .NET so I'm quite confident you found what causes the problem.

    According to https://stackoverflow.com/questions/14912693/how-to-fix-the-error-failed-to-create-appdomain-ssisdb-dboruntime-x it could be caused by pending .NET updates and/or restarting just the SQL Service might enough.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 9:20 AM
  • User1122355199 posted

    Sounds like we're zeroing in on the issue.  However, there is no outstanding updates waiting to be applied.  I guess the other issue is that, if this is going to hang up, each time an update needs to be applied, this is not a reliable code to use.

    Sunday, April 8, 2018 2:24 PM
  • User753101303 posted

    I believe you have other features such as some types that are using the CLR behind the scene. Rather than to just drop FORMAT Iµ would try to understand what happens (seems uncommon this is the first time I heard about this problem).

    Try perhaps https://blogs.msdn.microsoft.com/dohollan/2012/04/20/sql-server-2012-sqlclr-net-framework-version/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 8, 2018 10:12 PM