Reseting memory in DB [modified]
-
I found the following sp on this website, when I run by right clicking on the procedure using the command "Execute Stored Procedure" it works fine. However, when I run the sp from C# I get the following error: //err.Message = "Microsoft SQL Native Client: //CONFIG statement cannot be used inside a user transaction.\\42000 = 574; //Microsoft SQL Native Client: Configuration option 'show advanced options' //changed from 0 to 1. Run the RECONFIGURE statement to install.\\01000 = 15457" ALTER PROCEDURE [dbo].[sp_Database_ResetMemory] AS EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; EXEC sp_configure 'max server memory (MB)', 512; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; WAITFOR DELAY '00:00:15'; EXEC sp_configure 'max server memory (MB)', 2147483647; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Can anyone tell me what the problem is. Thanks in advance, Michael
modified on Thursday, May 26, 2011 12:50 PM
-
I found the following sp on this website, when I run by right clicking on the procedure using the command "Execute Stored Procedure" it works fine. However, when I run the sp from C# I get the following error: //err.Message = "Microsoft SQL Native Client: //CONFIG statement cannot be used inside a user transaction.\\42000 = 574; //Microsoft SQL Native Client: Configuration option 'show advanced options' //changed from 0 to 1. Run the RECONFIGURE statement to install.\\01000 = 15457" ALTER PROCEDURE [dbo].[sp_Database_ResetMemory] AS EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; EXEC sp_configure 'max server memory (MB)', 512; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; WAITFOR DELAY '00:00:15'; EXEC sp_configure 'max server memory (MB)', 2147483647; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max server memory (MB)'; EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Can anyone tell me what the problem is. Thanks in advance, Michael
modified on Thursday, May 26, 2011 12:50 PM
My ex-colleague came across similar message. Not sure about this, but if sql server is running in mixed authentication mode and you are using sql authentication, this must have occurred.
// ♫ 99 little bugs in the code, // 99 bugs in the code // We fix a bug, compile it again // 101 little bugs in the code ♫