Memory problem….
-
I’m having a problem with SQL Server fragmenting memory and not being able to obtain a large enough block of contiguous memory for some operations to run.I have a number of stored procedures which, using UDFs call extended stored procedures, which in turn call COM objects (see UDF code below). Over a period of some weeks use memory seems to become fragmented and the stored procedures will error due to lack of contiguous memory.
BEGIN
DECLARE @objPCPlus int
DECLARE @Result int
--Create the object using the in built stored procedure
EXEC sp_OACreate 'AFDUtilX.Utility', @objPCPlus OUT,4
--set the Postcode property of the object
EXEC sp_OASetProperty @objPCPlus, 'Postcode', @strPostcode
--Initiate the CheckPostcode routine
EXEC sp_OAMethod @objPCPlus, 'CheckPostcode'
EXEC sp_OAGetProperty @objPCPlus, 'Result', @Result OUT
EXEC sp_OADestroy @objPCPlus OUT
Return @Result
END
Due to the many connections which would have to be re-established and its 24-7 use stopping and starting SQL Server is not an ideal solution.
Is there any way of forcing a memory ‘clear out’ without stopping SQL Server that I can schedule to run at a low demand time?
-
What error are you getting? # and msg?
I'm not sure if this helps the server, but dbcc freeproccache will clear the cached plans from SQL Server. Might allow some memory garbage collection.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply